当前位置：首页 > news >正文

Mysql---多表查询

news 2026/3/26 18:04:50

题目1：浙江大学用户题目回答情况

问题分解：

限定条件：来自浙江大学的用户，学校信息在user_profile表，答题情况在question_practice_detail表，因此需要通过device_id关联两个表的数据；

方法1：join两个表，用inner join，条件是on up.device_id=qpd.device_id and up.university='浙江大学'

    select q.device_id, q.question_id, q.resultfrom question_practice_detail as qjoin user_profile as uon u.device_id=q.device_id where u.university='浙江大学'order by question_id

结合代码进行分析

select 公共的列名需要标注来自哪个表（q.device_id）,(后面的question_id和q.result是q表所特有的，可以加表名也可以不加，加上更规范)
from question_practice_detail as q（as后面是question_practice_detail表的别名  为 q）
join user_profile as u  （user_profile的别名是u，默认为inner join）
on u.device_id=q.device_id   （通过 device_id（设备 ID）作为唯一标识，将u表和q表关联起来。也就是on后面加两个表关联的条件）
where u.university='浙江大学'    （在关联的基础上，过滤出用户表中 university 字段等于浙江大学的记录。）
order by question_id   （按照 question_id（题目 ID）进行升序（默认）排列。）

方法2：先从user_profile表找到浙江大学的所有学生id列表where university='浙江大学'，再去question_practice_detail表筛选出id在这个列表的记录，用where in

    select device_id, question_id, resultfrom question_practice_detailwhere device_id in (select device_id from user_profilewhere university='浙江大学')order by question_id

题目2：统计每个学校的答过题的用户的平均答题数

问题分解：

（如果某个列名是某个表独有的，不用写成表名.列名的形式，当然写了更规范）

根据输出示例确定select的内容：university，生成新的列，列名为avg_answer_cnt（as 表头重命名）
平均答题数量：在每个学校中，总的答题数量除以总人数就可以得到平均答题数量count(question_id) / count(distinct device_id)===》count（）是统计总数的函数，distinct是去重关键字
保留四位小数：ROUND(数值/表达式, 保留的小数位数)
表连接：学校信息和答题信息不在同一个表，需要作连接
表连接标识：device_id（两个表所共有的）
每个学校平均答题数量：按照学校分组 group by university
按照university升序排序：order by university(默认升序)

规范代码示例：

    select u.university,round(count(question_id)/count(distinct q.device_id),4) as avg_answer_cntfrom question_practice_detail as qjoin user_profile as uon q.device_id=u.device_idgroup by u.universityorder by u.university

注：

distinct是关键字不是函数 distinct 列名即可

题目3：统计每个学校各难度的用户平均刷题数（多对多）

问题分解：

连接表：将 user_profile 表与 question_practice_detail 表通过 device_id 进行连接，然后将question_practice_detail表与 question_detail 表通过 question_id 进行连接，以获取每个用户的答题难度信息。（question_practice_detail是中间表）
分组计算：group by university,difficult_level
计算平均答题数量：计算该分组内，答题总量除以答题人数，得到平均答题数量（使用round函数保留四位小数，使用as 进行表名重命名）

规范代码示例：

    select u.university,qd.difficult_level,round(count(qpd.question_id)/count(distinct qpd.device_id),4) as avg_answer_cntfrom user_profile as ujoin question_practice_detail as qpdon u.device_id=qpd.device_idjoin question_detail as qdon qpd.question_id=qd.question_idgroup by u.university,qd.difficult_levelorder by u.university,qd.difficult_level

注：
为什么不能用u.id关联qd.id?

user_profile 存储用户信息，question_detail 存储题目信息，两者没有直接关联，必须通过中间表 question_practice_detail（答题记录）来关联

题目4：统计每个用户的平均刷题数

问题分解：

连接表：我们需要将 user_profile 表与 question_practice_detail 表通过 device_id 进行连接，然后再与 question_detail 表通过 question_id 进行连接，以获取每个用户的答题难度信息。
过滤数据：只选择 university 为 "山东大学" 的用户。
分组计算：根据 difficult_level 进行分组，计算每个难度级别的平均答题量。
计算平均值：对于每个分组，计算该组内用户的答题总数，并除以用户数量，得到平均答题量。
格式化输出：将结果保留四位小数。

规范代码示例：

    select u.university,qd.difficult_level,round(count(qd.question_id)/count(distinct u.device_id),4) as avg_answer_cntfrom user_profile as ujoin question_practice_detail as qpdon u.device_id=qpd.device_idjoin question_detail as qdon qpd.question_id=qd.question_idwhere u.university='山东大学'group by u.university,qd.difficult_levelorder by u.university,qd.difficult_level

题目5：查找山东大学或者性别为男生的信息

问题分解：

限定条件：学校为山东大学或者性别为男性的用户：university='山东大学', gender='male'；
分别查看&结果不去重：所以直接使用两个条件的or是不行的，直接用union也不行，要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重
不去重：union all

规范代码示例：

    select device_id,gender,age,gpa from user_profile where university='山东大学'union allselect device_id,gender,age,gpa from user_profile where gender='male'

注：
UNION ALL 的作用是：把多个结构相同的 SELECT 语句的结果集 “直接拼接” 在一起，返回所有行（包括重复行）。

核心特点：不去重、不排序，执行效率高；
适用场景：需要合并多个查询结果，且确认无重复行（或允许重复行）时。

UNION ALL的基础语法：

-- 语法结构：多个SELECT语句用UNION ALL连接，字段数/类型必须一致SELECT 字段1, 字段2, ... FROM 表1 WHERE 条件UNION ALLSELECT 字段1, 字段2, ... FROM 表2 WHERE 条件UNION ALLSELECT 字段1, 字段2, ... FROM 表3 WHERE 条件;

关键规则（必须遵守）：