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

bubuko 2022/1/25 20:04:10 mysql 字数 11780 阅读 808 0 来源 http://www.bubuko.com/infolist-5-1.html

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

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

SELECT
　　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
LEFT JOIN 
    student d 
ON
    c.s_id = d.s_id
WHERE 
    排名 BETWEEN 2 AND 3

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

SELECT
　　d.*, c.排名, c.s_score, c.c_id 
FROM
　　( 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
LEFT JOIN 
    student d 
ON
    c.s_id = d.s_id
WHERE 
    排名 BETWEEN 2 AND 3

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

SELECT
　　d.*, c.排名, c.s_score, c.c_id 
FROM
　　( 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
LEFT JOIN 
    student d 
ON
    c.s_id = d.s_id
WHERE 
    排名 BETWEEN 2 AND 3

4. 联合

SELECT
　　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=‘02‘ ORDER BY a.s_score DESC )c
LEFT JOIN 
    student d 
ON
    c.s_id = d.s_id
WHERE 
    排名 BETWEEN 2 AND 3
UNION
SELECT
　　d.*, c.排名, c.s_score, c.c_id 
FROM
　　( 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
LEFT JOIN 
    student d 
ON
    c.s_id = d.s_id
WHERE 
    排名 BETWEEN 2 AND 3
UNION
SELECT
　　d.*, c.排名, c.s_score, c.c_id 
FROM
　　( 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
LEFT JOIN 
    student d 
ON
    c.s_id = d.s_id
WHERE 
    排名 BETWEEN 2 AND 3

5. 查询结果

技术分享图片

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

原文：https://www.cnblogs.com/PersonalDiary/p/12531591.html

如果您也喜欢它,动动您的小指点个赞吧

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

分类汇总

您的鼓励是对我最大的支持