查询结果取交集_多表查询

1.表的加法
2.表的联结
3.联结应用案例
4.case表达式

select 查询结果 -- 第五步

from 查询表名 -- 第一步

where 查询指定条件 -- 第二步

group by 分组 -- 第三步

having 对分组结果指定条件 -- 第四步

order by 查询结果排序 -- 第六步

limit 从结果中取出指定行数; -- 第七步

注意：from后面不能直接使用别名

准备：新建一张表

CREATE TABLE course1(课程号 VARCHAR(255), 课程名称 varchar(255) NOT NULL, 教师号 VARCHAR(255) NOT null, PRIMARY KEY(课程号));

INSERT INTO course1(课程号,课程名称,教师号) VALUES('0001','语文','0002');

INSERT INTO course1(课程号,课程名称,教师号) VALUES('0004','计算机','0004');

INSERT INTO course1(课程号,课程名称,教师号) VALUES('0005','数据库','0005');

1.表的加法

union表示求合集（去重），union all表示求合集（不去重）

select 课程号,课程名称,教师号

from course

union

select 课程号,课程名称,教师号

from course1

select 课程号,课程名称,教师号

from course

union all

select 课程号,课程名称,教师号

from course1

2.表的联结

交叉联结（笛卡尔积）cross join，得到的行数是两个表行数乘积。

实际应用比较少。

内联结inner join，得到的是两个表的交集。(注意，join和inner join的效果是一样的)

select a.学号,a.姓名,b.课程号

from student as a inner join score as b

on a.学号=b.学号;

左联结left join，左侧表中的数据全部取出，在右侧表查不到的补全为Null

SELECT a.学号,a.姓名,b.课程号,b.成绩

from student as a left join score as b

on a.学号=b.学号;

SELECT a.学号,a.姓名,b.课程号,b.成绩

from student as a left join score as b

on a.学号=b.学号

where b.学号 is Null;

默认情况下推荐使用is null 作条件判断，因为sql默认情况下对where xx =null的判断永远返回0行，不提示语法错误。

右联结right join

select b.学号,a.姓名,b.课程号

from student as a

right join score as b

on a.学号=b.学号;

利用学号配对student中的姓名等信息，学号不在student表中则补全null。

select b.学号,a.姓名,b.课程号

from student as a

right join score as b

on a.学号=b.学号

where a.学号 is null

order by 姓名;

使用is null，取学号在score表中，但不在student表的数据。

全连接full join，返回左表和右表中的所有行，有匹配的行则进行合并，没有的补全null。

MySQL不支持全连接。

3.联结应用案例

准备两张表student和score。注意两张表中学号没有包含关系，但是有交集。

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

思路：先将student和score左联结，然后分组计数。

select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩

from student as a left join score as b

on a.学号 = b.学号

group by a.学号;

实际上最好用全连接，因为student表可能比score多若干学号、同时少若干学号。

但是MySQL不支持全连接。可以先查询score表中学号不在student表的信息，然后union all。

如下是学号在score中，但不在student中的数据。（如下有两种方法）

select 学号,(Null) as 姓名, count(课程号) as 选课数, sum(成绩) as 总成绩

from score

where 学号<>all(select 学号 from student)

GROUP BY 学号;

select b.学号,a.姓名,count(b.课程号) as 选课数, SUM(b.成绩) as 总成绩

from student as a RIGHT JOIN score as b

ON a.学号=b.学号

where a.学号 is null

GROUP BY b.学号;

因此最终结果如下：

select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩

from student as a left join score as b

on a.学号 = b.学号

group by a.学号

union all

select 学号,(Null) as 姓名, count(课程号) as 选课数, sum(成绩) as 总成绩

from score

where 学号<>all(select 学号 from student)

GROUP BY 学号;

实际上述计算出来的总成绩是错误的。原因待后续分析。最后结果如下：

-- 内联结+左联结+右联结

