Mysql 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

1. 查询课程ID为‘01’ 的课程的成绩第2名到第3名的学生信息及该课程成绩

1SELECT　　d.*, c.排名, c.s_score, c.c_id FROM　　( SELECT a.s_id, a.s_score, a.c_id, @i:=@i+1 AS 排名 FROM score a,(SELECT @i:=0)s WHERE a.c_id='01' ORDER BY a.s_score DESC )c
2LEFT JOIN 
3    student d 
4ON
5    c.s_id = d.s_id
6WHERE 
7    排名 BETWEEN 2 AND 3

2. 查询课程ID为‘02’ 的课程的成绩第2名到第3名的学生信息及该课程成绩

1SELECT
2　　d.*, c.排名, c.s_score, c.c_id 
3FROM
4　　( SELECT a.s_id, a.s_score, a.c_id, @j:=@j+1 AS 排名 FROM score a,(SELECT @j:=0)s WHERE a.c_id='02' ORDER BY a.s_score DESC )c
5LEFT JOIN 
6    student d 
7ON
8    c.s_id = d.s_id
9WHERE 
10    排名 BETWEEN 2 AND 3

3. 查询课程ID为‘03’ 的课程的成绩第2名到第3名的学生信息及该课程成绩

1SELECT
2　　d.*, c.排名, c.s_score, c.c_id 
3FROM
4　　( SELECT a.s_id, a.s_score, a.c_id, @k:=@k+1 AS 排名 FROM score a,(SELECT @k:=0)s WHERE a.c_id='02' ORDER BY a.s_score DESC )c
5LEFT JOIN 
6    student d 
7ON
8    c.s_id = d.s_id
9WHERE 
10    排名 BETWEEN 2 AND 3

4. 联合

1SELECT
2　　d.*, c.排名, c.s_score, c.c_id 
3FROM
4　　( SELECT a.s_id, a.s_score, a.c_id, @i:=@i+1 AS 排名 FROM score a,(SELECT @i:=0)s WHERE a.c_id='02' ORDER BY a.s_score DESC )c
5LEFT JOIN 
6    student d 
7ON
8    c.s_id = d.s_id
9WHERE 
10    排名 BETWEEN 2 AND 3
11UNION
12SELECT
13　　d.*, c.排名, c.s_score, c.c_id 
14FROM
15　　( SELECT a.s_id, a.s_score, a.c_id, @j:=@j+1 AS 排名 FROM score a,(SELECT @j:=0)s WHERE a.c_id='02' ORDER BY a.s_score DESC )c
16LEFT JOIN 
17    student d 
18ON
19    c.s_id = d.s_id
20WHERE 
21    排名 BETWEEN 2 AND 3
22UNION
23SELECT
24　　d.*, c.排名, c.s_score, c.c_id 
25FROM
26　　( SELECT a.s_id, a.s_score, a.c_id, @k:=@k+1 AS 排名 FROM score a,(SELECT @k:=0)s WHERE a.c_id='02' ORDER BY a.s_score DESC )c
27LEFT JOIN 
28    student d 
29ON
30    c.s_id = d.s_id
31WHERE 
32    排名 BETWEEN 2 AND 3

5. 查询结果