SQL 多列的SQL MAX？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/71022/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL MAX of multiple columns?
提问by BenB
How do you return 1 value per row of the max of several columns:
您如何在几列的最大值中每行返回 1 个值：
TableName
表名
[Number, Date1, Date2, Date3, Cost]
I need to return something like this:
我需要返回这样的东西：
[Number, Most_Recent_Date, Cost]
Query?
询问？
采纳答案by Lasse V. Karlsen
Well, you can use the CASE statement:
好吧，您可以使用 CASE 语句：
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
[For Microsoft SQL Server 2008and above, you may consider Sven's simpler answer below.]
[对于 Microsoft SQL Server 2008及更高版本，您可以考虑下面 Sven 的简单答案。]
回答by Sven
Here is another nice solution for the Maxfunctionality using T-SQL and SQL Server
这是Max使用 T-SQL 和 SQL Server的功能的另一个不错的解决方案
SELECT [Other Fields],
(SELECT Max(v)
FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]
FROM [YourTableName]
回答by bajafresh4life
If you're using MySQL, you can use
如果您使用的是 MySQL，则可以使用
SELECT GREATEST(col1, col2 ...) FROM table
回答by Niikola
There are 3 more methods where UNPIVOT(1) is the fastest by far, followed by Simulated Unpivot (3) which is much slower than (1) but still faster than (2)
还有 3 种方法，其中UNPIVOT(1) 是迄今为止最快的，其次是 Simulated Unpivot (3)，它比 (1) 慢得多，但仍然比 (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)
解决方案 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 (Sub query per row)
解决方案2（每行子查询）
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 (Simulated UNPIVOT)
解决方案 3（模拟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
回答by databyss
Either of the two samples below will work:
以下两个示例中的任何一个都可以使用：
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
The second is an add-on to lassevk'sanswer.
第二个是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
回答by doker
For T-SQL (MSSQL 2008+)
对于 T-SQL (MSSQL 2008+)
SELECT
(SELECT
MAX(MyMaxName)
FROM ( VALUES
(MAX(Field1)),
(MAX(Field2))
) MyAlias(MyMaxName)
)
FROM MyTable1
回答by Martin Smith
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
回答by MartinC
Scalar Function cause all sorts of performance issues, so its better to wrap the logic into an Inline Table Valued Function if possible. This is the function I used to replace some User Defined Functions which selected the Min/Max dates from a list of upto ten dates. When tested on my dataset of 1 Million rows the Scalar Function took over 15 minutes before I killed the query the Inline TVF took 1 minute which is the same amount of time as selecting the resultset into a temporary table. To use this call the function from either a subquery in the the SELECT or a CROSS APPLY.
标量函数会导致各种性能问题，因此如果可能，最好将逻辑包装到内联表值函数中。这是我用来替换一些用户定义函数的函数，这些函数从最多十个日期的列表中选择最小/最大日期。在我的 100 万行数据集上进行测试时，标量函数在我终止查询之前花费了 15 多分钟，而内联 TVF 花费了 1 分钟，这与将结果集选择到临时表中的时间相同。要使用此调用，请从 SELECT 或 CROSS APPLY 中的子查询调用该函数。
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)
)
回答by Nat
SELECT
CASE
WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
WHEN Date2 >= Date3 THEN Date2
ELSE Date3
END AS MostRecentDate
This is slightly easier to write out and skips evaluation steps as the case statement is evaluated in order.
由于 case 语句是按顺序计算的，所以写出来会稍微容易一些，并且会跳过计算步骤。
回答by Disillusioned
Unfortunately Lasse's answer, though seemingly obvious, has a crucial flaw. It cannot handle NULL values. Any single NULL value results in Date1 being returned. Unfortunately any attempt to fix that problem tends to get extremely messy and doesn't scale to 4 or more values very nicely.
不幸的是，Lasse 的回答虽然看起来很明显，但有一个关键的缺陷。它无法处理 NULL 值。任何单个 NULL 值都会导致 Date1 被返回。不幸的是，任何解决该问题的尝试都会变得非常混乱，并且不能很好地扩展到 4 个或更多值。
databyss's first answerlooked (and is) good. However, it wasn't clear whether the answer would easily extrapolate to 3 values from a multi-table join instead of the simpler 3 values from a single table. I wanted to avoid turning such a query into a sub-query just to get the max of 3 columns, also I was pretty sure databyss's excellent idea could be cleaned up a bit.
databyss 的第一个答案看起来（而且现在）很好。但是，尚不清楚答案是否可以轻松地从多表连接中推断出 3 个值，而不是从单个表中推断出更简单的 3 个值。我想避免将这样的查询变成子查询，只是为了获得最多 3 列，而且我很确定 databyss 的好主意可以稍微清理一下。
So without further ado, here's my solution (derived from databyss's idea).
It uses cross-joins selecting constants to simulate the effect of a multi-table join. The important thing to note is that all the necessary aliases carry through correctly (which is not always the case) and this keeps the pattern quite simple and fairly scalable through additional columns.
所以不用多说，这是我的解决方案（源自databyss的想法）。
它使用交叉连接选择常量来模拟多表连接的效果。需要注意的重要一点是，所有必要的别名都正确执行（情况并非总是如此），这使模式非常简单，并且通过附加列具有相当的可扩展性。
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
