SQL学习四（SQL ZOO - JOIN）

最新推荐文章于 2025-01-13 18:02:07 发布

原创最新推荐文章于 2025-01-13 18:02:07 发布 · 341 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍两个实用的SQL查询案例：一是列出所有非德国队球员在对阵德国队比赛中进球的名单；二是展示每场比赛中两支队伍的得分情况，通过使用CASE WHEN语句实现对比赛结果的详细统计。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、THE JOIN OPERATION

The example query shows all goals scored in the Germany-Greece quarterfinal.

Instead show the name of all players who scored a goal against Germany.

HINT

Select goals scored only by non-German players in matches where GER was the id of either team1 or team2.

You can use teamid!='GER' to prevent listing German players.

You can use DISTINCT to stop players being listed twice.

select distinct(player) from goal join game on matchid = id where teamid != 'GER' and(team1 = 'GER' or team2 = 'GER')

13.

List every match with the goals scored by each team as shown. This will use "CASE WHEN" which has not been explained in any previous exercises.

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
...

Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

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 mdate, matchid, team1, team2