SQL语句面试题经典50题（一）：汇总、分组、分组条件查询【图文并茂，附答案】

怼烎@ 2024-02-05 21:00 28阅读 0赞

## 表分析 ##

已知有如下4张表：

学生表：student(学号,学生姓名,出生年月,性别)

成绩表：score(学号,课程号,成绩)

课程表：course(课程号,课程名称,教师号)

教师表：teacher(教师号,教师姓名)

*  1.学生表  
    Student(s\_id,s\_name,s\_birth,s\_sex) —学生编号,学生姓名, 出生年月,学生性别
 *  2.课程表  
    Course(c\_id,c\_name,t\_id) – —课程编号, 课程名称, 教师编号
 *  3.教师表  
    Teacher(t\_id,t\_name) —教师编号,教师姓名
 *  4.成绩表  
    Score(s\_id,c\_id,s\_score) —学生编号,课程编号,分数

根据以上信息按照下面要求写出对应的SQL语句。

ps：这些题考察SQL的编写能力，对于这类型的题目，需要你先把4张表之间的关联关系搞清楚了，最好的办法是自己在草稿纸上画出关联图，然后再编写对应的SQL语句就比较容易了。下图是我画的这4张表的关系图，可以看出它们之间是通过哪些外键关联起来的：

[![img][]][img 1]

## 创建数据库和表 ##

为了演示题目的运行过程，我们先按下面语句在客户端navicat中创建数据库和表。

![img][img 2]

![img][img 3]

-- 建表
    -- 学生表
    CREATE TABLE `Student`(
    	`s_id` VARCHAR(20),
    	`s_name` VARCHAR(20) NOT NULL DEFAULT '',
    	`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
    	`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
    	PRIMARY KEY(`s_id`)
    );
    -- 课程表
    CREATE TABLE `Course`(
    	`c_id`  VARCHAR(20),
    	`c_name` VARCHAR(20) NOT NULL DEFAULT '',
    	`t_id` VARCHAR(20) NOT NULL,
    	PRIMARY KEY(`c_id`)
    );
    -- 教师表
    CREATE TABLE `Teacher`(
    	`t_id` VARCHAR(20),
    	`t_name` VARCHAR(20) NOT NULL DEFAULT '',
    	PRIMARY KEY(`t_id`)
    );
    -- 成绩表
    CREATE TABLE `Score`(
    	`s_id` VARCHAR(20),
    	`c_id`  VARCHAR(20),
    	`s_score` INT(3),
    	PRIMARY KEY(`s_id`,`c_id`)
    );
    -- 插入学生表测试数据
    insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
    insert into Student values('02' , '钱电' , '1990-12-21' , '男');
    insert into Student values('03' , '孙风' , '1990-05-20' , '男');
    insert into Student values('04' , '李云' , '1990-08-06' , '男');
    insert into Student values('05' , '周梅' , '1991-12-01' , '女');
    insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
    insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
    insert into Student values('08' , '王菊' , '1990-01-20' , '女');
    -- 课程表测试数据
    insert into Course values('01' , '语文' , '02');
    insert into Course values('02' , '数学' , '01');
    insert into Course values('03' , '英语' , '03');
    
    -- 教师表测试数据
    insert into Teacher values('01' , '张三');
    insert into Teacher values('02' , '李四');
    insert into Teacher values('03' , '王五');
    
    -- 成绩表测试数据
    insert into Score values('01' , '01' , 80);
    insert into Score values('01' , '02' , 90);
    insert into Score values('01' , '03' , 99);
    insert into Score values('02' , '01' , 70);
    insert into Score values('02' , '02' , 60);
    insert into Score values('02' , '03' , 80);
    insert into Score values('03' , '01' , 80);
    insert into Score values('03' , '02' , 80);
    insert into Score values('03' , '03' , 80);
    insert into Score values('04' , '01' , 50);
    insert into Score values('04' , '02' , 30);
    insert into Score values('04' , '03' , 20);
    insert into Score values('05' , '01' , 76);
    insert into Score values('05' , '02' , 87);
    insert into Score values('06' , '01' , 31);
    insert into Score values('06' , '03' , 34);
    insert into Score values('07' , '02' , 89);
    insert into Score values('07' , '03' , 98);

### Student表： ###

[![image-20191212102315915][]][image-20191212102315915 1]

### Course表： ###

[![image-20191212102510760][]][image-20191212102510760 1]

### Teacher表： ###

[![Teacher][]][Teacher 1]

### Score表： ###

[![Score][]][Score 1]

## 简单查询 ##

![img][img 4]

### 查询带"猴"的学生名单 ###

[![img][img 5]][img_img 5]

