有时候我们需要从多个相同的列里(这些列的数据类型相同)找出最大的那个值,并显示
这里给出一个例子
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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
2
3
4
5
6
7
|
SELECT ID ,
Name ,
( SELECT MAX(LastUpdateDate)
FROM ( VALUES ( UpdateByApp1Date), ( UpdateByApp2Date),
( UpdateByApp3Date) ) AS UpdateDate ( LastUpdateDate )
) AS LastUpdateDate
FROM ##TestTable
|
方法二
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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
|
第一种方法使用values子句,将每行数据构造为只有一个字段的表,以后求最大值,非常巧妙
第二种方法使用行转列经常用的UNPIVOT 关键字进行转换再显示
第三种方法跟第一种方法差不多,但是使用union将三个UpdateByAppDate字段合并为只有一个字段的结果集然后求最大值
第一种方法的执行计划
第二种方法的执行计划
第三种方法的执行计划
总的来说,第一种方法的执行计划是最好的
注意,这里不涉及分组
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
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'
),
(
'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 ##TestTable
SELECT ID ,
Name ,
( SELECT MAX(LastUpdateDate)
FROM ( VALUES ( UpdateByApp1Date), ( UpdateByApp2Date),
( UpdateByApp3Date) ) AS UpdateDate ( LastUpdateDate )
) AS LastUpdateDate
FROM ##TestTable
|
name列相同的话,是无法得出name分组之后的最大值,这里要注意一下
http://www.2cto.com/database/201510/447166.html