【Tips】
- sqlzoo官网链接:
- 每个系列开头介绍所用表及其信息
- 每道题均测试通过,含有题目描述、代码和结果
- 题目为自己简写,最好在官网查看具体题目
- 部分测试结果不完整,仅为一部分截图
合并表格之足球赛数据库练习链接:
SELECT matchid,player FROM goal WHERE teamid='GER'
SELECT id,stadium,team1,team2 FROM game WHERE id=1012
SELECT goal.player,goal.teamid,game.stadium,game.mdate FROM game JOIN goal ON (id=matchid) WHERE teamid='GER'
SELECT team1,team2,player FROM game JOIN goal ON (id=matchid) WHERE player LIKE 'Mario%'
SELECT player, teamid, coach,gtime FROM goal JOIN eteam on teamid=id WHERE gtime<=10
SELECT mdate,teamname FROM game JOIN eteam ON (team1=eteam.id) WHERE coach='Fernando Santos'
SELECT player FROM goal JOIN game ON id=matchid WHERE stadium='National Stadium, Warsaw'
SELECT DISTINCT player FROM game JOIN goal ON id=matchid WHERE (team1='GER' OR team2='GER') AND goal.teamid != 'GER'
SELECT teamname,COUNT(player) FROM eteam JOIN goal ON id=teamid GROUP BY teamname
SELECT stadium,COUNT(player) FROM game JOIN goal ON id=matchid GROUP BY stadium
SELECT matchid,mdate,COUNT(player) FROM game JOIN goal ON matchid = id WHERE (team1 = 'POL' OR team2 = 'POL') GROUP BY matchid,mdate
SELECT matchid,mdate,COUNT(player) FROM game JOIN goal ON id=matchid WHERE (team1='GER' OR team2='GER') AND teamid='GER' GROUP BY matchid,mdate
SELECT mdate, team1, SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) AS score1, team2, SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) AS score2 FROM game LEFT JOIN goal ON matchid = id GROUP BY mdate,matchid,team1,team2