[译]SQL Server: 找到多列中的最大值

问题

有时我们需要在相同数据类型的表中的不同列找到最大值或者最小值。例如我们有一个表，有三列日期类型的字段：UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date。我们想要从表中检索数据并将其加载到另一个表中，但我们要在UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date三个字段中找到一个最大日期，作为新表中的LastUpdateDate。在SQL Server中我们可以通过各种方法处理。本篇文章中将汇总这些方法并比较性能。

解决方案

假设我们有一个表格，一共有五列，其中三列是Datetime类型。分别有三个程序更新这几列。这三列分别为 UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date。下面是建立表的代码和样列数据.

IF (OBJECT_ID('tempdb..##TestTable') IS NOT NULL) DROP TABLE ##TestTable
CREATE 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
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

然后我们把4个解决方案都放到一个语句里面执行,并开启’计划任务’

--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中我们可以发现不同列相同数据类型可以使用各种方法获取最大值或者最小值.高性能和较少的代码必要的.我们可以看到第一个是最右解决方案. 大家也可以通过表结构,列索等来深入优化.