SQL MAX de plusieurs colonnes?
Comment voulez-vous revenir 1 valeur par ligne de la max de plusieurs colonnes:
TableName
[Number, Date1, Date2, Date3, Cost]
j'ai besoin de revenir à quelque chose comme ceci:
[Number, Most_Recent_Date, Cost]
20 réponses
Eh bien, vous pouvez utiliser l'instruction de cas:
SELECT
CASE
WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2
WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3
ELSE Date1
END AS MostRecentDate
[pour Microsoft SQL Server 2008 et au-dessus, vous pouvez considérer la réponse plus simple de Sven ci-dessous.]
Voici une autre bonne solution pour la fonctionnalité Max utilisant T-SQL et SQL Server
SELECT [Other Fields],
(SELECT Max(v)
FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]
FROM [YourTableName]
si vous utilisez MySQL, vous pouvez utiliser
SELECT GREATEST(col1, col2 ...) FROM table
il y a 3 autres méthodes où UNPIVOT (1) est de loin la plus rapide, suivi par UNPIVOT simulé (3) qui est beaucoup plus lent que (1) mais encore plus rapide que (2)
CREATE TABLE dates
(
number INT PRIMARY KEY ,
date1 DATETIME ,
date2 DATETIME ,
date3 DATETIME ,
cost INT
)
INSERT INTO dates
VALUES ( 1, '1/1/2008', '2/4/2008', '3/1/2008', 10 )
INSERT INTO dates
VALUES ( 2, '1/2/2008', '2/3/2008', '3/3/2008', 20 )
INSERT INTO dates
VALUES ( 3, '1/3/2008', '2/2/2008', '3/2/2008', 30 )
INSERT INTO dates
VALUES ( 4, '1/4/2008', '2/1/2008', '3/4/2008', 40 )
GO
Solution 1 ( UNPIVOT )
SELECT number ,
MAX(dDate) maxDate ,
cost
FROM dates UNPIVOT ( dDate FOR nDate IN ( Date1, Date2,
Date3 ) ) as u
GROUP BY number ,
cost
GO
Solution 2 (Sous-requête par ligne)
SELECT number ,
( SELECT MAX(dDate) maxDate
FROM ( SELECT d.date1 AS dDate
UNION
SELECT d.date2
UNION
SELECT d.date3
) a
) MaxDate ,
Cost
FROM dates d
GO
Solution 3 (Simulée UNPIVOT )
;WITH maxD
AS ( SELECT number ,
MAX(CASE rn
WHEN 1 THEN Date1
WHEN 2 THEN date2
ELSE date3
END) AS maxDate
FROM dates a
CROSS JOIN ( SELECT 1 AS rn
UNION
SELECT 2
UNION
SELECT 3
) b
GROUP BY Number
)
SELECT dates.number ,
maxD.maxDate ,
dates.cost
FROM dates
INNER JOIN MaxD ON dates.number = maxD.number
GO
DROP TABLE dates
GO
L'un ou l'autre des deux échantillons ci-dessous fonctionnera:
SELECT MAX(date_columns) AS max_date
FROM ( (SELECT date1 AS date_columns
FROM data_table )
UNION
( SELECT date2 AS date_columns
FROM data_table
)
UNION
( SELECT date3 AS date_columns
FROM data_table
)
) AS date_query
le second est un ajout à la réponse de lassevk .
SELECT MAX(MostRecentDate)
FROM ( SELECT CASE WHEN date1 >= date2
AND date1 >= date3 THEN date1
WHEN date2 >= date1
AND date2 >= date3 THEN date2
WHEN date3 >= date1
AND date3 >= date2 THEN date3
ELSE date1
END AS MostRecentDate
FROM data_table
) AS date_query
fonction scalaire causer toutes sortes de problèmes de performance, il est donc préférable d'envelopper la logique dans une fonction de valeur de Table en ligne si possible. C'est la fonction que j'ai utilisée pour remplacer certaines fonctions définies par L'utilisateur qui ont sélectionné les dates Min/Max à partir d'une liste de jusqu'à dix dates. Lorsque testé sur mon ensemble de données de 1 Million de lignes la fonction scalaire a pris plus de 15 minutes avant que je tue la requête le TVF en ligne a pris 1 minute qui est le même temps que la sélection du jeu de résultats dans un table temporaire. Pour utiliser cet appel la fonction d'un sous-jeu dans le SELECT ou une croix appliquent.
CREATE FUNCTION dbo.Get_Min_Max_Date
(
@Date1 datetime,
@Date2 datetime,
@Date3 datetime,
@Date4 datetime,
@Date5 datetime,
@Date6 datetime,
@Date7 datetime,
@Date8 datetime,
@Date9 datetime,
@Date10 datetime
)
RETURNS TABLE
AS
RETURN
(
SELECT Max(DateValue) Max_Date,
Min(DateValue) Min_Date
FROM (
VALUES (@Date1),
(@Date2),
(@Date3),
(@Date4),
(@Date5),
(@Date6),
(@Date7),
(@Date8),
(@Date9),
(@Date10)
) AS Dates(DateValue)
)
DECLARE @TableName TABLE (Number INT, Date1 DATETIME, Date2 DATETIME, Date3 DATETIME, Cost MONEY)
INSERT INTO @TableName
SELECT 1, '20000101', '20010101','20020101',100 UNION ALL
SELECT 2, '20000101', '19900101','19980101',99
SELECT Number,
Cost ,
(SELECT MAX([Date])
FROM (SELECT Date1 AS [Date]
UNION ALL
SELECT Date2
UNION ALL
SELECT Date3
)
D
)
[Most Recent Date]
FROM @TableName
pour T-SQL (MSSQL 2008+)
SELECT
(SELECT
MAX(MyMaxName)
FROM ( VALUES
(MAX(iSortCode)),
(MAX(Field2))
) MyAlias(MyMaxName)
)
FROM MyTable1
SELECT
CASE
WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
WHEN Date2 >= Date3 THEN Date2
ELSE Date3
END AS MostRecentDate
il est un peu plus facile d'écrire et de sauter les étapes d'évaluation que l'énoncé du cas est évalué dans l'ordre.
malheureusement réponse de Lasse , bien qu'apparemment évidente, a un défaut crucial. Il ne peut pas gérer les valeurs nulles. Toute valeur nulle simple entraîne le retour de Date1. Malheureusement, toute tentative de corriger ce problème tend à devenir extrêmement désordonné et n'a pas d'échelle à 4 ou plus des valeurs très bien.
la première réponse de databyss semblait (et est) bonne. Toutefois, il n'était pas clair si la réponse pouvait facilement être extrapolée 3 valeurs d'une jointure multi-table au lieu des 3 valeurs plus simples d'une seule table. Je voulais éviter de tourner une telle requête dans une sous-requête pour obtenir le max de 3 colonnes, aussi, j'étais assez sûr databyss excellente idée pourrait être nettoyé un peu.
donc sans plus attendre, voici ma solution (dérivée de l'idée de databyss).
Il utilise des constantes de sélection de jointures croisées pour simuler l'effet d'une jointure multi-tables. La chose importante à noter est que tous les alias nécessaires se déroulent correctement (ce qui n'est pas toujours le cas) et cela permet de garder le motif très simple et assez évolutif à travers des colonnes supplémentaires.
DECLARE @v1 INT ,
@v2 INT ,
@v3 INT
--SET @v1 = 1 --Comment out SET statements to experiment with
--various combinations of NULL values
SET @v2 = 2
SET @v3 = 3
SELECT ( SELECT MAX(Vals)
FROM ( SELECT v1 AS Vals
UNION
SELECT v2
UNION
SELECT v3
) tmp
WHERE Vals IS NOT NULL -- This eliminates NULL warning
) AS MaxVal
FROM ( SELECT @v1 AS v1
) t1
CROSS JOIN ( SELECT @v2 AS v2
) t2
CROSS JOIN ( SELECT @v3 AS v3
) t3
problème: choisissez la valeur du taux minimum donné à une entité Conditions: les tarifs D'Agence peuvent être nuls
[MinRateValue] =
CASE
WHEN ISNULL(FitchRating.RatingValue, 100) < = ISNULL(MoodyRating.RatingValue, 99)
AND ISNULL(FitchRating.RatingValue, 100) < = ISNULL(StandardPoorsRating.RatingValue, 99)
THEN FitchgAgency.RatingAgencyName
WHEN ISNULL(MoodyRating.RatingValue, 100) < = ISNULL(StandardPoorsRating.RatingValue , 99)
THEN MoodyAgency.RatingAgencyName
ELSE ISNULL(StandardPoorsRating.RatingValue, 'N/A')
END
Inspiré par cette réponse à partir de Nat
si vous utilisez SQL Server 2005, vous pouvez utiliser la fonctionnalité UNPIVOT. Voici un exemple complet:
create table dates
(
number int,
date1 datetime,
date2 datetime,
date3 datetime
)
insert into dates values (1, '1/1/2008', '2/4/2008', '3/1/2008')
insert into dates values (1, '1/2/2008', '2/3/2008', '3/3/2008')
insert into dates values (1, '1/3/2008', '2/2/2008', '3/2/2008')
insert into dates values (1, '1/4/2008', '2/1/2008', '3/4/2008')
select max(dateMaxes)
from (
select
(select max(date1) from dates) date1max,
(select max(date2) from dates) date2max,
(select max(date3) from dates) date3max
) myTable
unpivot (dateMaxes For fieldName In (date1max, date2max, date3max)) as tblPivot
drop table dates
utilisant CROSS appliquer (pour 2005+) ....
SELECT MostRecentDate
FROM SourceTable
CROSS APPLY (SELECT MAX(d) MostRecentDate FROM (VALUES (Date1), (Date2), (Date3)) AS a(d)) md
de SQL Server 2012, nous pouvons utiliser IIF .
DECLARE @Date1 DATE='2014-07-03';
DECLARE @Date2 DATE='2014-07-04';
DECLARE @Date3 DATE='2014-07-05';
SELECT IIF(@Date1>@Date2,
IIF(@Date1>@Date3,@Date1,@Date3),
IIF(@Date2>@Date3,@Date2,@Date3)) AS MostRecentDate
s'il vous Plaît essayez d'utiliser UNPIVOT :
SELECT MAX(MaxDt) MaxDt
FROM tbl
UNPIVOT
(MaxDt FOR E IN
(Date1, Date2, Date3)
)AS unpvt;
vous pouvez créer une fonction où vous passez les dates et puis Ajouter la fonction à la déclaration select comme ci-dessous. sélectionnez Numéro de dbo.fxMost_Recent_Date( Date1, Date2, Date3), Cost 151930920"
create FUNCTION fxMost_Recent_Date
( @Date1 smalldatetime, @Date2 smalldatetime, @Date3 smalldatetime ) Renvoie smalldatetime COMME COMMENCER DECLARE @Result smalldatetime
declare @MostRecent smalldatetime
set @MostRecent='1/1/1900'
if @Date1>@MostRecent begin set @MostRecent=@Date1 end
if @Date2>@MostRecent begin set @MostRecent=@Date2 end
if @Date3>@MostRecent begin set @MostRecent=@Date3 end
RETURN @MostRecent
END
basé sur la ScottPletcher 's solution from http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24204894.html J'ai créé un ensemble de fonctions (par exemple GetMaxOfDates3 , GetMaxOfDates13 )pour trouver jusqu'à 13 valeurs de Date en utilisant UNION ALL. Voir T-SQL fonction pour obtenir le Maximum de valeurs de la même ligne Cependant, je n'ai pas considéré la solution UNPIVOT au moment de la rédaction de ces fonctions
une autre façon d'utiliser cas où
SELECT CASE true
WHEN max(row1) >= max(row2) THEN CASE true WHEN max(row1) >= max(row3) THEN max(row1) ELSE max(row3) end ELSE
CASE true WHEN max(row2) >= max(row3) THEN max(row2) ELSE max(row3) END END
FROM yourTable
Je ne sais pas s'il est sur SQL, etc... sur M$ACCESS help il y a une fonction appelée MAXA(Value1;Value2;...) qui est supposée faire cela.
l'Espoir peut aider quelqu'un.
P. D.: Les valeurs peuvent être des colonnes ou calculées, etc.
voici une bonne solution:
CREATE function [dbo].[inLineMax] (@v1 float,@v2 float,@v3 float,@v4 float)
returns float
as
begin
declare @val float
set @val = 0
declare @TableVal table
(value float )
insert into @TableVal select @v1
insert into @TableVal select @v2
insert into @TableVal select @v3
insert into @TableVal select @v4
select @val= max(value) from @TableVal
return @val
end