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=93count(1)=1 < 2,符合要求~
  • a.grade=97count(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条记录的方法