SQLzoo JOIN教程#13 [英] SQLzoo JOIN tutorial #13

问题描述

我一直在研究SQLzoo问题，但在JOIN教程问题#13中的最后一个问题上遇到了麻烦-如图所示，列出每支球队得分均达到的每场比赛.

I have been working on the SQLzoo problems but having trouble with the last one in the JOIN tutorial question #13 - List every match with the goals scored by each team as shown.

链接: http://sqlzoo.net/wiki/The_JOIN_operation

在他们给出的示例代码中，它使用了一个大小写.我是这样修改的:

In the sample code they gave, it uses a case. I modified it like this:

SELECT game.mdate, game.team1,
  CASE WHEN goal.teamid=game.team1 THEN 1 ELSE 0 END score1, game.team2,
  CASE WHEN goal.teamid=game.team2 THEN 1 ELSE 0 END score2
  FROM game, goal WHERE game.id=goal.matchid
GROUP BY game.mdate, goal.matchid, game.team1, game.team2

他们建议在score1/score2上使用SUM函数将提供答案.对于如何在SQL内创建的这两列上使用SUM函数，我感到困惑.

They suggest that using a SUM function on the score1/score2 will provide the answer. I am confused as to how to use the SUM function on these 2 columns that are created within the SQL.

任何人都可以提供有关如何执行或广泛提及如何以更好的方式编写此SQL查询的提示吗?

Could anyone provide a hint as to how to do or mention in broad terms how to write this SQL query in a better fashion?

推荐答案

好吧，您确实需要 SUM 那些列( SUM 是一个聚合函数，所以这就是为什么那里有一个 GROUP BY ).为了更好地编写查询，您需要丢失旧的隐式 JOIN 样式，并使用ANSI显式的样式:

Well, you do need to SUM those columns (SUM is an aggregation function, that's why you have a GROUP BY there). And as to writing your query in a better fashion, you need to lose that old implicit JOIN style and use the ANSI explicit one:

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

这篇关于SQLzoo JOIN教程#13的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！