[![image-20191212105344854][]][image-20191212105344854 1]

[![image-20191212105601321][]][image-20191212105601321 1]

[![image-20191212105632018][]][image-20191212105632018 1]

### 查询姓"孟"老师的个数 ###

SELECT COUNT(*) AS 姓“孟”老师的个数
    FROM teacher
    WHERE t_name LIKE '孟%'

![image-20191212105917024][]

## 汇总、分组、分组条件查询 ##

### 汇总 ###

![img][img 6]

#### 查询课程编号为0002的总成绩 ####

/*
    分析思路
    select 查询结果 [总成绩:汇总函数sum]
    from 从哪张表中查找数据[成绩表score]
    where 查询条件 [课程号是0002]
    */
    
    SELECT sum(s_score)
    FROM score
    WHERE c_id ='0002'

![image-20191212110022584][]

#### 查询选了课程的学生人数 ####

/*
    这个题目翻译成大白话就是：查询有多少人选了课程
    select 学号，成绩表里学号有重复值需要去掉
    from 从课程表查找score;
    */
    SELECT COUNT(DISTINCT c_id) AS 学生人数
    FROM score

![image-20191212110055542][]

### 分组 ###

![img][img 7]

#### 查询各科成绩最高和最低的分 ####

以如下的形式显示：课程号，最高分，最低分

/*
    分析思路
    select 查询结果 [课程ID：是课程号的别名,最高分：max(成绩) ,最低分：min(成绩)]
    from 从哪张表中查找数据 [成绩表score]
    where 查询条件 [没有]
    group by 分组 [各科成绩：也就是每门课程的成绩，需要按课程号分组];
    */
    /*
    上述题的拆分：课程id为0002的最高分与最低分
    
    SELECT MAX(s_score) AS 最高分, MIN(s_score) AS 最低分
    FROM score
    WHERE c_id = '0002'
    */
    
    SELECT
    	c_id,MAX(s_score) AS 最高分,MIN(s_score) AS 最低分
    FROM score
    GROUP BY c_id

![image-20191212110934135][]

#### 查询每门课程被选修的学生数 ####

通过对成绩表的课程id进行分组，然后对该学号进行计数

/*
    分析思路
    select 查询结果 [课程号，选修该课程的学生数：汇总函数count]
    from 从哪张表中查找数据 [成绩表score]
    where 查询条件 [没有]
    group by 分组 [每门课程：按课程号分组];
    */
    SELECT c_id,COUNT(c_id)
    FROM
    score
    GROUP BY c_id

![image-20191212111222219][]

#### 查询男生、女生人数 ####

在学生表中对性别进行分组 计数

/*
    分析思路
    select 查询结果 [性别，对应性别的人数：汇总函数count]
    from 从哪张表中查找数据 [性别在学生表中，所以查找的是学生表student]
    where 查询条件 [没有]
    group by 分组 [男生、女生人数：按性别分组]
    having 对分组结果指定条件 [没有]
    order by 对查询结果排序[没有];
    */
    
    
    SELECT s_sex,COUNT(s_sex) AS 个数
    FROM student
    GROUP BY s_sex

### 分组结果的条件 ###

![img][img 8]

#### 查询平均成绩大于60分学生的学号和平均成绩 ####

/* 
    题目翻译成大白话：
    平均成绩：展开来说就是计算每个学生的平均成绩
    这里涉及到“每个”就是要分组了
    平均成绩大于60分，就是对分组结果指定条件
    
    分析思路
    select 查询结果 [学号，平均成绩：汇总函数avg(成绩)]
    from 从哪张表中查找数据 [成绩在成绩表中，所以查找的是成绩表score]
    where 查询条件 [没有]
    group by 分组 [平均成绩：先按学号分组，再计算平均成绩]
    having 对分组结果指定条件 [平均成绩大于60分]
    */
    
    -- 在成绩表中对学号进行分组求平均成绩  having条件是平均成绩>60分
    select s_id,AVG(s_score)
    FROM score
    GROUP BY s_id
    HAVING AVG(s_score)>60
    ORDER BY AVG(s_score) DESC

![image-20191212111352401][]

#### 查询至少选修两门课程的学生学号 ####

/* 
    翻译成大白话：
    第1步，需要先计算出每个学生选修的课程数据，需要按学号分组
    第2步，至少选修两门课程：也就是每个学生选修课程数目>=2，对分组结果指定条件
    
    分析思路
    select 查询结果 [学号,每个学生选修课程数目：汇总函数count]
    from 从哪张表中查找数据 [课程的学生学号：课程表score]
    where 查询条件 [至少选修两门课程：需要先计算出每个学生选修了多少门课，需要用分组，所以这里没有where子句]
    group by 分组 [每个学生选修课程数目：按课程号分组，然后用汇总函数count计算出选修了多少门课]
    having 对分组结果指定条件 [至少选修两门课程：每个学生选修课程数目>=2]
    */
    
    SELECT s_id,COUNT(c_id) as 选修课程数目
    FROM score
    GROUP BY c_id
    HAVING COUNT(c_id)>=2

