如何在几列的最大值中每行返回1个值:
的表
[Number, Date1, Date2, Date3, Cost]
我需要返回这样的东西:
[Number, Most_Recent_Date, Cost]
查询?
当前回答
我更喜欢基于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
其他回答
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)
)
请尝试使用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
我更喜欢基于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
这里有一个很好的解决方案:
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
推荐文章
- 对于PostgreSQL表来说，多大才算太大?
- SQL Server数据库备份恢复到低版本
- 在MySQL中检测值是否为number
- MySQL中两个日期之间的差异
- SQL Server:过滤sp_who2的输出
- 使用SQL查询查找最近的纬度/经度
- 在SQL Server上使用varchar(MAX) vs TEXT
- Visual Studio: ContextSwitchDeadlock
- Sql Server字符串到日期的转换
- 将一列的多个结果行连接为一列，按另一列分组
- 检查MySQL表是否存在而不使用“select from”语法?
- 如何将SQL Azure数据库复制到本地开发服务器?
- SQL Server 2008不能用新创建的用户登录
- 在PostgreSQL中快速发现表的行数
- 更改varchar列的大小为较低的长度
