如何在几列的最大值中每行返回1个值:
的表
[Number, Date1, Date2, Date3, Cost]
我需要返回这样的东西:
[Number, Most_Recent_Date, Cost]
查询?
如何在几列的最大值中每行返回1个值:
的表
[Number, Date1, Date2, Date3, Cost]
我需要返回这样的东西:
[Number, Most_Recent_Date, Cost]
查询?
当前回答
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.
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)
)
其他回答
这是一个古老的答案,在很多方面都有问题。
请参阅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
如果你正在使用MySQL或PostgreSQL或Oracle或BigQuery,你可以使用
SELECT GREATEST(col1, col2 ...) FROM table
我不知道它是否在SQL上,等等…在M$ACCESS帮助中,有一个称为MAXA(Value1;Value2;…)的函数,它应该这样做。
希望能帮助到别人。
p.d.:值可以是列或计算值,等等。
从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
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.
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)
)