![image-20191212130502309][]

#### 查询同名同性学生名单并统计同名人数 ####

/* 
    翻译成大白话，问题解析：
    1）查找出姓名相同的学生有谁，每个姓名相同学生的人数
    查询结果：姓名,人数
    条件：怎么算姓名相同？按姓名分组后人数大于等于2，因为同名的人数大于等于2
    分析思路
    select 查询结果 [姓名,人数：汇总函数count(*)]
    from 从哪张表中查找数据 [学生表student]
    where 查询条件 [没有]
    group by 分组 [姓名相同：按姓名分组]
    having 对分组结果指定条件 [姓名相同：count(*)>=2]
    order by 对查询结果排序[没有];
    */
    
    SELECT s_name,COUNT(s_name)
    FROM student
    GROUP BY s_name
    HAVING COUNT(s_name)>=2

![image-20191212131157051][]

#### 查询不及格的课程并按课程号从大到小排列 ####

/* 
    分析思路
    select 查询结果 [课程号]
    from 从哪张表中查找数据 [成绩表score]
    where 查询条件 [不及格：成绩 <60]
    group by 分组 [没有]
    having 对分组结果指定条件 [没有]
    order by 对查询结果排序[课程号从大到小排列：降序desc];
    */
    
    SELECT c_id
    FROM score
    WHERE s_score<60
    GROUP BY c_id
    ORDER BY c_id desc

![image-20191212131322831][]

#### 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列 ####

/* 
    分析思路
    select 查询结果 [课程号,平均成绩：汇总函数avg(成绩)]
    from 从哪张表中查找数据 [成绩表score]
    where 查询条件 [没有]
    group by 分组 [每门课程：按课程号分组]
    having 对分组结果指定条件 [没有]
    order by 对查询结果排序[按平均成绩升序排序:asc，平均成绩相同时，按课程号降序排列:desc];
    */
    
    SELECT c_id,AVG(s_score) as 平均成绩
    FROM score
    GROUP BY c_id
    ORDER BY AVG(s_score) ASC,c_id DESC

![image-20191212131432805][]

#### 检索课程编号为0004且分数小于60的学生学号，结果按按分数降序排列 ####

/* 
    分析思路
    select 查询结果 []
    from 从哪张表中查找数据 [成绩表score]
    where 查询条件 [课程编号为“04”且分数小于60]
    group by 分组 [没有]
    having 对分组结果指定条件 []
    order by 对查询结果排序[查询结果按按分数降序排列];
    */
    select s_id,s_score
    FROM score
    WHERE c_id = "0004" AND s_score >=60
    ORDER BY s_score DESC

![image-20191212131500424][]

#### 统计每门课程的学生选修人数(超过2人的课程才统计) ####

要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

/* 
    分析思路
    select 查询结果 [要求输出课程号和选修人数]
    from 从哪张表中查找数据 []
    where 查询条件 []
    group by 分组 [每门课程：按课程号分组]
    having 对分组结果指定条件 [学生选修人数(超过2人的课程才统计)：每门课程学生人数>2]
    order by 对查询结果排序[查询结果按人数降序排序，若人数相同，按课程号升序排序];
    */
    
    SELECT c_id,COUNT(score.s_id) as '选修人数'
    FROM score
    GROUP BY c_id
    HAVING COUNT(score.s_id) > 2
    ORDER BY COUNT(score.s_id) DESC,c_id ASC

![image-20191212134936442][]

#### 查询两门以上不及格课程的同学的学号及其平均成绩 ####

