SQLZOO练习--JOIN: 关于JOIN的操作

简介

sqlzoo是一个适合用于练习sql的题库网站
不要觉得页面丑而且无简体中文
如果做得完大部分题目比你听网课更有成效
补充:我是菜比

本练习的习题地址

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

FootballERD.png

练习

1. 列出球员'Bender'的所在的球队的所有进球数据

嗯?bender?

220px-Bender_Rodriguez.png

// 因为简书显示的问题所以注释使用//,请使用井字 

// 使用子查询
SELECT matchid,player FROM goal   WHERE teamid = 
(select teamid from goal where player like '%bender%')

// 因为bender是德国队的,也可以直接搜德国ger
SELECT matchid,player FROM goal   WHERE teamid = 'GER'

2. 列出比赛1012的 id, stadium, team1, team2

SELECT id,stadium,team1,team2
  FROM game where id = '1012'

3. 列出2012中所有德国进球的球员名player,队伍teamid,场馆stadium,日期mdate

通过1和2的例子来引入join的概念
from 主表 join 从表 on (他们的对应关系字段)

SELECT player,teamid,stadium,mdate
  FROM game JOIN goal ON (id=matchid) 
where teamid = 'GER'

附:我打了三遍'gre'都是错误答案,有点想不通后发现gre是希腊

4. 列出Mario (player LIKE 'Mario%')进球的比赛的队伍team1, team2 和球员player

巩固练习,建议手打一遍过脑

select team1,team2,player
from game join goal on (id=matchid)
where player like 'mario%'

5. 列出比赛前10分钟(gtime<=10)进球的球员,和他的队伍teamid,教练coach, 进球时间gtime

将goal表与eteam表联合,依然是巩固练习

SELECT player, teamid,coach,gtime
  FROM goal join eteam on (teamid = id)
 WHERE gtime<=10

6.列出教练coach'Fernando Santos'作为队伍1 team1 赛事日期和队伍名teamname

如果2张表有相同的字段(例如此处的id),则需要明确指出是谁与谁建立关系连接

select mdate,teamname from game join eteam on (eteam.id=team1)
where coach = 'Fernando Santos'

7.列出场馆 'National Stadium, Warsaw'的所有进球球员player

巩固练习

select player from game join goal on(id=matchid)
where stadium = 'National Stadium, Warsaw'

8. 列出所有射入德国球门的球员(不重复)

2个要点

1. 结果不要重复,因此使用关键词distinct
2. 假定德国人不射德国门,因此teamid需要排除ger

SELECT distinct player
  FROM game JOIN goal ON matchid = id 
    WHERE 
(team1 = 'ger'
or
team2 = 'ger')
and teamid != 'ger'

顺便一提繁体中文翻译的是什么垃圾

以下例子找出德國-希臘Germany-Greece 的八強賽事的入球
修改它，只列出全部賽事，射入德國龍門的球員名字。

例として、ドイツ―ギリシャ戦の全試合を表示するクエリーが入力してある。
代わりに、ドイツと対戦して、ゴールした選手の名前を全て表示する。

9. 列出所有球队名和其进球数

使用count和group by即可

SELECT teamname,count(teamid)
  FROM eteam JOIN goal ON id=teamid
group by teamname
 ORDER BY teamname

10. 列出所有场馆的进球数

很明显需要使用聚合+group by
联合2张表后随便count某个字段即可,官方给出的答案是count(1),有点骚

select stadium,count(1)
from game join goal on (id=matchid)
group by stadium

11. 列出所有波兰队的比赛,包括比赛编号matchid, 日期date 和进球数

依然是聚合+group by
第一次我按照teamid来分类(group by)
结果发现有2场相同的比赛,因此需要按照matchid来分类

SELECT matchid,mdate,count(1)
  FROM game JOIN goal ON matchid = id 
 WHERE (team1 = 'POL' OR team2 = 'POL')
group by matchid

12. 列出所有德国队ger的比赛,包括比赛编号matchid, 日期date 和德国队进球数

和刚才的进球数不同,这次只限德国队,因此多加一个where teamid = 'ger'

SELECT matchid,mdate,count(matchid)
  FROM game JOIN goal ON matchid = id 
 WHERE (team1 = 'GER' OR team2 = 'GER')
and teamid = 'ger'
group by matchid

13. 使用CASE WHEN 来表示mdate,team1,score1,team2,score2

结果的样式表格如下:

mdate           team1   score1  team2   score2
--------------------------------------------
1 July 2012     ESP     4       ITA     0
10 June 2012    ESP     1       ITA     1
10 June 2012    IRL     1       CRO     3
...

有点难,因为我从来没用case when
因此根据大象放进冰箱的法则,把他拆成一个个小题目

根据提示:

You could SUM this column to get a count of the goals scored by team1

1. 如何通过case when获取到每场比赛双方的score

select
sum(
  case
  when teamid = team1  THEN 1  else 0
end) score1
FROM game join goal on matchid = id
where matchid = '1002'    //此处where用于缩小范围.因为1001是1:1,选择出现多个结果的4:1

结果:

score1
------
4

2. 那么再加个team2的score呢

select
sum(
  case
  when teamid = team1  THEN 1  else 0
end) score1,
sum(
  case
  when teamid = team2  THEN 1  else 0
end) score2
FROM game join goal on matchid = id
where matchid = '1002'    

结果:

score1  score2
--------------
4       1

非常完美,符合题目的要求,因此将其余的条件也塞进去

select
mdate,
team1,
sum(
  case
  when teamid = team1  THEN 1  else 0
end) score1,
team2,
sum(
  case
  when teamid = team2  THEN 1  else 0
end) score2
FROM game join goal on matchid = id
group by matchid
order by mdate,matchid,team1,team2

这时候基本可以得出题目需要的结果
但是依然没有出现correct answer
查询一番后发现有几场比赛是0:0
因此会比正确答案少那么几条数据
那么很自然而然的想到是用左外连接来获取null结果

select ...
FROM game →LEFT← join goal on matchid = id
...

但依然不对!!
因为题目会默认给予写法提示matchid = id
仔细一想主表是game,对应的主键是id,而从表goal的主键才是matchid
那从表来和主表建立连接不少结果才怪!
因此完整的正确答案:

select 
mdate,
team1,
sum(
  case
  when teamid = team1  then 1
  else 0
end) score1,
team2,
sum(
  case
  WHEN teamid = team2  then 1
  else 0
end) score2
from game left join goal on id = matchid 
group by id    //这里的分类也需要换为id
order by mdate,matchid,team1,team2

总结

除了最后一题恶心外,其他都比比子查询简单(话说子查询最后一题我也没做)
此外,关于join,数据库得到此指令后会优化通过笛卡尔积获取的数据,因此会跳过不符合条件的数据