|
有数据如图1，字段id是序号，id相同的为一组，createtime为创建时间。要求：
?
将createtime为最新时间所在的组排在前面，同时，同一组内的数据按createtime降序排序。
?
最终结果如图2所示
实现思路：
?
1.用分析函数取组内最大值
?
2.按照组内最新时间和创建时间降序排序
?
SQL实现：
/*测试数据*/
WITH x0
AS ( SELECT 1 AS id ,
100 AS data ,
CONVERT(DATETIME, '2015-09-01 12:23:56') AS createtime
UNION ALL
SELECT 1 AS id ,
24 AS data ,
CONVERT(DATETIME, '2015-09-02 18:25:26') AS createtime
UNION ALL
SELECT 2 AS id ,
68 AS data ,
CONVERT(DATETIME, '2015-09-01 08:46:12') AS createtime
UNION ALL
SELECT 2 AS id ,
360 AS data ,
CONVERT(DATETIME, '2015-09-01 00:52:58') AS createtime
UNION ALL
SELECT 2 AS id ,
200 AS data ,
CONVERT(DATETIME, '2015-09-03 10:08:36') AS createtime
UNION ALL
SELECT 3 AS id ,
70 AS data ,
CONVERT(DATETIME, '2015-09-02 13:48:12') AS createtime
UNION ALL
SELECT 4 AS id ,
108 AS data ,
CONVERT(DATETIME, '2015-08-31 03:11:23')
),/*分析函数求组内最新时间*/
x1
AS ( SELECT id ,
data ,
createtime ,
MAX(createtime) OVER ( PARTITION BY id ) AS createtime_max
FROM x0
)/*对结果集排序*/
SELECT id ,
data ,
createtime
FROM x1
ORDER BY createtime_max DESC ,
createtime DESC
?
|
