SQLZOO JOIN答案

最新推荐文章于 2025-10-18 23:31:29 发布

原创最新推荐文章于 2025-10-18 23:31:29 发布 · 329 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

sql 专栏收录该内容

6 篇文章

订阅专栏

一系列SQL查询练习，涉及数据联接、筛选、聚合等操作，包括查找特定球队进球球员、比赛详细信息、教练信息、进球时间、比赛日期和场地等。

链接：https://sqlzoo.net/wiki/The_JOIN_operation
在这里插入图片描述

在这里插入图片描述
1.

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 (game.id=goal.matchid)
WHERE goal.teamid='GER'

4. 在这里插入图片描述

SELECT game.team1,game.team2,goal.player
FROM game
JOIN goal ON game.id=goal.matchid
WHERE goal.player LIKE 'Mario%'

在这里插入图片描述

SELECT goal.player, goal.teamid, eteam.coach, goal.gtime
FROM goal 
JOIN eteam ON goal.teamid=eteam.id
WHERE gtime<=10

在这里插入图片描述

SELECT game.mdate, eteam.teamname
FROM game
JOIN eteam ON game.team1=eteam.id
WHERE eteam.coach='Fernando Santos'

在这里插入图片描述

SELECT goal.player
FROM goal
JOIN game ON goal.matchid=game.id
WHERE game.stadium='National Stadium, Warsaw'

在这里插入图片描述

SELECT DISTINCT goal.player
FROM goal JOIN game ON goal.matchid=game.id 
WHERE (game.team1='GER' OR game.team2='GER' )AND goal.teamid!='GER'

在这里插入图片描述

SELECT eteam.teamname, COUNT(goal.player)
FROM eteam JOIN goal ON eteam.id=goal.teamid
GROUP BY(eteam.teamname)
ORDER BY eteam.teamname

在这里插入图片描述

SELECT game.stadium, COUNT(goal.player)
FROM game 
JOIN goal ON game.id = goal.matchid
GROUP BY(game.stadium)

在这里插入图片描述

SELECT goal.matchid, game.mdate, COUNT(goal.matchid) AS count
FROM game JOIN goal ON matchid = id 
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY goal.matchid, game.mdate

在这里插入图片描述

SELECT game.id, game.mdate, COUNT(game.id)
FROM game JOIN goal ON game.id=goal.matchid
WHERE goal.teamid='GER'
GROUP BY game.id, game.mdate

在这里插入图片描述

SELECT game.mdate, 
  game.team1, SUM(CASE WHEN goal.teamid=game.team1 THEN 1 ELSE 0 END) AS score1, 
  game.team2, SUM(CASE WHEN goal.teamid=game.team2 THEN 1 ELSE 0 END) AS score2
FROM game LEFT JOIN goal ON game.id=goal.matchid
GROUP BY game.mdate, game.id, game.team1,game.team2
ORDER BY game.mdate, game.id, game.team1, game.team2