SQL MAX of multiple columns?
如何在几列的最大值的每一行中返回1值：
表名
1
| [NUMBER, Date1, Date2, Date3, Cost] |
我需要返回以下内容：
1
| [NUMBER, Most_Recent_Date, Cost] |
查询？
这是使用T-SQL和SQL Server的Max功能的另一个不错的解决方案
1
2
3
4
| SELECT [Other FIELDS],
(SELECT MAX(v)
FROM (VALUES (date1), (date2), (date3),...) AS VALUE(v)) AS [MaxDate]
FROM [YourTableName] |
好了，您可以使用CASE语句：
1
2
3
4
5
6
7
| 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 |
[对于Microsoft SQL Server 2008及更高版本，您可以在下面考虑Sven的简单答案。]
如果您使用的是MySQL，则可以使用
1
| SELECT GREATEST(col1, col2 ...) FROM TABLE |
还有3种方法，其中UNPIVOT(1)是迄今为止最快的，其次是Simulated Unpivot(3)，它比(1)慢得多，但仍比(2)快
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| 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 |
解决方案1(UNPIVOT)
1
2
3
4
5
6
7
8
| SELECT NUMBER ,
MAX(dDate) maxDate ,
cost
FROM dates UNPIVOT ( dDate FOR nDate IN ( Date1, Date2,
Date3 ) ) AS u
GROUP BY NUMBER ,
cost
GO |
解决方案2(每行子查询)
1
2
3
4
5
6
7
8
9
10
11
12
| 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 |
解决方案3(模拟UNPIVOT)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| ;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 |
以下两个示例中的任何一个都可以工作：
1
2
3
4
5
6
7
8
9
10
11
12
| 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 |
第二个是lassevk的答案的附件。
1
2
3
4
5
6
7
8
9
10
11
| 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 |
对于T-SQL(MSSQL 2008+)
1
2
3
4
5
6
7
8
9
| SELECT
(SELECT
MAX(MyMaxName)
FROM ( VALUES
(MAX(Field1)),
(MAX(Field2))
) MyAlias(MyMaxName)
)
FROM MyTable1 |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| 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 |
标量函数会导致各种性能问题，因此最好将逻辑包装到内联表值函数中。这是我用来替换一些用户定义函数的函数，这些函数从最多十个日期的列表中选择了最小/最大日期。在我的100万行数据集上进行测试时，标量函数花费了15分钟以上的时间才杀死了该查询，而内联TVF花了1分钟的时间与将结果集选择到临时表中的时间相同。要使用此调用，请从SELECT中的子查询或CROSS APPLY中调用该函数。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
| 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)
) |
1
2
3
4
5
6
| SELECT
CASE
WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
WHEN Date2 >= Date3 THEN Date2
ELSE Date3
END AS MostRecentDate |
这样写起来稍微容易些，因为按顺序评估case语句，所以跳过评估步骤。
不幸的是，拉瑟的答案虽然看似显而易见，但却存在严重缺陷。它不能处理NULL值。任何单个NULL值都会导致返回Date1。不幸的是，任何解决该问题的尝试都会变得非常混乱，并且不能很好地扩展到4个或更多的值。
databyss的第一个答案看起来(并且是)很好。但是，尚不清楚答案是否可以轻松地从多表联接中推断出3个值，而不是从单个表中推断出更简单的3个值。我想避免将这样的查询转换为子查询，只是为了获得最多3列，而且我很确定databyss的绝妙想法可以被清除。
因此，事不宜迟，这是我的解决方案(源自databyss的想法)。
它使用交叉联接选择常量来模拟多表联接的效果。需要注意的重要一点是，所有必要的别名都可以正确执行(并非总是如此)，这可以使模式相当简单，并且可以通过其他列进行相当扩展。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| 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 |
问题：选择提供给实体的最低汇率
要求：代理商费率可以为空
1
2
3
4
5
6
7
8
9
10
11
| [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 |
受到Nat的回答的启发
使用CROSS APPLY(适用于2005 +)....
1
2
3
| SELECT MostRecentDate
FROM SourceTable
CROSS APPLY (SELECT MAX(d) MostRecentDate FROM (VALUES (Date1), (Date2), (Date3)) AS a(d)) md |
在SQL Server 2012中，我们可以使用IIF。
1
2
3
4
5
6
7
| 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 |
如果使用的是SQL Server 2005，则可以使用UNPIVOT功能。这是一个完整的示例：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| 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 |
请尝试使用UNPIVOT：
1
2
3
4
5
6
| SELECT MAX(MaxDt) MaxDt
FROM tbl
UNPIVOT
(MaxDt FOR E IN
(Date1, Date2, Date3)
)AS unpvt; |
我更喜欢基于案例的解决方案，当时我的假设是，与其他可能的解决方案(例如具有交叉应用，values()，自定义函数等的解决方案)相比，它对可能的性能下降的影响最小。
这是区分大小写的版本，用于处理大多数可能的测试用例的空值：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
| SELECT
CASE
WHEN Date1 > COALESCE(Date2,'0001-01-01') AND Date1 > COALESCE(Date3,'0001-01-01') THEN Date1
WHEN Date2 > COALESCE(Date3,'0001-01-01') THEN Date2
ELSE Date3
END AS MostRecentDate
, *
FROM
(VALUES
( 1, CAST('2001-01-01' AS DATE), CAST('2002-01-01' AS DATE), CAST('2003-01-01' AS DATE))
,( 2, CAST('2001-01-01' AS DATE), CAST('2003-01-01' AS DATE), CAST('2002-01-01' AS DATE))
,( 3, CAST('2002-01-01' AS DATE), CAST('2001-01-01' AS DATE), CAST('2003-01-01' AS DATE))
,( 4, CAST('2002-01-01' AS DATE), CAST('2003-01-01' AS DATE), CAST('2001-01-01' AS DATE))
,( 5, CAST('2003-01-01' AS DATE), CAST('2001-01-01' AS DATE), CAST('2002-01-01' AS DATE))
,( 6, CAST('2003-01-01' AS DATE), CAST('2002-01-01' AS DATE), CAST('2001-01-01' AS DATE))
,( 11, CAST(NULL AS DATE), CAST('2002-01-01' AS DATE), CAST('2003-01-01' AS DATE))
,( 12, CAST(NULL AS DATE), CAST('2003-01-01' AS DATE), CAST('2002-01-01' AS DATE))
,( 13, CAST('2003-01-01' AS DATE), CAST(NULL AS DATE), CAST('2002-01-01' AS DATE))
,( 14, CAST('2002-01-01' AS DATE), CAST(NULL AS DATE), CAST('2003-01-01' AS DATE))
,( 15, CAST('2003-01-01' AS DATE), CAST('2002-01-01' AS DATE), CAST(NULL AS DATE))
,( 16, CAST('2002-01-01' AS DATE), CAST('2003-01-01' AS DATE), CAST(NULL AS DATE))
,( 21, CAST('2003-01-01' AS DATE), CAST(NULL AS DATE), CAST(NULL AS DATE))
,( 22, CAST(NULL AS DATE), CAST('2003-01-01' AS DATE), CAST(NULL AS DATE))
,( 23, CAST(NULL AS DATE), CAST(NULL AS DATE), CAST('2003-01-01' AS DATE))
,( 31, CAST(NULL AS DATE), CAST(NULL AS DATE), CAST(NULL AS DATE))
) AS demoValues(id, Date1,Date2,Date3)
ORDER BY id
; |
结果是：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| MostRecent id Date1 Date2 Date3
2003-01-01 1 2001-01-01 2002-01-01 2003-01-01
2003-01-01 2 2001-01-01 2003-01-01 2002-01-01
2003-01-01 3 2002-01-01 2001-01-01 2002-01-01
2003-01-01 4 2002-01-01 2003-01-01 2001-01-01
2003-01-01 5 2003-01-01 2001-01-01 2002-01-01
2003-01-01 6 2003-01-01 2002-01-01 2001-01-01
2003-01-01 11 NULL 2002-01-01 2003-01-01
2003-01-01 12 NULL 2003-01-01 2002-01-01
2003-01-01 13 2003-01-01 NULL 2002-01-01
2003-01-01 14 2002-01-01 NULL 2003-01-01
2003-01-01 15 2003-01-01 2002-01-01 NULL
2003-01-01 16 2002-01-01 2003-01-01 NULL
2003-01-01 21 2003-01-01 NULL NULL
2003-01-01 22 NULL 2003-01-01 NULL
2003-01-01 23 NULL NULL 2003-01-01
NULL 31 NULL NULL NULL |
您可以创建一个传递日期的函数，然后将该函数添加到select语句中，如下所示。
选择数字，dbo.fxMost_Recent_Date(Date1，Date2，Date3)，成本
1
| CREATE FUNCTION fxMost_Recent_Date |
(
@ Date1 smalldatetime，
@ Date2 smalldatetime，
@ Date3 smalldatetime
)
返回smalldatetime
如
开始
声明@Result smalldatetime
1
2
3
4
5
6
7
8
| 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 |
结束
使用CASE WHEN的另一种方式
1
2
3
4
| 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 |
基于来自http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24204894.html的ScottPletcher解决方案
我创建了一组函数(例如GetMaxOfDates3，GetMaxOfDates13)，以使用UNION ALL查找最多13个日期值。
请参阅T-SQL函数以从同一行获取最大值
但是在编写这些功能时我还没有考虑过UNPIVOT解决方案
 上表是一个以薪金1，薪金2，薪金3，薪金4为列的员工薪水表。下面的查询将返回四列中的最大值
1
2
3
| SELECT
(SELECT MAX(salval) FROM( VALUES (MAX(salary1)),(MAX(salary2)),(MAX(salary3)),(MAX(Salary4)))alias(salval)) AS largest_val
FROM EmployeeSalary |
在上面的查询中运行将输出为largest_val(10001)
以上查询的逻辑如下：
1
| SELECT MAX(salvalue) FROM(VALUES (10001),(5098),(6070),(7500))alias(salvalue) |
输出将是10001
这是一个很好的解决方案：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| 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 |
我不知道它是否在SQL上，等等...在M $ ACCESS帮助上，有一个叫做MAXA(Value1;Value2;...)的函数应该这样做。
希望可以帮助某人。
P.D .：值可以是列或计算得出的值，等等。
|
