mysql多表联查 查询数据去重 mysql多表联合查询并求和

一. 课堂练习以及知识点

表的加法

含义：将两个表按行合并在一起，用union将两个表中的结果合并在一起

案例

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

用union all可以保留重复行

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

表的联结

联结（join）-表与表之间通过列产生对应关系，联结是将表与表之间关系合并在一起的操作

交叉联结

1. 表1的第一行和表2的两行合并在一起，形成两行数据，交叉结果是两个表中行数的乘积；
2. 交叉连接在实际业务中用的比较少，因为结果太多，花费大量的成本运算；

内联结

1. 含义：同时存在两张表的数据，即交集
2. 用inner join联结
3. 关键点

• from 子句中，同时使用两张表，需要给表取别名
• inner join 表示内连接
• on 表示哪个表中的哪个列匹配，表示匹配关系

案例

#查询学生的姓名，学号和课程号

SELECT s.姓名,s.学号,sc.课程号 
FROM student AS s 
INNER JOIN score AS sc 
ON s.`学号`=sc.`学号`;

左联结/右联结（以下内容以左联结为例）

用left join联结，left左边的表为主表，右边的为从表（right join右边的是主表，左边的是从表）

案例

SELECT s.姓名,s.学号,sc.`课程号` 
FROM student AS s 
LEFT  JOIN score AS sc 
ON s.`学号`=sc.`学号`;

SELECT s.姓名,s.学号,sc.`课程号` 
FROM student AS s 
LEFT  JOIN score AS sc 
ON s.`学号`=sc.`学号` 
WHERE sc.`课程号` IS NULL;

SELECT s.姓名,s.学号,sc.`课程号` 
FROM student AS s 
RIGHT JOIN score AS sc 
ON s.`学号`=sc.`学号`;

全联结

1. 全联结用full join 关键词联结；
2. 得到两个表中的所有行，若匹配不到则返回空值，合并在一起；

（mysql中不支持该联结，暂不详细说明）

总结

当实际业务中需要生成固定数据的表单或者需要指定数据，可以用左/右联结，其他情况用内联结，取两个表的公共部分。

应用案例

用sql解决问题的步骤：翻译题目，分析思路，sql语句

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

翻译题目：

• 学号，姓名在student表
• 选课数（在score表中，计算count（课程号)，对每个学号分组）
• 总成绩（在score表中，计算sum(成绩),对每个学号分组）

分析思路

• FROM学生表，成绩表，因为要查到所有学生的信息，因此应该让student做主表，通过学号用左连接
• 没有where查询条件
• group by：对学号分组，计算count（课程号)；对学号分组，计算sum(成绩)
• having没有分组结果的筛选条件
• order by没有排序
• limit没有指定行

sql语句

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

因为要查询所有学生信息，因此应该按主表即a表的学号分组

案例2 查询平均成绩大于85的所有学生的学号，姓名，平均成绩

翻译题目以及思路

• FROM学生表，成绩表，因为查询所有学生的信息，则学生表为主表，通过学号左连接
• 没有where条件
• group by:在成绩表中对学号分组，计算每个学生的平均成绩
• having: 对平均成绩比较，筛选出大于85分的
• order by:没有排序
• limit:没有指定行

sql语句

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;

案例3 查询所有学生的选课情况：学号，姓名，课程号，课程名称

翻译题目

• 在学生表中找学号，姓名
• 在课表中找课程号，课程名称

思路

from 学生表和课程表，但是两个表没有关系条件联结，所以需要通过成绩表进行联结，学生表和成绩表通过学号联结，课程表和成绩表通过课程号联结

sql语句

SELECT s.学号,s.姓名,c.课程号,c.课程名称 
FROM student AS s 
LEFT JOIN score AS sc 
ON s.`学号`=sc.`学号` 
LEFT JOIN course AS c 
ON c.`课程号`=sc.`课程号`;

case表达式

case when 判断表达式 then 表达式 
when 判断表达式 then 表达式 
... 
else 表达式 
end

案例1： 在查询结果中显示成绩是否及格

SELECT 学号,课程号,成绩,( 
CASE WHEN 成绩<=60 THEN '不及格' 
WHEN 成绩>60 AND 成绩<=80 THEN '中等' 
WHEN 成绩>80 THEN '优秀' ELSE '未知' END) AS 是否及格
FROM score;

案例2：查找出每门课程的及格人数和不及格人数

思路：先判断出每门课程及格或者不及格，及格为1，不及格为0，然后对课程号分组统计sum

sql语句：

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 课程号;

案例3：使用分段85-100, 70-85, 60-70，<60,来统计各科成绩，分别统计：各个分数段人数，课程号，课程名称

翻译题目

1. 根据分段，对成绩进行判断
2. 需要对不同分数段分组，计算每个分数段人数
3. 用课程表查出课程名称，课程号

思路

• FROM 因为需要成绩则用成绩表，需要课程名称则需要课程表，因为统计的是各个科目，则课程表做主表，通过课程号左连接，课程表放在左边，如果用右联结，course放在右边
• 没有where条件
• group by:对每个分数段进行分组，统计总人数
• having：没有
• order by:没有排序
• limit:没有指定行

sql语句

SELECT s.`课程号`,c.`课程名称`, SUM(
CASE WHEN 成绩<60 THEN 1 ELSE 0 END) AS '[<60]', 
SUM(CASE WHEN 成绩>=60 AND 成绩<70 THEN 1 ELSE 0 END) AS '[60-70]', 
SUM(CASE WHEN 成绩>=70 AND 成绩<85 THEN 1 ELSE 0 END) AS '[70-85]', 
SUM(CASE WHEN 成绩>=85 AND 成绩<=100 THEN 1 ELSE 0 END) AS '[85-100]' 
FROM course AS c 
LEFT JOIN score AS s ON s.`课程号`=c.`课程号` 
GROUP BY s.`课程号`,c.`课程名称`;

注意：select子句中的被分组的列应该是group by子句中的，因此都要放在group by后边；group by后边用多个列分组时，这几个列的值全部相同才算一组，比如‘01’-语文 是一组，‘01’-数学 是另一组，该案例中课程号和课程名称是一对一的关系，多列分组不影响结果。

注意事项

• else可以不写，默认为空值，但是为了方便理解，建议书写养成良好习惯
• 最后的end不能省略
• case表达式可以写在任何子句中

什么情况下使用case表达式：当有多种情况需要判断，则需要CASE表达式

二. SQLzoo练习

数据来源

https://sqlzoo.net/wiki/The_JOIN_operation/zhsqlzoo.net

The JOIN operation/zh

The JOIN operation/zhsqlzoo.net

#1列出 賽事編號matchid和球員名player,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查:teamid = 'GER'

SELECT matchid,player
FROM goal
WHERE teamid='GER';

#2由以上查詢，你可見Lars Bender's 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。留意在 goal 表格中的欄位 matchid ，是對應表格game的欄位id。我們可以在表格 game中找出賽事1012的資料。只顯示賽事1012的 id, stadium, team1, team2

SELECT id,stadium,team1,team2
FROM game
WHERE id='1012';

#3顯示每一個德國入球的球員名，隊伍名，場館和日期。

SELECT gl.player,gl.teamid,gm.stadium,gm.mdate
FROM game as gm
INNER JOIN goal  as gl
ON gm.id=gl.matchid
where gl.teamid='GER';

#4列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player

select gm.team1,gm.team2,gl.player
from game as gm
inner join goal as gl
on gm.id=gl.matchid
where gl.player like 'Mario%';

#5列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime

SELECT gl.player, gl.teamid, et.coach,gl.gtime
FROM goal as gl
inner join eteam as et
on gl.teamid=et.id
WHERE gtime<=10;

#6列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名。

思路：赛事日期和team1在在game表中，队伍名和教练在eteam中,game表中的team1 和eteam表中的id可以关联，用内连接。

select gm.mdate,et.teamname
from game as gm
inner join  eteam as et 
on gm.team1=et.id
where et.coach='Fernando Santos';

#7列出場館 'National Stadium, Warsaw'的入球球員。

思路：player在goal表中，场馆信息在game中，game表中的id和goal表的matchid可以关联，用内联结

select player
from game as gm
inner join goal as gl
on gm.id=gl.matchid
where gm.stadium= 'National Stadium, Warsaw';

#8找出德國-希臘Germany-Greece 的八強賽事的入球,只列出全部賽事，射入德國龍門的球員名字。

提醒：一个球员可能多次将球射入德国的球门，因此需要用select distinct 去除重复值；其他球队射入德国的门，首先德国需要参与比赛，所以要么team1='GER’要么team2='GER', 根据比赛规则，德国不可能将球射入自己的门中，则入球球队不能是德国，即goal.teamid<>'GER' （可能不知道足球规则做错）;题目中要求全部赛事，用left join

SELECT distinct gl.player
FROM game as gm
left join goal as gl
ON gl.matchid =gm.id 
WHERE gl.teamid<>'GER'
and
(team1='GER' or team2='GER');

#9列出隊伍名稱teamname和該隊入球總數

思路：球队名称在eteam表中，入球信息在goal表中，goal.teamid=eteam.id；入球总数可以将eteam.teamname分组统计各个teamname出现次数作为入球数量.

SELECT et.teamname,count(et.teamname)
FROM eteam as et
inner JOIN goal as gl
ON et.id=gl.teamid
group by et.teamname;

#10列出場館名和在該場館的入球數字。

select gm.stadium,count(gl.player)
from game as gm
inner join goal as gl
on gm.id=gl.matchid
group by gm.stadium;

#11每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。

思路：FROM---赛事信息和入球信息分别在game 和goal 表中，两个表可以通过goal.matchid=game.id内连接关联；

where---条件是由pol参与，则team1为pol或者team2为pol;

group---入球信息通过对每个赛事分组统计count（player），因此需要用到group by子句；

SELECT gl.matchid,gm.mdate,count(gl.player)
FROM game as gm
inner JOIN goal as gl
ON gm.id = gl.matchid 
WHERE gm.team1 = 'POL' OR gm.team2 = 'POL'
group by gm.id;

#12每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。

思路：上一题类似，但是条件多了一个德国入球数字，即德国参赛且入球的数字，那么加上goal.teamid为GER这条件即可.每场比赛可能是一场赛事进行多天，则分组时应该是group by gm.id,gm.mdate

select gl.matchid,gm.mdate,count(gl.player)
from game as gm
inner join goal as gl
on gm.id=gl.matchid
where (gm.team1 = 'GER' OR gm.team2 = 'GER') and gl.teamid='GER'
group by gm.id,gm.mdate;

#13查找出所有比赛的日期，每场比赛中对战双方各自的进球数（也就是team1进球数，team2进球数）,并且按照mdate, matchid, team1 and team2排序。

翻译题目

• 查找game中的mdate,team1,team2
• 统计team1和team2的进球数字 作为score1和score2显示，按比赛分组

分析思路

• from---game表和goal表，通过id 和matchi联结，因为是所有比赛，所以用left join
• where---没有
• group by---需要将比赛分组，统计进球数，用多条件判断sum(case when...)
• order by---按题意要求排序

select gm.mdate,gm.team1,
sum(case when gl.teamid=gm.team1 then 1 else 0 end) as score1,
gm.team2,sum(case when gl.teamid=gm.team2 then 1 else 0 end) as score2
from game as gm
left join goal as gl
on gm.id=gl.matchid
group by gm.id,gm.mdate,gm.team1,gm.team2
order by gm.mdate,gm.id,gm.team1,gm.team2;