多表查询过滤重复数据_数据分析——sql多表查询

表的加法

union：两个表的并集。两个表达 重复项会自动删除，只保留一个。

select 课程号,课程名称
from course
union
select 课程号,课程名称
from course_1

union all：两个表重复的地方并不会被删除

select 课程号,课程名称
from course
union all
select 课程号,课程名称
from course_1

表的联结

表和表之间是通过列产生关系的。联结是通过表和表之间的关系将两个表合并在一起的操作。

常用的联结有：

• 交叉联结cross join

交叉联结结果的行数是两张表行数的乘积。

生活中的例子：扑克牌

• 内联结inner join

查找出同时存在于两张表中的数据

select a.学号, a.姓名, b.课程号
from student as a inner join score as b
on a.学号 = b.学号

最后一步代表两个表是通过学号联结起来的。

左联结

以左边的表为主表。左表中的数据全部读取出来，右边的表只取出与左边相同的学号的行。

-- 左联结
select a.学号, a.姓名, b.课程号
from student as a left join score as b
on a.学号 = b.学号

左联结-去除和右边的公共部分

-- 左联结去除和右边的共同部分
select a.学号, a.姓名, b.课程号
from student as a left join score as b
on a.学号 = b.学号
where b.学号 = null;

右联结

和左联结逻辑相同

全联结

全联结会返回左表和右表所有行，当某一行和另一表有对应的时候会自动填充；没有对应的时候会填充null.

mysql不支持全联结。

当实际工作业务中说明了想要生成固定行数的表单，或者哪一张表里的全部数据时，我们会使用左联结或右联结；其他时候都用内联结。

联结应用案例

问题：查询所有学生的学号、姓名、选课数、总成绩

• 所有学生的学号、姓名（student表）
• 选课数、总成绩（score 表）

-选课数 ：count（对课程号计数）group by 学号

-总成绩：sum(成绩）group by 学号

/*查询所有学生的学号、姓名、选课数、总成绩*/
select a.学号, a.姓名, count(b.课程号) as 选课数,sum(b.成绩) as 总成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号;

问题：查询平均成绩大于85的所有学生的学号、姓名和平均成绩

• 得到所有学生的 姓名、学号、平均成绩
• 姓名学号（student表）
• 计算所有学生的平均成绩（score表）group by 学号【avg(成绩)】
• having 平均成绩>85

-- 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select a.学号, a.姓名, avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
group by a.学号
having avg(b.成绩)>85;

问题：查询学生的选课情况：学号，姓名，课程号，课程名称

• 查询学号、姓名（student表）
• 课程号、课程名称（course表）

-- 查询学生的选课情况：学号，姓名，课程号，课程名称
select a.学号, a.姓名, c.课程号, c.课程名称
from student as a inner join score as b
on a.学号 = b.学号
inner join course as c 
on b.课程号 = c.课程号;

case表达式

类似于条件判断表达式

问题：查询出 每门课程 的 及格人数 和 不及格人数

-- 查询出每门课程的及格人数和不及格人数
select 课程号,
sum(case when 成绩 >= 60 then 1 else 0
end) as 及格人数,
sum(case when 成绩 < 60 then 1 else 0
end) as 不及格人数
from score
group by 课程号;

使用分段【100-85】，【85-70】，【70-60】，【<60】来统计各科成绩，分别统计：

各分段人数（成绩表score），课程号和课程名称（课程表course）

/*使用分段【100-85】，【85-70】，【70-60】，【<60】来统计各科成绩，分别统计：
各分段人数（成绩表score），课程号和课程名称（课程表course）*/
select b.课程号, a.课程名称,
sum(case when 成绩<60 then 1 else 0
end) as '[<60]',
sum(case when 成绩>=60 and 成绩<70 then 1 else 0
end) as '[70-60]',
sum(case when 成绩>=70 and 成绩<85 then 1 else 0
end) as '[85-70]',
sum(case when 成绩>=85 and 成绩<100 then 1 else 0
end) as '[100-85]'
from course as a left join score as b
on a.课程号 = b.课程号
group by a.课程号;