sql如何将不同表的查询结果合并 并排序_数据分析师——旅程篇（多表查询）...

实际生活中我们期望得到的数据往往分散在不同的表中，所以多表查询是很必要的，本篇文章包括四个部分：表的加法、表的联结、联结应用案例、case表达式。

一.表的加法

我们有两张表，score表和score1表：

表的加法是union:按行合并在一起

例如：

select 课程号,课程名称
from score
union
select 课程号,课程名称
from score1;

结果：

如果想要保留重复行：在union后面加上关键字all

select 课程号,课程名称
from score
union all
select 课程号,课程名称
from score1;

结果：

二.表的联结

1.交叉联结（cross join）

交叉联结也叫做笛卡尔积，是指将表中的每一行与另一张表中的每一行合并在一起，结果的行数是两张表中行数的乘积，如图所示：

2.内联结（inner join）

查找出同时存在于两张表中的数据，例如：

student表：

course表：

内联结两张表：

SELECT a.学号,a.姓名,b.课程号
FROM student a INNER JOIN course b
ON a.学号 = b.学号;

结果：

3.左联结（left join）

左联结会将左侧表的数据全部查找出来，例如：

SELECT a.学号,a.姓名,b.课程号
FROM student a LEFT JOIN course b
ON a.学号 = b.学号;

结果：

如果想要去掉公共部分的数据，只留下左边表的数据：

SELECT a.学号,a.姓名,b.课程号
FROM student a LEFT JOIN course b
ON a.学号 = b.学号
WHERE b.学号 = Null;

4.右联结（right join）

右联结会将右侧表的数据全部查找出来，例如：

SELECT a.学号,a.姓名,b.课程号
FROM student a RIGHT JOIN course b
ON a.学号 = b.学号;

结果：

如果想要去掉公共部分的数据，只留下右边表的数据：

SELECT a.学号,a.姓名,b.课程号 
FROM student a 
RIGHT JOIN course b 
ON a.学号 = b.学号 
WHERE a.学号 = Null;

5.全联结（full join）

它会返回左表和右表中的所有行，没有匹配的地方用空值来填充，但是mysql不支持全联结。

总之，可以用一张图来总结所有联结：

三.联结应用案例

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

1）翻译成大白话：

学号、姓名：student 表

选课数：course 表，按学号进行分组，对课程号计数count

总成绩：course 表，按学号进行分组，对成绩求和sum

2）分析思路

select 查询结果[学号,姓名,选课数,总成绩]
from 从哪张表中查找数据[学生表student,课程表course 两个表如何联结？按学号 用哪种联结？左联结]
where 查询条件[没有]
group by 分组
[每个学生的选课数目：按学号分组，对课程号计数count
 每个学生的总成绩：按学号分组，对成绩求和sum
] 
having 对分组结果指定条件[没有]
order by 对查询结果排序[没有]
limit 从查询结果中取出指定行[没有]

3）SQL语句

SELECT a.学号,a.姓名, COUNT(b.课程号) AS 选课数, SUM(b.成绩) AS 总成绩
FROM student a LEFT JOIN course b
ON a.学号 = b.学号
GROUP BY a.学号;

结果：

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

1）翻译成大白话：

①查询出所有学生的学号、姓名、平均成绩

学号，姓名（在学生表student）

平均成绩（每个学生的平均成绩：在课程表course, 按学号分组，平均成绩：avg(成绩)）

②查出平均成绩>85的学生

2）分析思路

select 查询结果[学号,姓名,选课数,总成绩] 
from 从哪张表中查找数据[学生表student,课程表course 两个表如何联结？按学号 用哪种联结？左联结]
where 查询条件[没有] 
group by 分组 [
每个学生的平均成绩：按学号分组，对成绩求平均avg ]  
having 对分组结果指定条件[平均成绩大于85] 
order by 对查询结果排序[没有] 
limit 从查询结果中取出指定行[没有]

3）sql语句

SELECT a.学号,a.姓名, AVG(b.成绩) AS 平均成绩
FROM student a LEFT JOIN course b
ON a.学号 = b.学号
GROUP BY a.学号
HAVING 平均成绩>85

结果：

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

1）翻译成大白话：

学号、姓名（学生表 student）

课程号、课程名称（成绩表 score）

但是中间需要课程表进行连接

2）分析思路

select 查询结果[学号、姓名、课程号、课程名称]  
from 从哪张表中查找数据[学生表student，课程表course，成绩表score 三个表如何联结？按学号联结学生表和课程表，用课程号联结课程表和成绩表 用哪种联结？内联结] 
where 查询条件[没有]  
group by 分组 [没有]
having 对分组结果指定条件[没有]  
order by 对查询结果排序[没有]  
limit 从查询结果中取出指定行[没有]

3）sql语句

SELECT a.学号,a.姓名,c.课程号,c.课程名称
FROM student a JOIN course b 
ON a.学号 = b.学号
JOIN score c
ON b.课程号 = c.课程号;

结果：

四.case表达式

case表达式的sql语句：

例如：

SELECT 学号,课程号,成绩,
(CASE WHEN 成绩>=60 THEN '及格'
    WHEN 成绩 <60 THEN '不及格'
    ELSE null
END) AS 是否及格
FROM course;

结果：

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

SELECT 课程号,
sum(case when 成绩>=60 then 1
   else 0
   end) AS 及格人数,
sum(case when 成绩<60 then 1
   else 0
   end) AS 不及格人数
FROM course
GROUP BY 课程号;

结果：

case表示式注意事项：

else<表达式>可以省略不写，那就默认为空值

end不能省略

问题2：使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

SELECT a.课程号,b.课程名称,
SUM(CASE WHEN 成绩 BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '[100-85]',
SUM(CASE WHEN 成绩>=70 AND 成绩<85 THEN 1 ELSE 0 END) AS '[85-70]',
SUM(CASE WHEN 成绩>=60 AND 成绩<70 THEN 1 ELSE 0 END) AS '[70-60]',
SUM(CASE WHEN 成绩<60 THEN 1 ELSE 0 END) AS '[<60]'
FROM course a RIGHT JOIN score b
ON a.课程号 = b.课程号
GROUP BY a.课程号,b.课程名称;    ---当用多个列来分组时，这几个列的值全部相同才算一组

结果：

---

sqlzoo题目: