有什么问题,欢迎评论或私聊。转载请私聊博主,谢谢。
原题链接:https://sqlzoo.net/wiki/The_JOIN_operation
其他题解连接:https://blog.csdn.net/aiqiyizz/article/details/109057732
题解对应的是英文版题目。
从JOIN开始应该是难度的分水岭了。
6 JOIN
6.1
SELECT matchid, player
FROM goal
WHERE teamid = 'GER'
6.2
SELECT id,stadium,team1,team2
FROM game
WHERE id = 1012
6.3
SELECT player,teamid, stadium, mdate
FROM game JOIN goal ON (id=matchid)
WHERE teamid = 'GER'
6.4
SELECT team1, team2, player
FROM game JOIN goal ON (id=matchid)
WHERE player LIKE 'Mario%'
6.5
前几道题题目没看清就做都对。。
SELECT player, teamid, coach, gtime
FROM goal JOIN eteam on teamid=id
WHERE gtime<=10
6.6
迷惑题意*1
本来以为要输出的是比赛名字和对手队名
SELECT mdate, y.teamname
FROM game JOIN eteam x ON game.team1 = x.id
JOIN eteam y ON game.team2 = y.id
WHERE x.coach = 'Fernando Santos'
最后发现要输出的是比赛名字和自己队名
SELECT mdate, teamname
FROM game JOIN eteam ON game.team1 = eteam.id
WHERE eteam.coach = 'Fernando Santos'
6.7
该题正解有问题,应该添加DISTINCT。
SELECT player
FROM goal JOIN game ON goal.matchid = game.id
WHERE stadium = 'National Stadium, Warsaw'
6.8 More difficult questions
DISTINCT用于排除重复项,WHERE前部分条件排除德国人,第二部分限制与德国对战条件。
SELECT DISTINCT player
FROM game JOIN goal ON matchid = id
WHERE teamid <> 'GER' AND (team1='GER' OR team2='GER')
6.9
SELECT teamname, COUNT(*)
FROM eteam JOIN goal ON id=teamid
GROUP BY teamname
ORDER BY teamname
6.10
SELECT stadium, COUNT(*)
FROM game JOIN goal ON id=matchid
GROUP BY stadium
6.11
SELECT matchid, mdate, COUNT(teamid)
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY matchid, mdate
6.12
SELECT matchid, mdate, COUNT(teamid)
FROM game JOIN goal ON matchid = id
WHERE teamid = 'GER' AND (team1 = 'GER' OR team2 = 'GER')
GROUP BY matchid, mdate
6.13
题意怪,看了一会才捕捉到关键词,需要形成题目要求的表格。
主要要使用game LEFT JOIN goal ON matchid = id
因为存在一场比赛两队都没有得分的情况,这样在game中存在id而在goal中不存在matchid。使用JOIN不会加入表格。
SELECT
mdate,
team1,
SUM(CASE teamid WHEN team1 THEN 1 ELSE 0 END) AS score1,
team2,
SUM(CASE teamid WHEN team2 THEN 1 ELSE 0 END) AS score2
FROM
game LEFT JOIN goal ON matchid = id
GROUP BY
mdate, team1, team2
ORDER BY
mdate, matchid, team1, team2