标签：多表 -- course num student MySQL 解析 id SELECT

多表查询

【一】概要

（1）联表查询

inner join：内连接

-- inner join / join  # 拼接两张表中共有的数据部分
select * from 表2 inner join 表1 on 表2.字段 = 表1.字段;

left join：左外连接

-- left join  # 返回左表中的所有行，以及右表中与左表中相匹配的行。如果右表中没有匹配的行，则返回 NULL 值
select * from 表2 left join 表1 on 表2.字段 = 表1.字段;

right join：右外连接

-- right join  # 返回右表中的所有行，以及左表中与右表中相匹配的行。如果左表中没有匹配的行，则返回 NULL 值
select * from 表2 right join 表1 on 表2.字段 = 表1.字段;

union：全连接

-- union # union实际上不是连接查询，而是一种用于组合多个 SELECT 查询结果集的操作符,并将结果集 中的重复行去除
select * from 表2 
left join 表1 on 表2.字段 = 表1.字段
union
select * from 表2 
right join 表1 on 表2.字段 = 表1.字段;

（2）子查询

-- 将一个查询语句的结果当做另外一个查询语句的条件去用
select * from 表名 where 字段名 = (select 字段 from 表2 where 筛选条件);

【二】详细

【1】数据准备

-- 建部门表
create table dep(
		id int PRIMARY KEY AUTO_INCREMENT,
    name varchar(20)
);

-- 建员工表
CREATE TABLE emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    sex ENUM("male","female") NOT NULL DEFAULT "male",
    age INT,
    dep_id INT
);

-- 插入部门数据
insert into dep values
("200","技术部"),
("201","人力资源"),
("202","销售部"),
("203","运营部"),
("204","售后部");

-- 插入员工数据
insert into emp(name,sex,age,dep_id) values
("alex","male",18,200),
("alice","female",18,201),
("peter","male",38,202),
("tom","male",18,203),
("jerry","male",28,204),
("happy","male",18,205);

【2】子查询练习

将一个查询语句的结果当做另外一个查询语句的条件去用

【2.1】获取指定员工所在的部门名称

获取alice所在部门名称

【2.1.1】不使用子查询实现要求

/*
先对题目进行分析
首先在员工表中获取部门编号
然后在部门表中根据部门编号查询部门名称
*/

select name,dep_id from emp where name = 'alice'; -- 获取部门编号
select name from dep where id = %(上述查询到的部门标号)s; -- 获取部门名称
select name from dep where id = 201; -- 获取部门名称

缺点：依赖于前一条sql语句的执行结果，且不适合重复利用，需要根据前一条执行结果修改第二条sql语句

【2.1.2】使用子查询

-- 将查询部门编号的sql语句作为筛选条件中的参数
-- 通过小括号()声明子查询
select name from dep where id = (
	select dep_id from emp where name ='alice'
);

【2.2】获取指定部门下的所有员工

技术部和人力资源

【2.2.1】不适用子查询实现

/*
先根据部门名称获取部门编号
再根据部门编号去员工表查询员工
*/

select id,name from dep where name = '技术部' or name = '人力资源'; -- 查询部门编号
select name,dep_id from emp where dep_id = 200 or dep_id =201; -- 查询员工

【2.2.2】使用子查询实现

-- 有两个需要使用的，所以使用 in 
select name,dep_id from emp where dep_id in (
	select id from dep where name = '技术部' or name = '人力资源'
);

【3】联表查询

【3.1】笛卡尔积（Cartesian Product）

笛卡尔乘积_百度百科 (baidu.com)
笛卡尔积（Cartesian Product）是集合论中的一个概念，指的是两个集合的所有可能的组合。
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

【3.2】MySQL中的笛卡尔积

