A-A+
mysql group by分组获取每组的前N条记录
本文章向大家介绍mysql group by分组如何获取每组的前N(N为1,2,3...)条数据。文章给出了多种方法,这些方法各有各的优缺点,大家可以根据SQL性能来选择适合自己的方法。
有一张学生表,用于存放学生考试成绩信息,包括学生ID,学生姓名,学生班级ID,学生总成绩。这张表的数据如下表所示:
student_id | student_name | class_id | total_score |
1 | Jason | 1 | 298 |
2 | yayuan | 1 | 295 |
3 | Martin | 3 | 300 |
4 | Alison | 4 | 289 |
5 | Mathews | 2 | 250 |
6 | Celia | 2 | 240 |
7 | Rice | 1 | 275 |
8 | David | 3 | 257 |
9 | Larry | 2 | 243 |
10 | zhang | 3 | 250 |
11 | wu | 4 | 255 |
12 | ge | 1 | 260 |
13 | li | 3 | 265 |
14 | meng | 4 | 279 |
15 | qiu | 1 | 272 |
16 | liu | 4 | 283 |
17 | tian | 3 | 299 |
18 | huang | 2 | 201 |
19 | nie | 1 | 228 |
20 | wang | 3 | 230 |
我们要获取每一个班级总分在前两名的学生信息,SQL语句该如何写呢?这里有三个方法:
第一种方法:
SELECT a.student_id, a.student_name, a.class_id, a.total_score
FROM student a
LEFT JOIN student b
ON a.class_id = b.class_id
AND a.total_score < b.total_score
GROUP BY a.student_id, a.student_name, a.class_id, a.total_score
HAVING COUNT( b.student_id ) <2
ORDER BY a.class_id, a.total_score DESC
第二种方法:
SELECT *
FROM student a
WHERE 2 > (
SELECT COUNT( * )
FROM student
WHERE class_id = a.class_id
AND total_score > a.total_score )
ORDER BY a.class_id, a.total_score DESC
第三种方法
SELECT *
FROM student a
WHERE student_id
IN (
SELECT student_id
FROM student
WHERE class_id = a.class_id
ORDER BY total_score DESC
LIMIT 2
)
ORDER BY a.class_id, a.total_score DESC
对于第三种方法,好像有语法错误,在mysql里面,子查询里面不能使用limit,但在其他数据库里面可以用top等代替。
汪洋大海,文章转载:http://www.manongjc.com/article/1081.html
select f_id, max(id) as id from table group by f_id order by id desc
布施恩德可便相知重
微信扫一扫打赏
支付宝扫一扫打赏