先排序再分组"与分组取前N条实则意思相同
创建一个表格 table
这里是一张成绩表,包含:姓名、班级、分数
id | name | class | score |
---|---|---|---|
1 | a | c1 | 67 |
2 | b | c1 | 55 |
3 | c | c1 | 67 |
4 | d | c1 | 65 |
5 | e | c2 | 80 |
6 | f | c2 | 65 |
7 | g | c2 | 77 |
8 | h | c2 | 73 |
9 | i | c3 | 73 |
10 | j | c3 | 68 |
11 | k | c3 | 75 |
12 | l | c3 | 81 |
13 | m | c4 | 88 |
14 | n | c4 | 90 |
15 | o | c4 | 74 |
16 | p | c4 | 77 |
需要展示结果:找出每个班级的前两名,并由高到低排序
id | name | class | score |
---|---|---|---|
1 | a | c1 | 67 |
2 | b | c1 | 67 |
3 | c | c2 | 80 |
4 | d | c2 | 77 |
5 | e | c3 | 81 |
6 | f | c3 | 75 |
7 | g | c4 | 90 |
8 | h | c4 | 88 |
方法一:
SELECT a.id,a.name,a.clsss,a.score FROM table a
LEFT JOIN table b ON a.class=b.class AND a.score<b.score
group by a.id,a.name,a.class,a.score
having count(b.id)<2
ORDER BY a.class,a.score desc
拆开分析:
LEFT JOIN table b ON a.class=b.class AND a.score<b.score
同一个班级(每个班级四个人),分数比当前学生高的记录,那就意味这成绩垫底的学生,将会产生三条记录
group by a.id,a.name,a.class,a.score having count(b.id)<2
a.id,a.name,a.class,a.score可以代表一个学生(以学生分组),如果count(b.id)<2(成绩超过你的人不能多于2个),那就只剩第一第二了。
方法二:
SELECT * FROM table a WHERE
(SELECT COUNT(*) FROM table WHERE class=a.class and score>a.score) <2
ORDER BY a.class,a.score DESC;
取每一条记录,判断同一个班级,大于当前成绩的同学是不是小于2个人。
个人感觉,第二条比较好