如何在几列的最大值中每行返回1个值:
的表
[Number, Date1, Date2, Date3, Cost]
我需要返回这样的东西:
[Number, Most_Recent_Date, Cost]
查询?
如何在几列的最大值中每行返回1个值:
的表
[Number, Date1, Date2, Date3, Cost]
我需要返回这样的东西:
[Number, Most_Recent_Date, Cost]
查询?
当前回答
还有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
其他回答
这里有一个很好的解决方案:
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 Server 2012我们可以使用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
请尝试使用UNPIVOT:
SELECT MAX(MaxDt) MaxDt
FROM tbl
UNPIVOT
(MaxDt FOR E IN
(Date1, Date2, Date3)
)AS unpvt;
这是一个古老的答案,在很多方面都有问题。
请参阅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
最后,针对以下内容:
SQL Server 2022 (16.x)预览 Azure SQL数据库 Azure SQL托管实例
我们也可以用GREATEST。与其他T-SQL函数类似,这里有一些重要的注意事项:
如果所有参数都具有相同的数据类型,并且 的类型为 支持 进行比较, GREATEST将返回该类型; 否则, 函数 将隐式地将所有参数转换为 最高优先级 的数据类型进行比较,并使用 这个 类型 作为返回类型; 如果一个或多个参数不是NULL,那么NULL参数将在比较过程中被忽略;如果所有参数都为NULL,则GREATEST将返回NULL;
在GREATEST中不支持以下类型进行比较:varchar(max)、varbinary(max)或nvarchar(max)超过8,000字节、游标、几何、地理、图像、非字节顺序的用户定义类型、ntext、table、text和xml。