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

来源:转载

有时候我们需要从多个相同的列里(这些列的数据类型相同)找出最大的那个值,并显示 
这里给出一个例子

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

 



 
有三种方法可以实现
 
方法一
 
SELECT ID , Name , ( SELECT MAX(LastUpdateDate) FROM ( VALUES ( UpdateByApp1Date), ( UpdateByApp2Date), ( UpdateByApp3Date) ) AS UpdateDate ( LastUpdateDate ) ) AS LastUpdateDateFROM ##TestTable

 


方法二
 
SELECT ID , [Name] , MAX(UpdateDate) AS LastUpdateDateFROM ##TestTable UNPIVOT ( UpdateDate FOR DateVal IN ( UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date ) ) AS uGROUP 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 ) LastUpdateDateFROM ##TestTable tt

 


 
 
第一种方法使用values子句,将每行数据构造为只有一个字段的表,以后求最大值,非常巧妙
 
第二种方法使用行转列经常用的UNPIVOT 关键字进行转换再显示
 
第三种方法跟第一种方法差不多,但是使用union将三个UpdateByAppDate字段合并为只有一个字段的结果集然后求最大值
 
第一种方法的执行计划

第二种方法的执行计划

第三种方法的执行计划

总的来说,第一种方法的执行计划是最好的
 
注意,这里不涉及分组
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' ), ( 'ABC', '2015-07-05', '2015-06-04', '2015-09-06' ), ( 'NewCopmany', '2014-07-05', '2012-12-09', '2015-08-14' ), ( 'MyCompany', '2015-03-05', '2015-01-14', '2015-07-26' ) SELECT *FROM ##TestTableSELECT ID , Name , ( SELECT MAX(LastUpdateDate) FROM ( VALUES ( UpdateByApp1Date), ( UpdateByApp2Date), ( UpdateByApp3Date) ) AS UpdateDate ( LastUpdateDate ) ) AS LastUpdateDateFROM ##TestTable

 


 
name列相同的话,是无法得出name分组之后的最大值,这里要注意一下




分享给朋友:
您可能感兴趣的文章:
随机阅读: