你的位置:首页 > 软件开发 > 数据库 > 对数据按组排序

对数据按组排序

发布时间:2015-09-03 14:00:07
数据库环境:SQL SERVER 2005  有数据如图1,字段id是序号,id相同的为一组,createtime为创建时间。要求:将createtime为最新时间所在的组排在前面,同时,同一组内的数据按createtime降序排序。最终结果如图2所示 ...

数据库环境:SQL SERVER 2005

  有数据如图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      

原标题:对数据按组排序

关键词:排序

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。