如何在几列的最大值中每行返回1个值:
的表
[Number, Date1, Date2, Date3, Cost]
我需要返回这样的东西:
[Number, Most_Recent_Date, Cost]
查询?
当前回答
For T-SQL (MSSQL 2008+)
SELECT
(SELECT
MAX(MyMaxName)
FROM ( VALUES
(MAX(Field1)),
(MAX(Field2))
) MyAlias(MyMaxName)
)
FROM MyTable1
其他回答
我更喜欢基于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
还有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
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
基于http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24204894.html的ScottPletcher的解决方案 我已经创建了一组函数(例如GetMaxOfDates3, GetMaxOfDates13)，以使用UNION ALL找到最多13个日期值。 参见T-SQL函数从同一行获取最大值 然而，在编写这些函数时，我还没有考虑过UNPIVOT解决方案
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
不幸的是，拉斯的答案虽然看似显而易见，但却有一个关键的缺陷。它不能处理NULL值。任何一个NULL值都会返回Date1。不幸的是，任何试图解决这个问题的尝试往往会变得非常混乱，并且不能很好地扩展到4或更多的值。
Databyss的第一个答案看起来(现在也是)不错。但是，不清楚答案是否可以轻易地从多表连接中推断出3个值，而不是从单个表中推断出3个值。我想避免将这样的查询变成子查询，只是为了得到最多3列，而且我非常确定databyss的优秀想法可以被清理一点。
废话不多说，下面是我的解决方案(源自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
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询，还可以获得总行数
- 当恢复sql时，psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的