在数据库中，如果执行了没有指定任何连接条件的两个表的联接操作，将会产生笛卡尔积。
举例来说，假设有两个表 A 和 B，分别有 m 和 n 条记录，如果执行了没有指定连接条件的联接操作（如 SELECT * FROM A, B;），将会生成一个包含 m * n 条记录的结果集，其中每一条记录都是表 A 中的一行和表 B 中的一行的组合。
笛卡尔积通常会导致结果集过大，因此在实际的数据库查询中，应该尽量避免无连接条件的联接操作，以免产生不必要的数据冗余和性能问题。

select * from dep,emp;
-- 可以看到，共计30条数据，部门表5条记录 员工表6条记录
-- 每一个员工重复了5次部门表中的内容，造成数据冗余

【3.3】优化MySQL中的笛卡尔积

【3.3.1】指定连接条件

select * from 表1,表2 where 连接条件;
-- 连接条件通常由一个或多个列之间的等值关系组成，以确定两个表中哪些行应该被组合到一起。

select * from dep,emp where emp.dep_id = dep.id;
-- 通过【.】点语法指定哪张表中的哪一列数据

【3.3.2】使用合适的连接类型

MySQL基础——多表查询（各种join连接详解）_三个表join怎样关联-CSDN博客

`inner join`：内连接

-- inner join / join  # 拼接两张表中共有的数据部分
select * from 表2 inner join 表1 on 表2.字段 = 表1.字段;

select * from emp inner join dep on emp.dep_id = dep.id;
-- 需要注意，内连接只拼接共有部分，如【员工6】happy的部门编号为205，而部门表中并不存在205所以就不显示

`left join`：左外连接

-- left join  # 返回左表中的所有行，以及右表中与左表中相匹配的行。如果右表中没有匹配的行，则返回 NULL 值
select * from 表2 left join 表1 on 表2.字段 = 表1.字段;

select * from emp left join dep on emp.dep_id = dep.id;

`right join`：右外连接

-- right join  # 返回右表中的所有行，以及左表中与右表中相匹配的行。如果左表中没有匹配的行，则返回 NULL 值
select * from 表2 right join 表1 on 表2.字段 = 表1.字段;

select * from emp right join dep on emp.dep_id = dep.id;

`union`：全连接

-- union # union实际上不是连接查询，而是一种用于组合多个 SELECT 查询结果集的操作符,并将结果集 中的重复行去除
select * from 表2 
left join 表1 on 表2.字段 = 表1.字段
union
select * from 表2 
right join 表1 on 表2.字段 = 表1.字段;

select * from emp
left join dep on emp.dep_id = dep.id
union
select * from emp
right join dep on emp.dep_id = dep.id;

-- FULL OUTER JOIN
# 在大多数常见的关系型数据库管理系统中，FULL OUTER JOIN 是支持的，但是需要注意的是，MySQL 并不直接支持 FULL OUTER JOIN
SELECT * FROM dep FULL OUTER JOIN emp ON dep.id = emp.dep_id;

【3.3.2】限制结果集大小

select 列1,列2... from 表1,表2 [where 筛选条件] [order by 排序列] limit 限制条数;

select * from dep,emp limit 5;
-- 询问客户是否需要下一页数据 --
select * from dep,emp limit 6,5; -- 从第6行数据开始返回5行数据

如果无法避免笛卡尔积，可以通过限制结果集的大小或增加筛选条件来减少数据量，以提高查询性能。
限制结果集类似于分页，它可以帮助控制返回的数据量，并且在处理大量数据时可以提高查询效率。
当你需要获取大量数据时，将结果集分成多个较小的页面，每次只加载一页数据，可以有效地减少数据库的负载和网络传输的开销。此外，对于用户来说，分页功能也使得数据展示更加友好和可控。
通常，当用户需要查看下一页或上一页数据时，会根据当前页码和每页显示的行数计算出下一页或上一页的起始行数，然后再次执行查询以获取相应的数据。

【3.3.3】创建索引

详细可以查看【MySQL 进阶语法】中的索引部分

CREATE INDEX index_name ON table_name (column_name);

