SQL查询成绩表中的每科第三名

版权声明:本文为博主原创文章,转载注明出处。 https://blog.csdn.net/g1418377085/article/details/77504076

SQL查询成绩表中的每科第三名

 题目要求

这里写图片描述

 表格数据

  • grade表
c_id c_no c_name
1 1 80
1 2 90
1 3 70
1 4 95
2 1 64
2 2 88
2 3 89
2 4 79
3 1 90
3 2 80
3 3 96
3 4 85
4 1 88
4 2 90
4 3 50
4 4 60

  • course表
c_id c_no c_name
1 001 C/C++
2 002 JAVA
3 003 Linux
4 004 数据库

  1. student表
s_id s_no s_name
1 001 gb
2 002 lly
3 003 pdl
4 004 lb

解题过程

  • 第一步 先根据成绩表得出每科每名学生的排名

查询代码

SELECT
    t1.*, (
        SELECT
            count(*)
        FROM
            grade t2
        WHERE
            t1.score <= t2.score
        AND t1.c_id = t2.c_id
    ) AS rownum
FROM
    grade t1

查询结果

c_id s_id score rownum
1 1 80 3
1 2 90 2
1 3 70 4
1 4 95 1
2 1 64 4
2 2 88 2
2 3 89 1
2 4 79 3
3 1 90 2
3 2 80 4
3 3 96 1
3 4 85 3
4 1 88 2
4 2 90 1
4 3 50 4

  • 第二步 将第一步所的表命名为t3 然后将表中rownum为3的查询出来 并按学科排序

查询代码

SELECT
    *
FROM
    (
        SELECT
            t1.*, (
                SELECT
                    count(*)
                FROM
                    grade t2
                WHERE
                    t1.score <= t2.score
                AND t1.c_id = t2.c_id
            ) AS rownum
        FROM
            grade t1
    ) t3
WHERE
    rownum = 3
ORDER BY
    c_id

查询结果

c_id s_id score rownum
1 1 80 3
2 4 79 3
3 4 85 3
4 4 60 3

  • 第三步 对最终查询结果与 student cource 两表进行左连接

查询代码

SELECT
    student.s_no,
    student.s_name,
    course.c_name,
    grade_temp.score
FROM
    (
        SELECT
            *
        FROM
            (
                SELECT
                    t1.*, (
                        SELECT
                            count(*)
                        FROM
                            grade t2
                        WHERE
                            t1.score <= t2.score
                        AND t1.c_id = t2.c_id
                    ) AS rownum
                FROM
                    grade t1
            ) t3
        WHERE
            rownum = 3
        ORDER BY
            c_id,
            score DESC
    ) grade_temp
LEFT JOIN course ON grade_temp.c_id = course.c_id
LEFT JOIN student ON grade_temp.s_id = student.s_id

查询结果

s_id s_name c_name score
001 gb C/C++ 80
004 lb JAVA 79
004 lb Linux 85
004 lb 数据库 60

附上表结构和表数据sql


SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_id` int(11) NOT NULL,
  `c_no` varchar(20) NOT NULL,
  `c_name` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '001', 'C/C++');
INSERT INTO `course` VALUES ('2', '002', 'JAVA');
INSERT INTO `course` VALUES ('3', '003', 'Linux');
INSERT INTO `course` VALUES ('4', '004', '数据库');

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
  `c_id` int(11) NOT NULL,
  `s_id` int(11) NOT NULL,
  `score` float NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES ('1', '1', '80');
INSERT INTO `grade` VALUES ('1', '2', '90');
INSERT INTO `grade` VALUES ('1', '3', '70');
INSERT INTO `grade` VALUES ('1', '4', '95');
INSERT INTO `grade` VALUES ('2', '1', '64');
INSERT INTO `grade` VALUES ('2', '2', '88');
INSERT INTO `grade` VALUES ('2', '3', '89');
INSERT INTO `grade` VALUES ('2', '4', '79');
INSERT INTO `grade` VALUES ('3', '1', '90');
INSERT INTO `grade` VALUES ('3', '2', '80');
INSERT INTO `grade` VALUES ('3', '3', '96');
INSERT INTO `grade` VALUES ('3', '4', '85');
INSERT INTO `grade` VALUES ('4', '1', '88');
INSERT INTO `grade` VALUES ('4', '2', '90');
INSERT INTO `grade` VALUES ('4', '3', '50');
INSERT INTO `grade` VALUES ('4', '4', '60');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `s_id` int(11) NOT NULL,
  `s_no` varchar(20) NOT NULL,
  `s_name` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '001', 'gb');
INSERT INTO `student` VALUES ('2', '002', 'lly');
INSERT INTO `student` VALUES ('3', '003', 'pdl');
INSERT INTO `student` VALUES ('4', '004', 'lb');
展开阅读全文

没有更多推荐了,返回首页