先来说一下我的需求:
有一张学生表,用于存放学生考试成绩信息,包括学生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等代替。