如何在几列的最大值中每行返回 1 个值:
表名
[Number, Date1, Date2, Date3, Cost]
我需要返回这样的东西:
[Number, Most_Recent_Date, Cost]
询问?
这是使用 T-SQL 和 SQL Server 的 Max
功能的另一个不错的解决方案
SELECT [Other Fields],
(SELECT Max(v)
FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate]
FROM [YourTableName]
"指定要构造到表中的一组行值表达式。Transact-SQL 表值构造函数允许在单个 DML 语句中指定多行数据。表值构造函数可以指定为INSERT ... VALUES 语句,或作为 MERGE 语句的 USING 子句或 FROM 子句中的派生表。”
如果您使用的是 MySQL 或 PostgreSQL 或 Oracle,则可以使用
SELECT GREATEST(col1, col2 ...) FROM table
这是一个古老的答案,在很多方面都被打破了。
请参阅https://stackoverflow.com/a/6871572/194653,它有更多的赞成票并与 sql server 2008+ 一起使用并处理空值等。
原始但有问题的答案:
好吧,您可以使用 CASE 语句:
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
WHEN Date1 > Date2 AND Date1 > Date3 THEN Date1; WHEN Date2 > Date3 THEN Date3; ELSE Date3
还不够吗?
还有 3 种方法,其中 UNPIVOT
(1) 是迄今为止最快的,其次是 Simulated 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
标量函数会导致各种性能问题,因此如果可能,最好将逻辑包装到内联表值函数中。这是我用来替换一些用户定义函数的函数,这些函数从最多十个日期的列表中选择最小/最大日期。在我的 100 万行数据集上进行测试时,标量函数在我终止查询之前花费了 15 分钟以上,内联 TVF 花费了 1 分钟,这与将结果集选择到临时表中的时间相同。要使用此函数,请从 SELECT 或 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)
)
以下两个示例中的任何一个都可以使用:
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
第二个是 lassevk's 答案的附加内容。
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
对于 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
SELECT
CASE
WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
WHEN Date2 >= Date3 THEN Date2
ELSE Date3
END AS MostRecentDate
这更容易写出并跳过评估步骤,因为 case 语句是按顺序评估的。
不幸的是,Lasse's answer 虽然看起来很明显,但有一个关键缺陷。它不能处理 NULL 值。任何单个 NULL 值都会导致 Date1 被返回。不幸的是,任何解决该问题的尝试都会变得非常混乱,并且不能很好地扩展到 4 个或更多值。
databyss's first answer 看起来(现在)不错。但是,尚不清楚答案是否可以轻松地推断为来自多表连接的 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
问题:选择给予实体的最小费率值要求:代理费率可以为空
[MinRateValue] =
CASE
WHEN ISNULL(FitchRating.RatingValue, 100) < = ISNULL(MoodyRating.RatingValue, 99)
AND ISNULL(FitchRating.RatingValue, 100) < = ISNULL(StandardPoorsRating.RatingValue, 99)
THEN FitchgAgency.RatingAgencyName
WHEN ISNULL(MoodyRating.RatingValue, 100) < = ISNULL(StandardPoorsRating.RatingValue , 99)
THEN MoodyAgency.RatingAgencyName
ELSE ISNULL(StandardPoorsRating.RatingValue, 'N/A')
END
灵感来自 Nat 的 this answer
如果您使用的是 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
使用 CROSS APPLY(适用于 2005+)....
SELECT MostRecentDate
FROM SourceTable
CROSS APPLY (SELECT MAX(d) MostRecentDate FROM (VALUES (Date1), (Date2), (Date3)) AS a(d)) md
VALUES
会阻止这在 2005 年起作用。您必须将 VALUES
替换为等效的 SELECT value UNION ALL SELECT value UNION ALL …
系列。
从 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
DECLARE @Date1 DATE='2014-08-01'; DECLARE @Date2 DATE=null; DECLARE @Date3 DATE='2014-07-05'; /*this gets returned*/
select IIF(@Date1 > @Date2 or @Date2 is null, IIF(@Date1 > @Date3 or @Date3 is null, @Date1, @Date3), IIF(@Date2 > @Date3 or @Date3 is null, @Date2, @Date3)) as MostRecentDate
请尝试使用 UNPIVOT
:
SELECT MAX(MaxDt) MaxDt
FROM tbl
UNPIVOT
(MaxDt FOR E IN
(Date1, Date2, Date3)
)AS unpvt;
我更喜欢基于案例的解决方案,我的假设是,与其他可能的解决方案(如交叉应用、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
基于来自 http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24204894.html 的 ScottPletcher 的解决方案,我创建了一组函数(例如 GetMaxOfDates3 、 GetMaxOfDates13 )以使用 UNION ALL 查找最多 13 个日期值。请参阅 T-SQL function to Get Maximum of values from the same row 但是在编写这些函数时我还没有考虑过 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
您可以创建一个传递日期的函数,然后将该函数添加到 select 语句中,如下所示。选择数字,dbo.fxMost_Recent_Date(Date1,Date2,Date3),成本
create FUNCTION fxMost_Recent_Date
(@Date1 smalldatetime,@Date2 smalldatetime,@Date3 smalldatetime)返回 smalldatetime 作为开始声明 @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
结尾
另一种使用 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
我的解决方案也可以处理空值比较。它可以通过编写为一个查询来简化,但为了解释,我使用的是 CTE。这个想法是在步骤 1 中将比较从 3 个数字减少到 2 个数字,然后在步骤 2 中从 2 个数字减少到 1 个数字。
with x1 as
(
select 1 as N1, null as N2, 3 as N3
union
select 1 as N1, null as N2, null as N3
union
select null as N1, null as N2, null as N3
)
,x2 as
(
select
N1,N2,N3,
IIF(Isnull(N1,0)>=Isnull(N2,0),N1,N2) as max1,
IIF(Isnull(N2,0)>=Isnull(N3,0),N2,N3) as max2
from x1
)
,x3 as
(
select N1,N2,N3,max1,max2,
IIF(IsNull(max1,0)>=IsNull(max2,0),max1,max2) as MaxNo
from x2
)
select * from x3
输出:
https://i.stack.imgur.com/XcI1W.png
最后,针对以下内容:
SQL Server 2022 (16.x) 预览版
Azure SQL 数据库
Azure SQL 托管实例
我们也可以使用 GREATEST。与其他 T-SQL 函数类似,这里有几个重要的注意事项:
如果所有参数都具有相同的数据类型并且支持该类型进行比较,则 GREATEST 将返回该类型;
否则,该函数将在比较之前将所有参数隐式转换为优先级最高的数据类型,并将该类型用作返回类型;
如果一个或多个参数不为 NULL,则在比较过程中将忽略 NULL 参数;如果所有参数都为 NULL,则 GREATEST 将返回 NULL;
GREATEST 中不支持以下类型进行比较:varchar(max)、varbinary(max) 或 nvarchar(max) 超过 8,000 字节、光标、几何、地理、图像、非字节排序的用户定义类型、ntext、表、文本和 xml。
这是一个很好的解决方案:
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
我不知道它是否在 SQL 等上……在 M$ACCESS 帮助上有一个名为 MAXA(Value1;Value2;...)
的函数应该这样做。
希望可以帮助某人。
PD:值可以是列或计算值等。
MAXA
是 Excel function,而不是 Access。
https://i.stack.imgur.com/peyNn.png
select
(select Max(salval) from( values (max(salary1)),(max(salary2)),(max(salary3)),(max(Salary4)))alias(salval)) as largest_val
from EmployeeSalary
运行上面的查询将输出为 maximum_val(10001)
上述查询逻辑如下:
select Max(salvalue) from(values (10001),(5098),(6070),(7500))alias(salvalue)
输出将是 10001