select a.学号,a.姓名,count(b.课程号) as 选课数,sum(b.成绩) as 总成绩

from student as a left join score as b

on a.学号 = b.学号

where b.学号 is null

group by a.学号

UNION ALL

select b.学号,a.姓名,count(b.课程号) as 选课数, SUM(b.成绩) as 总成绩

from student as a RIGHT JOIN score as b

ON a.学号=b.学号

where a.学号 is null

GROUP BY b.学号

UNION ALL

select a.学号,a.姓名,count(b.课程号) as 选课数, sum(b.成绩) as 总成绩

from student as a INNER JOIN score as b

on a.学号=b.学号

GROUP BY a.学号;

案例：查询个人平均成绩大于85的所有学生的学号、姓名和平均成绩。

思路：查询所有学生的学号、姓名和平均成绩，然后使用分组条件(平均成绩>85)

select a.学号,a.姓名,avg(b.成绩)as 平均成绩

from student as a right join score as b

on a.学号=b.学号

GROUP BY b.学号

HAVING avg(b.成绩)>85;

案例：查询学生的选课情况：学号、姓名、课程号、课程名称

select a.学号,a.姓名,b.课程号,c.课程名称

from student as a left join score as b

on a.学号=b.学号

left join course as c

on b.课程号=c.课程号

order by 学号 asc,课程号 asc;

4.case表达式（对每一行记录进行判断）

case when 判断表达式 then 表达式

when 判断表达式 then 表达式

……

else 表达式

end

注意：else可以省略，默认为null，建议不省略。end不能省略。case表达式可以写在任意子句。

示例：

select 学号,课程号,成绩,

(case when 成绩>=80 then '及格'

when 成绩<80 then '不及格'

else null

end) as 是否及格

from score;

案例：算出每门课程的及格人数和不及格人数。

思路，首先要按照课程号分组，分组后及格人数（及格设置为1，不及格设置为0），不及格人数（及格设置为0，不及格设置为1），最后对每一组求和。

select 课程号,

sum(

case when 成绩>=80 then 1

else 0

end)as 及格人数,

sum(

case when 成绩<80 then 1

else 0

end) as 不及格人数

from score

group by 课程号;

案例：求每门课程的最高分、学号、课程号

解法一：

SELECT a.学号,a.课程号,a.成绩

from score as a,

(select 课程号,min(成绩) as 最低成绩 from score group by 课程号) as b

WHERE a.课程号=b.课程号 and a.成绩=b.最低成绩;

解法二：

select * from score a

where 成绩=(select min(成绩) from score as b where a.课程号=b;课程号);

案例：查询各科成绩前两名/后两名的记录。（top N的问题）

思路：这个问题没有理解，暂且记下来，后续再进行研究。

前两名：

select * from score as a

where (select count(*) from score as b where a.课程号=b.课程号 and a.成绩<b.成绩)<2

order by a.课程号,a.成绩 desc;

后两名：

select * from score as a

where (select count(*) from score as b where a.课程号=b.课程号 and a.成绩>b.成绩)<2

order by a.课程号,a.成绩 desc;

另外还有一种比较粗暴的方法：

先查出课程号（即有哪些分组），然后对每个分组的成绩排序，使用limit取topN，最后使用union all进行合并。

select 课程号 from score group by 课程号;

select 学号,课程号,成绩 from score

where 课程号='0001'

order by 成绩 desc

limit 2

因此，可以将三门课程的top 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 成绩 between 70 and 85 then 1

else 0

end

) as '[85-70]',

sum(

case when 成绩 between 60 and 70 then 1

else 0

end

) as '[70-60]',

sum(

case when 成绩<60 then 1

else 0

end

) as '[<60]'

from score as a right JOIN course as b -- 因为要查找课程表的全部数据，因此用右联结（实际上个人感觉也可以用左联结）

on a.课程号=b.课程号

group by b.课程号