多列的SQL MAX ?

这是一个古老的答案，在很多方面都有问题。

参见https://stackoverflow.com/a/6871572/194653，它有更多的赞，与sql server 2008+一起工作，并处理空值等。

原创但有问题的答案:

你可以使用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

以下两个示例中的任何一个都可以工作:

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的答案的附加项。

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

如果你正在使用MySQL或PostgreSQL或Oracle或BigQuery，你可以使用

SELECT GREATEST(col1, col2 ...) FROM table

如果您正在使用SQL Server 2005，您可以使用UNPIVOT特性。下面是一个完整的例子:

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

还有3个方法，其中UNPIVOT(1)是目前为止最快的，其次是模拟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

解决方案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

解决方案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

解决方案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

SELECT
CASE
WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
WHEN Date2 >= Date3 THEN Date2
ELSE Date3
END AS MostRecentDate

这样写起来稍微容易一些，因为case语句是按顺序求值的，因此跳过了求值步骤。

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

create FUNCTION  fxMost_Recent_Date

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

结束

标量函数会导致各种性能问题，所以如果可能的话，最好将逻辑封装到内联表值函数中。这是我用来替换一些用户定义函数，这些函数从多达10个日期的列表中选择了最小/最大日期。当在我的100万行数据集上测试时，标量函数在我杀死查询之前花费了15分钟以上，Inline 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)
)

基于ScottPletcher的解决方案从http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24204894.html 我已经创建了一组函数(例如GetMaxOfDates3, GetMaxOfDates13)，以使用UNION ALL找到最多13个日期值。 看到T-SQL函数从同一行中获取最大值 然而，在编写这些函数

CREATE FUNCTION GetMaxOfDates13 (
@value01 DateTime = NULL,
@value02 DateTime = NULL,
@value03 DateTime = NULL,
@value04 DateTime = NULL,
@value05 DateTime = NULL,
@value06 DateTime = NULL,
@value07 DateTime = NULL,
@value08 DateTime = NULL,
@value09 DateTime = NULL,
@value10 DateTime = NULL,
@value11 DateTime = NULL,
@value12 DateTime = NULL,
@value13 DateTime = NULL
)
RETURNS DateTime
AS
BEGIN
RETURN (
SELECT TOP 1 value
FROM (
SELECT @value01 AS value UNION ALL
SELECT @value02 UNION ALL
SELECT @value03 UNION ALL
SELECT @value04 UNION ALL
SELECT @value05 UNION ALL
SELECT @value06 UNION ALL
SELECT @value07 UNION ALL
SELECT @value08 UNION ALL
SELECT @value09 UNION ALL
SELECT @value10 UNION ALL
SELECT @value11 UNION ALL
SELECT @value12 UNION ALL
SELECT @value13
) AS [values]
ORDER BY value DESC
)
END –FUNCTION
GO
CREATE FUNCTION GetMaxOfDates3 (
@value01 DateTime = NULL,
@value02 DateTime = NULL,
@value03 DateTime = NULL
)
RETURNS DateTime
AS
BEGIN
RETURN dbo.GetMaxOfDates13(@value01,@value02,@value03,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
END –FUNCTION

下面是使用T-SQL和SQL Server实现Max功能的另一个很好的解决方案

SELECT [Other Fields],
(SELECT Max(v)
FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]
FROM [YourTableName]

值是表值构造函数。

指定要构造到表中的一组行值表达式。Transact-SQL表值构造函数允许在单个DML语句中指定多行数据。表值构造函数可以指定为INSERT…VALUES语句，或者作为MERGE语句的USING子句或FROM子句中的派生表。"

不幸的是，Lasse的回答，虽然看起来很明显，有一个关键的缺陷。它不能处理NULL值。任何一个NULL值都会返回Date1。不幸的是，任何试图解决这个问题的尝试往往会变得非常混乱，并且不能很好地扩展到4或更多的值。

Databyss的第一个答案看起来(现在也是)不错。但是，不清楚答案是否可以轻易地从多表连接中推断出3个值，而不是从单个表中推断出3个值。我想避免将这样的查询变成子查询，只是为了得到最多3列，而且我非常确定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

[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中的这个答案

请尝试使用UNPIVOT:

SELECT MAX(MaxDt) MaxDt
FROM tbl
UNPIVOT
(MaxDt FOR E IN
(Date1, Date2, Date3)
)AS unpvt;

使用CROSS APPLY (2005+) ....

SELECT MostRecentDate
FROM SourceTable
CROSS APPLY (SELECT MAX(d) MostRecentDate FROM (VALUES (Date1), (Date2), (Date3)) AS a(d)) md

在SQL Server 2012中，我们可以使用国际金融协会。

 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

For T-SQL (MSSQL 2008+)

SELECT
(SELECT
MAX(MyMaxName)
FROM ( VALUES
(MAX(Field1)),
(MAX(Field2))
) MyAlias(MyMaxName)
)
FROM MyTable1

这里有一个很好的解决方案:

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.:值可以是列或计算值，等等。

另一种使用情况下的方法

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

我更喜欢基于case-when的解决方案，我的假设是，与其他可能的解决方案(如交叉应用、values()、自定义函数等)相比，它对可能的性能下降的影响应该最小。

下面是case-when版本，它在大多数可能的测试用例中处理空值:

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
;

结果是:

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

__abc0上表是一个员工工资表，列为salary1,salary2,salary3,salary4。下面的查询将返回四列中的最大值

select
(select Max(salval) from( values (max(salary1)),(max(salary2)),(max(salary3)),(max(Salary4)))alias(salval)) as largest_val
from EmployeeSalary

运行上述查询将输出largest_val(10001)

上述查询逻辑如下:

select Max(salvalue) from(values (10001),(5098),(6070),(7500))alias(salvalue)

输出将是10001

我的解决方案也可以处理空值比较。它可以被简化为一个单一的查询，但为了解释，我使用CTE。这个想法是在第一步中减少从3个数字到2个数字的比较，然后在第二步中从2个数字到1个数字的比较。

with x1 as
(
select 1 as N1, null as N2, 3 as N3
union
select 1 as N1, null as N2, null as N3
union
select null as N1, null as N2, null as N3
)
,x2 as
(
select
N1,N2,N3,
IIF(Isnull(N1,0)>=Isnull(N2,0),N1,N2) as max1,
IIF(Isnull(N2,0)>=Isnull(N3,0),N2,N3) as max2
from x1
)
,x3 as
(
select N1,N2,N3,max1,max2,
IIF(IsNull(max1,0)>=IsNull(max2,0),max1,max2) as MaxNo
from x2
)
select * from x3

输出:

最后，针对以下内容:

• SQL Server 2022 (16.x)预览
• Azure SQL数据库
• Azure SQL托管实例

我们也可以使用最大的。与其他T-SQL函数类似，这里有一些重要的注意事项:

• 如果所有参数都具有相同的数据类型，并且 的类型为 支持 进行比较， GREATEST将返回该类型;
• 否则， 函数 将隐式地将所有参数转换为 最高优先级 的数据类型进行比较，并使用 这个 类型 作为返回类型;
• 如果一个或多个参数不是NULL，那么NULL参数将在比较过程中被忽略;如果所有参数都为NULL，则GREATEST将返回NULL;

在GREATEST中不支持以下类型进行比较:varchar(max)、varbinary(max)或nvarchar(max)超过8,000字节、游标、几何、地理、图像、非字节顺序的用户定义类型、ntext、table、text和xml。