sqlzoo-The JOIN operation-solution(11\12\13小题)

最近在刷sql，发现一个不错的练习网站：sqlzoo.

基本上不会的题目在百度里粘贴复制一下都可以找到大家的解答，但是做到TheJoin operation的时候发现11,12,13小题的答案无论如何都对不了。。。后来发现，在这个网站练习的时候是可以自己选择操作平台的，在页面的最上方右上角，而我的操作平台一直是Mysql，难怪直接复制网络上的答案总不能得出正确答案。。。

为了防止有同学遇到个我一样的问题，专门把基于Mysql平台的那三道小题的答案贴出来，希望有人能搜到吧~

11.For every match involving 'POL', show the matchid, date and the number of goals scored.

SELECT matchid,mdate,count(teamid) 
FROM game inner JOIN goal ON matchid = id   
     WHERE (team1 = 'POL' OR team2 = 'POL') group by mdate,matchid 

12.For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'

select matchid,mdate,count(teamid) 
FROM game inner join goal on game.id=goal.matchid 
WHERE teamid='GER' 
group by mdate,matchid  

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 matchid=id 
group by mdate,matchid,team1,team2