你的位置:首页 > 数据库

[数据库]对数据按组排序


数据库环境:SQL SERVER 2005

  有数据如图1,字段id是序号,id相同的为一组,createtime为创建时间。要求:

将createtime为最新时间所在的组排在前面,同时,同一组内的数据按createtime降序排序。

最终结果如图2所示

图1    箭头    图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      

View Code

(本文完)