index_name 是要创建的索引的名称，table_name 是要在其上创建索引的表的名称，column_name 是要在其上创建索引的字段的名称。

select index index_dep_emp on em(dep_id);

创建索引后，数据库系统将会自动维护该索引，并在查询中使用它来加速数据检索操作，包括连接操作

【补】常见的连接条件

等值连接（Equi-Join）： 使用等值连接时，连接条件是两个表之间的某些列具有相同的值。例如：ON table1.column = table2.column。
非等值连接（Non-Equi-Join）： 非等值连接是指连接条件不是简单的相等关系，而是基于其他条件来确定两个表之间的关联。例如：ON table1.column > table2.column。
自连接（Self-Join）： 自连接是指在单个表内部进行连接操作，通常用于在同一表中比较不同行之间的数据。例如：SELECT * FROM table t1 JOIN table t2 ON t1.column = t2.column。
多表连接（Multiple Table Join）： 多表连接是指连接多个表的操作，可以通过多个连接条件将多个表关联起来。例如：SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column1 JOIN table3 ON table2.column2 = table3.column2。

【三】练习题

【难】练习1

【10.1】多表查询练习题 - Chimengmeng - 博客园 (cnblogs.com)

【1.1】数据准备

SQL文件
- 可以将下述内容复制到sql文件中

