如何在几列的最大值中每行返回1个值:
的表
[Number, Date1, Date2, Date3, Cost]
我需要返回这样的东西:
[Number, Most_Recent_Date, Cost]
查询?
如何在几列的最大值中每行返回1个值:
的表
[Number, Date1, Date2, Date3, Cost]
我需要返回这样的东西:
[Number, Most_Recent_Date, Cost]
查询?
当前回答
如果您正在使用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
其他回答
您可以创建一个函数,其中传递日期,然后将该函数添加到select语句,如下所示。 select Number, dbo.fxMost_Recent_Date(Date1,Date2,Date3),成本
create FUNCTION fxMost_Recent_Date
( @Date1 smalldatetime, @Date2 smalldatetime, @Date3 smalldatetime ) 返回smalldatetime 作为 开始 DECLARE @Result smalldatetime
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
END
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)
)
我更喜欢基于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
SELECT
CASE
WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
WHEN Date2 >= Date3 THEN Date2
ELSE Date3
END AS MostRecentDate
这样写起来稍微容易一些,因为case语句是按顺序求值的,因此跳过了求值步骤。
从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