在一个SQL Server表中一行的多个列找出最大值

有时候我们需要从多个相同的列里（这些列的数据类型相同）找出最大的那个值，并显示

这里给出一个例子

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

结果如下所示

有三种方法可以实现

方法一

SELECT ID ,

Name ,

( SELECT MAX(LastUpdateDate)

FROM ( VALUES ( UpdateByApp1Date), ( UpdateByApp2Date),

( UpdateByApp3Date) ) AS UpdateDate ( LastUpdateDate )

) AS LastUpdateDate

FROM ##TestTable

方法二

SELECT ID ,

[Name] ,

MAX(UpdateDate) AS LastUpdateDate

FROM ##TestTable UNPIVOT ( UpdateDate FOR DateVal IN

( UpdateByApp1Date,UpdateByApp2Date,UpdateByApp3Date ) ) AS u

GROUP BY ID , Name

方法三

SELECT ID ,

name ,

( SELECT MAX(UpdateDate) AS LastUpdateDate

FROM ( SELECT tt.UpdateByApp1Date AS UpdateDate

UNION

SELECT tt.UpdateByApp2Date

UNION

SELECT tt.UpdateByApp3Date

) ud

) LastUpdateDate

FROM ##TestTable tt