/*
Navicat Premium Data Transfer

Source Server         : localhost
Source Server Type    : MySQL
Source Server Version : 50624
Source Host           : localhost
Source Database       : sqlexam

Target Server Type    : MySQL
Target Server Version : 50624
File Encoding         : utf-8

Date: 10/21/2016 06:46:46 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(32) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;

-- ----------------------------
--  Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(32) NOT NULL,
  `teacher_id` int(11) NOT NULL,
  PRIMARY KEY (`cid`),
  KEY `fk_course_teacher` (`teacher_id`),
  CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;

-- ----------------------------
--  Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `num` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_score_student` (`student_id`),
  KEY `fk_score_course` (`course_id`),
  CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
  CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;

-- ----------------------------
--  Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `gender` char(1) NOT NULL,
  `class_id` int(11) NOT NULL,
  `sname` varchar(32) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;

-- ----------------------------
--  Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tid` int(11) NOT NULL AUTO_INCREMENT,
  `tname` varchar(32) NOT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

【1.2】习题内容

1、查询所有的课程的名称以及对应的任课老师姓名

2、查询学生表中男女生各有多少人

3、查询物理成绩等于100的学生的姓名

4、查询平均成绩大于八十分的同学的姓名和平均成绩

5、查询所有学生的学号，姓名，选课数，总成绩

6、 查询姓李老师的个数

7、 查询没有报李平老师课的学生姓名

8、 查询物理课程比生物课程高的学生的学号

9、 查询没有同时选修物理课程和体育课程的学生姓名

10、查询挂科超过两门(包括两门)的学生姓名和班级
、查询选修了所有课程的学生姓名

12、查询李平老师教的课程的所有成绩记录
 
13、查询全部学生都选修了的课程号和课程名

14、查询每门课程被选修的次数

15、查询之选修了一门课程的学生姓名和学号

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

17、查询平均成绩大于85的学生姓名和平均成绩

18、查询生物成绩不及格的学生姓名和对应生物分数

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

20、查询每门课程成绩最好的前两名学生姓名

21、查询不同课程但成绩相同的学号，课程号，成绩

22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

24、任课最多的老师中学生单科成绩最高的学生姓名

【1.3】题目解析

待补充~

【1.4】参考答案

#1、查询所有的课程的名称以及对应的任课老师姓名
SELECT
    course.cname,
    teacher.tname
FROM
    course
INNER JOIN teacher ON course.teacher_id = teacher.tid;




#2、查询学生表中男女生各有多少人
SELECT
    gender 性别,
    count(1) 人数
FROM
    student
GROUP BY
    gender;




#3、查询物理成绩等于100的学生的姓名
SELECT
    student.sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        INNER JOIN course ON score.course_id = course.cid
        WHERE
            course.cname = '物理'
        AND score.num = 100
    );




#4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
    student.sname,
    t1.avg_num
FROM
    student
INNER JOIN (
    SELECT
        student_id,
        avg(num) AS avg_num
    FROM
        score
    GROUP BY
        student_id
    HAVING
        avg(num) > 80
) AS t1 ON student.sid = t1.student_id;




#5、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)
SELECT
    student.sid,
    student.sname,
    t1.course_num,
    t1.total_num
FROM
    student
LEFT JOIN (
    SELECT
        student_id,
        COUNT(course_id) course_num,
        sum(num) total_num
    FROM
        score
    GROUP BY
        student_id
) AS t1 ON student.sid = t1.student_id;




#6、 查询姓李老师的个数
SELECT
    count(tid)
FROM
    teacher
WHERE
    tname LIKE '李%';




#7、 查询没有报李平老师课的学生姓名(找出报名李平老师课程的学生，然后取反就可以)
SELECT
    student.sname
FROM
    student
WHERE
    sid NOT IN (
        SELECT DISTINCT
            student_id
        FROM
            score
        WHERE
            course_id IN (
                SELECT
                    course.cid
                FROM
                    course
                INNER JOIN teacher ON course.teacher_id = teacher.tid
                WHERE
                    teacher.tname = '李平老师'
            )
    );




#8、 查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表，然后连表即可)
SELECT
    t1.student_id
FROM
    (
        SELECT
            student_id,
            num
        FROM
            score
        WHERE
            course_id = (
                SELECT
                    cid
                FROM
                    course
                WHERE
                    cname = '物理'
            )
    ) AS t1
INNER JOIN (
    SELECT
        student_id,
        num
    FROM
        score
    WHERE
        course_id = (
            SELECT
                cid
            FROM
                course
            WHERE
                cname = '生物'
        )
) AS t2 ON t1.student_id = t2.student_id
WHERE
    t1.num > t2.num;




#9、 查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的，思路是得到物理+体育课程的学生信息表，然后基于学生分组，统计count(课程)=1)
SELECT
    student.sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        WHERE
            course_id IN (
                SELECT
                    cid
                FROM
                    course
                WHERE
                    cname = '物理'
                OR cname = '体育'
            )
        GROUP BY
            student_id
        HAVING
            COUNT(course_id) = 1
    );




#10、查询挂科超过两门(包括两门)的学生姓名和班级(求出<60的表，然后对学生进行分组，统计课程数目>=2)
SELECT
    student.sname,
    class.caption
FROM
    student
INNER JOIN (
    SELECT
        student_id
    FROM
        score
    WHERE
        num < 60
    GROUP BY
        student_id
    HAVING
        count(course_id) >= 2
) AS t1
INNER JOIN class ON student.sid = t1.student_id
AND student.class_id = class.cid;




#11、查询选修了所有课程的学生姓名(先从course表统计课程的总数，然后基于score表按照student_id分组，统计课程数据等于课程总数即可)
SELECT
    student.sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        GROUP BY
            student_id
        HAVING
            COUNT(course_id) = (SELECT count(cid) FROM course)
    );




#12、查询李平老师教的课程的所有成绩记录
SELECT
    *
FROM
    score
WHERE
    course_id IN (
        SELECT
            cid
        FROM
            course
        INNER JOIN teacher ON course.teacher_id = teacher.tid
        WHERE
            teacher.tname = '李平老师'
    );




#13、查询全部学生都选修了的课程号和课程名(取所有学生数，然后基于score表的课程分组，找出count(student_id)等于学生数即可)
SELECT
    cid,
    cname
FROM
    course
WHERE
    cid IN (
        SELECT
            course_id
        FROM
            score
        GROUP BY
            course_id
        HAVING
            COUNT(student_id) = (
                SELECT
                    COUNT(sid)
                FROM
                    student
            )
    );




#14、查询每门课程被选修的次数
SELECT
    course_id,
    COUNT(student_id)
FROM
    score
GROUP BY
    course_id;




#15、查询之选修了一门课程的学生姓名和学号
SELECT
    sid,
    sname
FROM
    student
WHERE
    sid IN (
        SELECT
            student_id
        FROM
            score
        GROUP BY
            student_id
        HAVING
            COUNT(course_id) = 1
    );




#16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT DISTINCT
    num
FROM
    score
ORDER BY
    num DESC;




#17、查询平均成绩大于85的学生姓名和平均成绩
SELECT
    sname,
    t1.avg_num
FROM
    student
INNER JOIN (
    SELECT
        student_id,
        avg(num) avg_num
    FROM
        score
    GROUP BY
        student_id
    HAVING
        AVG(num) > 85
) t1 ON student.sid = t1.student_id;




#18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT
    sname 姓名,
    num 生物成绩
FROM
    score
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN student ON score.student_id = student.sid
WHERE
    course.cname = '生物'
AND score.num < 60;




#19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
SELECT
    sname
FROM
    student
WHERE
    sid = (
        SELECT
            student_id
        FROM
            score
        WHERE
            course_id IN (
                SELECT
                    course.cid
                FROM
                    course
                INNER JOIN teacher ON course.teacher_id = teacher.tid
                WHERE
                    teacher.tname = '李平老师'
            )
        GROUP BY
            student_id
        ORDER BY
            AVG(num) DESC
        LIMIT 1
    );




#20、查询每门课程成绩最好的前两名学生姓名
#查看每门课程按照分数排序的信息，为下列查找正确与否提供依据
SELECT
    *
FROM
    score
ORDER BY
    course_id,
    num DESC;




#表1：求出每门课程的课程course_id,与最高分数first_num
SELECT
    course_id,
    max(num) first_num
FROM
    score
GROUP BY
    course_id;




#表2：去掉最高分，再按照课程分组，取得的最高分，就是第二高的分数second_num
SELECT
    score.course_id,
    max(num) second_num
FROM
    score
INNER JOIN (
    SELECT
        course_id,
        max(num) first_num
    FROM
        score
    GROUP BY
        course_id
) AS t ON score.course_id = t.course_id
WHERE
    score.num < t.first_num
GROUP BY
    course_id;




#将表1和表2联合到一起，得到一张表t3，包含课程course_id与该们课程的first_num与second_num
SELECT
    t1.course_id,
    t1.first_num,
    t2.second_num
FROM
    (
        SELECT
            course_id,
            max(num) first_num
        FROM
            score
        GROUP BY
            course_id
    ) AS t1
INNER JOIN (
    SELECT
        score.course_id,
        max(num) second_num
    FROM
        score
    INNER JOIN (
        SELECT
            course_id,
            max(num) first_num
        FROM
            score
        GROUP BY
            course_id
    ) AS t ON score.course_id = t.course_id
    WHERE
        score.num < t.first_num
    GROUP BY
        course_id
) AS t2 ON t1.course_id = t2.course_id;




#查询前两名的学生（有可能出现并列第一或者并列第二的情况）
SELECT
    score.student_id,
    t3.course_id,
    t3.first_num,
    t3.second_num
FROM
    score
INNER JOIN (
    SELECT
        t1.course_id,
        t1.first_num,
        t2.second_num
    FROM
        (
            SELECT
                course_id,
                max(num) first_num
            FROM
                score
            GROUP BY
                course_id
        ) AS t1
    INNER JOIN (
        SELECT
            score.course_id,
            max(num) second_num
        FROM
            score
        INNER JOIN (
            SELECT
                course_id,
                max(num) first_num
            FROM
                score
            GROUP BY
                course_id
        ) AS t ON score.course_id = t.course_id
        WHERE
            score.num < t.first_num
        GROUP BY
            course_id
    ) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHERE
    score.num >= t3.second_num
AND score.num <= t3.first_num;




#排序后可以看的明显点
SELECT
    score.student_id,
    t3.course_id,
    t3.first_num,
    t3.second_num
FROM
    score
INNER JOIN (
    SELECT
        t1.course_id,
        t1.first_num,
        t2.second_num
    FROM
        (
            SELECT
                course_id,
                max(num) first_num
            FROM
                score
            GROUP BY
                course_id
        ) AS t1
    INNER JOIN (
        SELECT
            score.course_id,
            max(num) second_num
        FROM
            score
        INNER JOIN (
            SELECT
                course_id,
                max(num) first_num
            FROM
                score
            GROUP BY
                course_id
        ) AS t ON score.course_id = t.course_id
        WHERE
            score.num < t.first_num
        GROUP BY
            course_id
    ) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHERE
    score.num >= t3.second_num
AND score.num <= t3.first_num
ORDER BY
    course_id;




#可以用以下命令验证上述查询的正确性
SELECT
    *
FROM
    score
ORDER BY
    course_id,
    num DESC;




-- 21、查询不同课程但成绩相同的学号，课程号，成绩
-- 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
-- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
-- 24、任课最多的老师中学生单科成绩最高的学生姓名

标签：多表,--,course,num,student,MySQL,解析,id,SELECT
From： https://www.cnblogs.com/Lea4ning/p/18028132

MySQL 多表查询 - 缺练习解析

多表查询

【一】概要

（1）联表查询

`inner join`：内连接

`left join`：左外连接

`right join`：右外连接

`union`：全连接

（2）子查询

【二】详细

【1】数据准备

【2】子查询练习

【2.1】获取指定员工所在的部门名称

【2.1.1】不使用子查询实现要求

【2.1.2】使用子查询

【2.2】获取指定部门下的所有员工

【2.2.1】不适用子查询实现

【2.2.2】使用子查询实现

【3】联表查询

【3.1】笛卡尔积（Cartesian Product）

【3.2】MySQL中的笛卡尔积

【3.3】优化MySQL中的笛卡尔积

【3.3.1】指定连接条件

【3.3.2】使用合适的连接类型

`inner join`：内连接

`left join`：左外连接

`right join`：右外连接

`union`：全连接

【3.3.2】限制结果集大小

【3.3.3】创建索引

【补】常见的连接条件

【三】练习题

【难】练习1

【1.1】数据准备

【1.2】习题内容

【1.3】题目解析

【1.4】参考答案

相关文章

赞助商

阅读排行

MySQL 多表查询 - 缺练习解析

多表查询

【一】概要

（1）联表查询

inner join：内连接

left join：左外连接

right join：右外连接

union：全连接

（2）子查询

【二】详细

【1】数据准备

【2】子查询练习

【2.1】获取指定员工所在的部门名称

【2.1.1】不使用子查询实现要求

【2.1.2】使用子查询

【2.2】获取指定部门下的所有员工

【2.2.1】不适用子查询实现

【2.2.2】使用子查询实现

【3】联表查询

【3.1】笛卡尔积（Cartesian Product）

【3.2】MySQL中的笛卡尔积

【3.3】优化MySQL中的笛卡尔积

【3.3.1】指定连接条件

【3.3.2】使用合适的连接类型

inner join：内连接

left join：左外连接

right join：右外连接

union：全连接

【3.3.2】限制结果集大小

【3.3.3】创建索引

【补】常见的连接条件

【三】练习题

【难】练习1

【1.1】数据准备

【1.2】习题内容

【1.3】题目解析

【1.4】参考答案

相关文章

赞助商

阅读排行

`inner join`：内连接

`left join`：左外连接

`right join`：右外连接

`union`：全连接

`inner join`：内连接

`left join`：左外连接

`right join`：右外连接

`union`：全连接