[译]SQL Server: 找到多列中的最大值
默认分类
2020-01-15
660
0
问题
有时我们需要在相同数据类型的表中的不同列找到最大值或者最小值。例如我们有一个表,有 三列日期类型的字段:UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date。我们想要从表中检索数据并将其加载到另一个表中,但我们要在UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date三个字段中找到一个最大日期,作为新表中的LastUpdateDate。在SQL Server中我们可以通过各种方法处理。本篇文章中将汇总这些方法并比较性能。
解决方案
假设我们有一个表格,一共有五列,其中三列是Datetime类型。分别有三个程序更新这几列。 这三列分别为 UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date。 下面是建立表的代码和样列数据.
IF (OBJECT_ID('tempdb..##TestTable') IS NOT NULL) DROP TABLE ##TestTableCREATE TABLE ##TestTable ( ID INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(40), UpdateByApp1Date DATETIME, UpdateByApp2Date DATETIME, UpdateByApp3Date DATETIME
)
INSERT INTO ##TestTable(Name, UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date ) VALUES('ABC', '2015-08-05','2015-08-04', '2015-08-06'), ('NewCopmany', '2014-07-05','2012-12-09', '2015-08-14'), ('MyCompany', '2015-03-05','2015-01-14', '2015-07-26')
SELECT * FROM ##TestTable
方案 1
SELECT ID, (SELECT MAX(LastUpdateDate) FROM (VALUES (UpdateByApp1Date),(UpdateByApp2Date),(UpdateByApp3Date)) AS UpdateDate(LastUpdateDate)) AS LastUpdateDate FROM ##TestTable
方案2
使用UNPIVOT 实现
SELECT ID, MAX(UpdateDate) AS LastUpdateDate FROM ##TestTable UNPIVOT ( UpdateDate FOR DateVal IN ( UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date ) ) AS u GROUP BY ID, Name
方案3
使用UNION
SELECT ID, MAX(UpdateDate) AS LastUpdateDate FROM ( SELECT ID, UpdateByApp1Date AS UpdateDate FROM ##TestTable UNION SELECT ID, UpdateByApp2Date AS UpdateDate FROM ##TestTable UNION SELECT ID, UpdateByApp3Date AS UpdateDate FROM ##TestTable ) ud GROUP BY ID
方案4
还是UNION
SELECT ID, ( SELECT MAX(UpdateDate) AS LastUpdateDate FROM
( SELECT tt.UpdateByApp1Date AS UpdateDate UNION SELECT tt.UpdateByApp2Date UNION SELECT tt.UpdateByApp3Date ) ud ) LastUpdateDate FROM ##TestTable tt
性能比较
前两种方法用的代码较少,我们用来开发比较方便,那么性能怎么样呢,让我们比较下.
下面生成数据.
TRUNCATE TABLE ##TestTable然后我们把4个解决方案都放到一个语句里面执行,并开启’计划任务’DECLARE @DateFrom DATE = '2012-01-01', @DateTo DATE = '2015-08-14', @UpdateDate1 DATE, @UpdateDate2 DATE, @UpdateDate3 DATE, @i INT = 1
WHILE (@i < 1000000) --Value 1000000 is used for having enough data for testing. Please choose appropriate value for your server to avoid overloading it. BEGIN SET @UpdateDate1 = DATEADD(DAY, RAND(CHECKSUM(NEWID()))(1+DATEDIFF(DAY, @DateFrom, @DateTo)), @DateFrom) SET @UpdateDate2 = DATEADD(DAY, RAND(CHECKSUM(NEWID()))(1+DATEDIFF(DAY, @DateFrom, @DateTo)), @DateFrom) SET @UpdateDate3 = DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(DAY, @DateFrom, @DateTo)), @DateFrom)
INSERT INTO ##TestTable(Name, UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date ) VALUES(CAST(NEWID() AS NVARCHAR(36)), @UpdateDate1,@UpdateDate2,@UpdateDate3) SET @i=@i+1
END
--1 SELECT ID, (SELECT MAX(LastUpdateDate) FROM (VALUES (UpdateByApp1Date),(UpdateByApp2Date),(UpdateByApp3Date)) AS UpdateDate(LastUpdateDate)) AS LastUpdateDate FROM ##TestTable --2 SELECT ID, MAX(UpdateDate) AS LastUpdateDate FROM ##TestTable UNPIVOT ( UpdateDate FOR DateVal IN ( UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date ) ) AS u GROUP BY ID, Name --3 SELECT ID, MAX(UpdateDate) AS LastUpdateDate FROM ( SELECT ID, UpdateByApp1Date AS UpdateDate FROM ##TestTable UNION SELECT ID, UpdateByApp2Date AS UpdateDate FROM ##TestTable UNION SELECT ID, UpdateByApp3Date AS UpdateDate FROM ##TestTable ) ud GROUP BY ID --4 SELECT ID, ( SELECT MAX(UpdateDate) AS LastUpdateDate FROM ( SELECT tt.UpdateByApp1Date AS UpdateDate UNION SELECT tt.UpdateByApp2Date UNION SELECT tt.UpdateByApp3Date ) ud ) LastUpdateDate FROM ##TestTable tt结果如下.第一个占用7%,第二个11%,第三个16%,第四个66%
有上可见,本例中,我们第一个解决方案是最佳的选择,代码少而且速度快.
当然我们也可以带一些参数来执行查询,比如 ufnGetMaximumDate(@Date1 datetime, @Date2 datetime, @Date3 datetime) ,把查询弄成动态的.
结尾
在SQL Server中我们可以发现不同列相同数据类型可以使用各种方法获取最大值或者最小值.高性能和较少的代码必要的.我们可以看到第一个是最右解决方案. 大家也可以通过表结构,列索等来深入优化.
下一步
相关文章: