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

1. 表的加法 （union）

快速建立相同结构表（以course为例）：

course ---》右键单击---》复制表---》结构和数据---》对新表重命名---》改数据

新建course1表

1）union不保留重复行

union all SQL语句示例

2）union all 保留重复行

union all SQL语句示例

2. 表的联接

1） 交叉联接（cross join）：实际业务中应用比较少，但是其他联接的基础

交叉联接

2） 内联接（inner join）：交集

取数图例：

内联接图例1

内联接图例2

SQL语句：

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

内联接语句查询

3） 左联接（left join）

取数图例：

左联接图例1

左联接图例2

SQL语句：

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

左联接语句查询

SQL语句：去掉交集部分

select a.学号, a.姓名, b.课程号
from student as a left join score as b
on a.学号 = b.学号
where b.学号 is Null;

左联接筛选语句去交集

4） 右联接（right join）

取数图例：

图例1

图例2

SQL语句：

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

右联接语句查询

SQL语句：去掉交集部分

select a.学号, a.姓名, b.课程号
from student as a right join score as b
on a.学号 = b.学号
where a.学号 is Null;

5） 全联接（full join）：MySQL不支持全连接

取数图例：

图例1

图例2

联接总结图

3. 联接应用案例

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

分析：
1）学号、姓名（student表）
2）选课数（每个学生的选课数、score表）
3）总成绩（每个学生的总成绩，score表，按学号分组，对成绩求和）

见图：

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.学号;

SQL查询结果

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

分析：
1） 查询出所有学生的学号、姓名、平均成绩、学号、姓名（student表）
平均成绩（score表），按学号分组求平均成绩
2） 平均成绩 > 85

SQL语句

select a.学号, a.姓名, avg(b.成绩) as 平均成绩
from student as a left join score as b
on a.学号 = b.学号
where avg(b.成绩) > 85
group by a.学号
having avg(b.成绩) > 85;

SQL查询结果

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

分析：
1） 学号、姓名（student表）
    课程号（score表）
2） 课程名称（course表）

SQL语句

select a.学号, a.姓名, b.课程号, c.课程名称
from student as a left inner join score as b
on a.学号 = b.学号
left inner join course as c
on b.课程号 = c.课程号;

SQL语句查询结果

4. case 表达式

case when (判断表达式) then (表达式)
     when (判断表达式) then (表达式)
     when (判断表达式) then (表达式)
     …
     else(表达式)
end

例1：

select 学号,课程号,成绩,
(case when 成绩 >= 60 then '及格'
      when 成绩 < 60 then '不及格'
      else
end) as 是否及格
from score;

SQL语句查询

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

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

注：sum()不应写成sum ()

步骤见图：

SQL语句查询成绩优秀或良好人数

注意事项：
1. else 子句可以默认不写，但是为了完整性，保留
2. end 不可省略不写
3. 该表达式可写在任何子句中
 

问题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 score as a right join course as b
on a.课程号 = b.课程号
group by a.课程号, b.课程名称; (这里加上课程名称对结果无影响)

SQL语句查询成绩段

SQLZOO练习

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

select matchid, player
from goal 
where teamid = 'GER';

2. 在表格 game中找出賽事1012的資料。只顯示賽事1012的 id, stadium, team1, team2

select game.id, game.stadium, game.team1, game.team2
from game left join goal
on game.id = goal.matchid
where game.id = '1012' and goal.matchid = '1012'
and goal.player = 'Lars Bender';

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

select goal.player, goal.teamid, game.stadium, game.mdate
from game inner join goal  (之前用left join，但此处为两表共有的部分)
on game.id = goal.matchid
where goal.teamid = 'Gre';

以上结果与答案不符，可能数据有改变。

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

select g1.team1, g1.team2, g2.player
from game as g1 right join goal as g2
on g1.id = g2.matchid
where g2.player like 'Mario%';

名字的模糊查询。

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

select g.player,g.teamid,e.coach,g.gtime
from goal as g left join eteam as e
on g.teamid = e.id
where g.gtime <= 10;

指定on的联接条件不能忘写，答案显示为错误，但是其给出的答案只是三次行改变了相对位置。

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

select g.mdate, e.teamname
from game as g left join eteam as e
on e.id = g.team1
where e.coach = 'Fernando Santos';

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

select g2.player
from game as g1 left join goal as g2
on g1.id = g2.matchid
where g1.stadium = 'National Stadium, Warsaw';

8. 以下例子找出德國-希臘Germany-Greece 的八強賽事的入球

修改它，只列出全部賽事，射入德國龍門的球員名字。

select player
from game as g1 inner join goal as g2
on g1.id = g2.matchid
where (g1.team1 = 'Ger' or g1.team2 = 'Ger')
and g2.player not in (select player
                      from goal
                      where teamid = 'Ger');

和给出的答案有出入，原因在于选取的名字应该用distinct

select distinct b.player
from game as a inner join goal as b on a.id = b.matchid
where (b.teamid = a.team1 and a.team2 = 'GER') or (b.teamid = a.team2 and a.team1 = 'GER');

以上语句可去重。

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

select e.teamname, count(g.teamid)
from goal as g inner join eteam as e
on g.teamid = e.id
group by e.teamname;

如果不用group by 分组，那么球队和进球数均为总数。

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

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

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

select g2.matchid, g1.mdate, count(g2.matchid)
from game as g1 inner join goal as g2
on g1.id = g2.matchid
where g1.team1 = 'POL' or g1.team2 = 'POL'
group by g2.matchid;

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

select g2.matchid, g1.mdate, count(g2.teamid = 'GER')
from game as g1 inner join goal as g2
on g1.id = g2.matchid
where (g1.team1 = 'GER' or g1.team2 = 'GER')
and g2.teamid = 'GER'
group by g2.matchid;

13. 每场比赛主客队进球数。

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

此处自己问题在于，group by和order by 后之前没有添加g1.id,g1.team1,g1.team2；添加多个分组可避免同一天有多个比赛时，不知会只有一场比赛。