如何在几列的最大值中每行返回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)
)
其他回答
另一种使用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
下面是使用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子句中的派生表。
上表为员工工资表,列为salary1、salary2、salary3、salary4。下面的查询将返回四列中的最大值
select
(select Max(salval) from( values (max(salary1)),(max(salary2)),(max(salary3)),(max(Salary4)))alias(salval)) as largest_val
from EmployeeSalary
运行上述查询将输出largest_val(10001)
上述查询逻辑如下:
select Max(salvalue) from(values (10001),(5098),(6070),(7500))alias(salvalue)
输出将是10001
For T-SQL (MSSQL 2008+)
SELECT
(SELECT
MAX(MyMaxName)
FROM ( VALUES
(MAX(Field1)),
(MAX(Field2))
) MyAlias(MyMaxName)
)
FROM MyTable1
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