a****k
发帖数: 117 | 1 【 以下文字转载自 DataSciences 讨论区 】
发信人: andykk (andykk), 信区: DataSciences
标 题: 求教sqlzoo euro 2012 第十三题
发信站: BBS 未名空间站 (Sun Nov 23 20:53:17 2014, 美东)
题目在这：
http://www.sqlzoo.net/wiki/The_JOIN_operation
第十三题：
我的solution:
SELECT a.mdate, a.team1, SUM(CASE WHEN a.team1=b.teamid THEN 1 ELSE 0 END)
AS score1, a.team2, SUM(CASE WHEN a.team2=b.teamid THEN 1 ELSE 0 END) AS
score2 FROM game a LEFT JOIN goal b ON a.id = b.matchid GROUP BY b.matchid
ORDER BY a.mdate, a.id, a.team1, a.team2
这道题关键是不要漏掉0：0的比赛。我这个解法retrieve了24号的这场比赛，但为什么
27号的0：0没有retrieve出来？ 有谁通过了sqlzoo的测试吗？多谢！
24 June 2012 ENG 0 ITA 0
27 June 2012 POR 0 ESP 0 | d*****8
发帖数: 3 | 2 我也做过这道题，把
SUM(CASE WHEN a.team1=b.teamid THEN 1 ELSE 0 END) AS Score1
改成
ISNULL（SUM(CASE WHEN a.team1=b.teamid THEN 1 ELSE 0 END ），0） AS Score1
加个ISNULL（query,0） | a****k
发帖数: 117 | 3 多谢！你用的是sqlserver把？我用的是mysql, 加了coalesce,跟sqlserver里的ISNULL
功能一样。还是不对啊！
SELECT a.mdate, a.team1, COALESCE(SUM(CASE WHEN a.team1=b.teamid THEN 1 ELSE
0 END),0)
AS score1, a.team2, COALESCE(SUM(CASE WHEN a.team2=b.teamid THEN 1 ELSE 0
END), 0) AS
score2 FROM game a LEFT JOIN goal b ON a.id = b.matchid GROUP BY b.matchid
ORDER BY a.mdate, a.id, a.team1, a.team2
【在 d*****8 的大作中提到】
: 我也做过这道题，把
: SUM(CASE WHEN a.team1=b.teamid THEN 1 ELSE 0 END) AS Score1
: 改成
: ISNULL（SUM(CASE WHEN a.team1=b.teamid THEN 1 ELSE 0 END ），0） AS Score1
: 加个ISNULL（query,0）
| d*****8
发帖数: 3 | | a*********0
发帖数: 41 | 5 select mdate, team1, sum(score1), team2, sum(score2) from (SELECT mdate,
team1,
CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1,
team2,
CASE WHEN teamid = team2 THEN 1 ELSE 0 END score2
FROM game left outer JOIN goal ON matchid = id) tmp
group by tmp.mdate, tmp.team1,tmp.team2
order by tmp.mdate, tmp.team1,tmp.team2
我在外面多加了个select 在里面求sum。 得分为0的没有漏洞而且答案看起来没什么问
题，只是没有得到笑脸 | O*********e
发帖数: 90 | 6 是不是这样？
select mdate,
team1,
(select count(gtime)from goal where teamid = team1 and matchid = id) as
score1,
team2,
(select count(gtime)from goal where teamid = team2 and matchid = id) as
score2
FROM game
order by mdate |
|
