SQL分组取前N条记录

发表于 分类于

SQL分组取前N条记录

实际业务中有这样这样一个需求，，有6种不同的测试项目，每个项目对应很多小游戏(改善方案)，客户要求测试完成后，每周进行小游戏的循环推送，每种项目每次推送5个小游戏。

假如A项目对应的小游戏有8个，，那么第一周则推送第1-5个，第二周则推送6、7、8、1、2,以此方式进行循环。

单个项目还好说，但是6种项目怎么搞？！懵逼。。想不出来，，好吧，用存储过程搞定了。但是，在前期摸索的过程中，发现好多猿猿也有类似的需求，，只不过，我的需要循环。那么，，倘若不循环，值进行一次推送呢？6个项目一起，每种项目取前5项？可以学习下~

以网上都能找到的例子来说：[^1]

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
-- 查询每门课程的前2名成绩
CREATE TABLE StudentGrade(
    stuId CHAR(4),    --学号
    subId INT,        --课程号
    grade INT,        --成绩
    PRIMARY KEY (stuId,subId)
)

-- 表中数据如下
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',1,97);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',2,50);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('001',3,70);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',1,92);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',2,80);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('002',3,30);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',1,93);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',2,95);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('003',3,85);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',1,73);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',2,78);
INSERT INTO StudentGrade(stuId,subId,grade) VALUES('004',3,87);

-- 要查询每门课程的前2名成绩，即
001 1 97
003 1 93
003 2 95
002 2 80
004 3 87
003 3 85
-- 如何实现？

查看表中所有数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select * from StudentGrade ORDER BY subid asc,grade desc
|学号|科目|成绩|
001     1	97
003     1	93
002     1	92
004     1	73
003     2	95
002     2	80
004     2	78
001     2	50
004     3	87
003     3	85
001     3	70
002     3	30

两种解决方案：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 第一种
select
     * 
    from StudentGrade a 
    where (select count(1) from studentGrade b where b.subId=a.subId and b.grade>a.grade)<2 
    order by subId,grade desc

-- 第二种
SELECT 
    a.* 
    from StudentGrade a 
    left join StudentGrade b on a.subid=b.subid and a.grade<b.grade 
    GROUP BY a.stuid,a.subid,a.grade 
    having count(b.stuid)<2 
    order by a.subid,a.grade desc

对于第一种方案我是这么理解的，，因为b.subId=a.subId,a、b两表相连，以subId进行区分，类似分组。以科目一为例，，

  • 若a.grade=73，则b表中的科目一中>73分的共有3项，而条件要求count(1)<2,这样显然是不符合要求的，所以科目一的73分不行；
  • 同理92分。
  • 若a.grade=93，count(1)=1 < 2，符合要求~
  • 若a.grade=97，count(1)=0 < 2，符合要求~
    完美~

本来也是有点想不明白的，但是看到下面这句，，诶，有点意思，，噢~了然！

每取每一条记录，判断同一个班级，大于当前成绩的同学是不是小于2个人[^2]

对于第二种方案，,有一种奇技淫巧，就是把a、b两表的数据都查询出来

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT 
    a.*,b.* 
    from StudentGrade a 
    left join StudentGrade b on a.subid=b.subid and a.grade<b.grade 
    GROUP BY a.stuid,a.subid,a.grade 
    having count(b.stuid)<2 
    order by a.subid,a.grade desc
    
-- 结果如下
001     1	97	null null null	
003     1	93	001	 1	  97
003     2	95	null null null		
002     2	80	003	 2	  95
004     3	87	null null null		
003     3	85	004	 3	  87
-- 前3列为a表，后3列为b表

如此看来，清楚明了，，因为a.subid=b.subid and a.grade<b.grade，所以第2行a表的93分和b表的97分相连了，又因为count(b.stuid)<2，所以只取了a表每个科目的前2项。若我们只取a表的数据，后3列消失，，完美~

这个，，就这样吧，剩下的就是实操了，还是自己亲身command+R运行一下，体会更深~

[^1]: sql 用Group by分组后,取每组的前几条记录(转)

[^2]: mysql使用GROUP BY分组实现取前N条记录的方法