/*
    分析思路
    先分解题目：
    1）[两门以上][不及格课程]限制条件
    2）[同学的学号及其平均成绩]，也就是每个学生的平均成绩，显示学号，平均成绩
    分析过程：
    第1步：得到每个学生的平均成绩，显示学号，平均成绩
    第2步：再加上限制条件：
    1）不及格课程
    2）两门以上[不及格课程]：课程数目>2
    
    
    /* 
    第1步：得到每个学生的平均成绩，显示学号，平均成绩
    select 查询结果 [学号,平均成绩：汇总函数avg(成绩)]
    from 从哪张表中查找数据 [涉及到成绩：成绩表score]
    where 查询条件 [没有]
    group by 分组 [每个学生的平均：按学号分组]
    having 对分组结果指定条件 [没有]
    order by 对查询结果排序[没有];
    */
    select 学号, avg(成绩) as 平均成绩
    from score
    group by 学号;
    
    
    /* 
    第2步：再加上限制条件：
    1）不及格课程
    2）两门以上[不及格课程]
    select 查询结果 [学号,平均成绩：汇总函数avg(成绩)]
    from 从哪张表中查找数据 [涉及到成绩：成绩表score]
    where 查询条件 [限制条件：不及格课程，平均成绩<60]
    group by 分组 [每个学生的平均：按学号分组]
    having 对分组结果指定条件 [限制条件：课程数目>2,汇总函数count(课程号)>2]
    order by 对查询结果排序[没有];
    */
    
    
    SELECT s_id,COUNT(s_score),avg(s_score) AS 平均成绩
    FROM score
    WHERE s_score<60
    GROUP BY s_id
    HAVING COUNT(s_score)>=2

![image-20191212140305267][]

--------------------

links：[https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98][https_enfangzhong.github.io_2019_12_03_SQL_E8_AF_AD_E5_8F_A5_E9_9D_A2_E8_AF_95_E7_BB_8F_E5_85_B850_E9_A2_98]

[img]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/81fc9a26c7284aeda287633b1946c98d.png
[img 1]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98/v2-86fd263583a6cead51675982c1735e68_hd.jpg
[img 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/65296f2b501342998cf6789e8cc8e9b5.png
[img 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/84434f5c69914b7ba3ff9f49ff5bddc8.png
[image-20191212102315915]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/7a1cbf45210045a6816f8acda1c27a0a.png
[image-20191212102315915 1]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98/image-20191212102315915.png
[image-20191212102510760]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/e4452b9f19464358947fafb925419f65.png
[image-20191212102510760 1]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98/image-20191212102510760.png
[Teacher]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/1dc0a3a3345e4f79b412316a0ed72e8f.png
[Teacher 1]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98/image-20191212102618628.png
[Score]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/ddf92b5c089a4568948ac2aa10eba029.png
[Score 1]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98/image-20191212102654572.png
[img 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/4628f0d131b0416681a0fa5ab1594680.png
[img 5]: https://img-blog.csdnimg.cn/img_convert/d7a50607a082d8e36fb749227751b151.jpeg
[img_img 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/7977938140c948e7af37ed743bcd9f06.png
[image-20191212105344854]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/a7e11761ed3540f89d40806b668cb090.png
[image-20191212105344854 1]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98/image-20191212105344854.png
[image-20191212105601321]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/b8ae4e57c098428f8d1c5001fba495fa.png
[image-20191212105601321 1]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98/image-20191212105601321.png
[image-20191212105632018]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/4133d102840147bc999788c3297b2bc4.png
[image-20191212105632018 1]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98/image-20191212105632018.png
[image-20191212105917024]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/3384f87420dd4aff81061c9053b183b3.png
[img 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/f7a6cd259d354fcfbade785e5f0e07db.png
[image-20191212110022584]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/b047f504916f447583384e95c7f6f84d.png
[image-20191212110055542]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/fe1b2ee90bb14c23a2247b703af575f2.png
[img 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/4158a4cb1b1040cc9c0aa7c0ae9308f3.png
[image-20191212110934135]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/29373564aa794fec99b195e0bbbf532c.png
[image-20191212111222219]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/72eab6d4d7444a669a0062c4c2a17a5b.png
[img 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/d0b297c0e7454a1fb91a01f0a8c919fe.png
[image-20191212111352401]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/e027442a1aba40a09564844f42215046.png
[image-20191212130502309]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/1e212e18549649c7952b9c34c6c8bdf4.png
[image-20191212131157051]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/84164be666f84238a291412777d26500.png
[image-20191212131322831]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/59e8249db20a42559c9f9692e506f264.png
[image-20191212131432805]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/1993fd720a7646d4a2a751e173c4aa61.png
[image-20191212131500424]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/0a98ceb2b1d94a6dba9283be8c5c3b55.png
[image-20191212134936442]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/99e34985d7dd4b258727f2f3b4cbb364.png
[image-20191212140305267]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/56f2d17d1f5447b5bf692d4a6d667ca5.png
[https_enfangzhong.github.io_2019_12_03_SQL_E8_AF_AD_E5_8F_A5_E9_9D_A2_E8_AF_95_E7_BB_8F_E5_85_B850_E9_A2_98]: https://enfangzhong.github.io/2019/12/03/SQL%E8%AF%AD%E5%8F%A5%E9%9D%A2%E8%AF%95%E7%BB%8F%E5%85%B850%E9%A2%98