Group by 前几项的值

在MySQL中如何获取Group By后前N项记录

 

背景

最近，在做公司产品数据统计时，遇到一个有趣的问题——获取各个产品线更新时间最新的一条记录。每个产品每更新一次，都会根据更新内容生成一条新的记录。为了解决这个问题，我想到了使用group by 进行产品类型分组。然后在查询时，筛选出更新时间时间戳最大的数据。SQL语句如下：

SELECT type, 
       max(update_time), 
       * 
FROM   tbl_user_works 
GROUP  BY type 

使用这条SQL确实帮我筛选到了各产品最新的更新时间，但是无法筛选出最新更新时间对应的记录。那么，如何筛选到各产品更新时间最新的记录呢？

问题描述

我们将问题分成了简单版和升级版：

• 简单版： 用Group by 分组后，获取每组最大项
• 升级版：用Group by 分组后，获取每组前N项

解决方法

为了描述方便，我们模拟了一张学生成绩表。表结构与数据如下：

CREATE TABLE `tbl_student_score` (
  `stu_id` char(4) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '学生编号',
  `sub_id` int(11) NOT NULL DEFAULT '0' COMMENT '课程ID',
  `score` int(11) DEFAULT NULL COMMENT '得分',
  PRIMARY KEY (`stu_id`,`sub_id`)
) COMMENT='学生得分表';

INSERT INTO `tbl_student_score` (`stu_id`, `sub_id`, `score`)
VALUES
    (X'303034', 3, 87),
    (X'303031', 3, 70),
    (X'303032', 3, 30),
    (X'303033', 3, 85),
    (X'303031', 2, 50),
    (X'303034', 2, 78),
    (X'303032', 2, 80),
    (X'303033', 2, 95),
    (X'303034', 1, 63),
    (X'303033', 1, 93),
    (X'303032', 1, 92),
    (X'303031', 1, 99);

需求描述：获取各科目成绩排前2名的学生成绩信息。

方法一：自联表

SELECT * 
FROM   tbl_student_score a 
WHERE  (SELECT count(1) 
        FROM   tbl_student_score b 
        WHERE  b.sub_id = a.sub_id 
               AND b.score > a.score) < 2 
ORDER  BY sub_id, 
          score DESC 

我们通过自联表的方式，将成绩表A与成绩表B进行关联，筛选出每组得分最高的2项。

SELECT count(1) 
        FROM   tbl_student_score b 
        WHERE  b.sub_id = a.sub_id 
               AND b.score > a.score

这条子查询返回 A表课程=B表课程，且 B表得分大于A表得分影响行数。
若行数为 0，则表明该记录得分最高，若行数为1，则表明该记录的得分为第二高得分。

最后，对筛选出的记录，按照课程ID升序、得分降序进行排序，得到结果。

 

自联表结果

缺点：

• 时间复杂度为 O(n^2)，查询效率比较低

方法二： UNION ALL

(select * from tbl_student_score where sub_id=1 order by score desc limit 2 )

union all

(select * from tbl_student_score where sub_id=2 order by score desc limit 2 )

union all

(select * from tbl_student_score where sub_id=3 order by score desc limit 2 )

通过穷举所有课程类型，分别对每个课程类型按照得分降序的方式，取前2条。然后使用 union all 合并所有结果。

注意：此处，使用 union all 合并结果，而不是 union 合并结果。是因为 union 会对重复数据进行合并，而 union all 则不会。

缺点：

• 查询语句较为繁琐，需要遍历所有类型。当类型较多、或容易发生变化时，此方法无效。

方法三：行号

SET @num := 0, @type := ''; 

SELECT stu_id, 
       sub_id, 
       score 
FROM   (SELECT *, 
               @num := IF(@type = sub_id, @num + 1, 1) AS row_number, 
               @type := sub_id                         AS temp_type 
        FROM   tbl_student_score 
        ORDER  BY sub_id, 
                  score DESC) AS a 
WHERE  a.row_number <= 2 

原理：这里使用了MySQL中的自定义变量，我们先后声明了序号变量@num 和 当前课程类型变量 @type。SQL重点在子查询部分，我们使用 order by 对表记录按照 sub_id（课程ID） 升序，score （得分）降序排列。

@num := IF(@type = sub_id, @num + 1, 1) AS row_number, 
@type := sub_id                         AS temp_type 

对排好序的数据，进行查询。若当前类型相同，则序号加1，否则，置为 1。每遍历一行数据，需要将当前数据的课程类型赋值给变量 @type。这样，我们只需对表遍历一次，就可以完成排序。时间复杂度为O(n)。

然后，我们再筛选出 row_number <= 2的数据。即可，获取每组排序前2名的数据了。第二次遍历的时间复杂度仍为 O(n)。

注意：使用此方法时，请记得初始化自定义变量 —— SET @num := 0, @type := '';。

总结

三种方法中，第三种方法扩展性和查询效率上最好。第一种方法查询效率较低，对于查询效率要求不高的场景可以使用此方法。第二种方法，在实际应用中不是很常见。实用性较差，如果出现新的类型变更需要调整SQL。