A-A+

mysql group by分组获取每组的前N条记录

2017年12月31日 20:08 汪洋大海 暂无评论 阅读 256 views 次

本文章向大家介绍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

标签:

给我留言