如何在几列的最大值中每行返回1个值:
的表
[Number, Date1, Date2, Date3, Cost]
我需要返回这样的东西:
[Number, Most_Recent_Date, Cost]
查询?
如何在几列的最大值中每行返回1个值:
的表
[Number, Date1, Date2, Date3, Cost]
我需要返回这样的东西:
[Number, Most_Recent_Date, Cost]
查询?
当前回答
另一种使用CASE WHEN的方法
SELECT CASE true
WHEN max(row1) >= max(row2) THEN CASE true WHEN max(row1) >= max(row3) THEN max(row1) ELSE max(row3) end ELSE
CASE true WHEN max(row2) >= max(row3) THEN max(row2) ELSE max(row3) END END
FROM yourTable
其他回答
以下两个示例中的任何一个都可以工作:
SELECT MAX(date_columns) AS max_date
FROM ( (SELECT date1 AS date_columns
FROM data_table )
UNION
( SELECT date2 AS date_columns
FROM data_table
)
UNION
( SELECT date3 AS date_columns
FROM data_table
)
) AS date_query
第二个是对拉塞夫克的答案的补充。
SELECT MAX(MostRecentDate)
FROM ( 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
FROM data_table
) AS date_query
For T-SQL (MSSQL 2008+)
SELECT
(SELECT
MAX(MyMaxName)
FROM ( VALUES
(MAX(Field1)),
(MAX(Field2))
) MyAlias(MyMaxName)
)
FROM MyTable1
基于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
下面是使用T-SQL和SQL Server实现Max功能的另一个很好的解决方案
SELECT [Other Fields],
(SELECT Max(v)
FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]
FROM [YourTableName]
Values是表值构造函数。
"指定要构造到表中的一组行值表达式。Transact-SQL表值构造函数允许在单个DML语句中指定多行数据。表值构造函数可以指定为INSERT…VALUES语句,或者作为MERGE语句的USING子句或FROM子句中的派生表。
请尝试使用UNPIVOT:
SELECT MAX(MaxDt) MaxDt
FROM tbl
UNPIVOT
(MaxDt FOR E IN
(Date1, Date2, Date3)
)AS unpvt;