我有两个表:playernames和比赛。
SELECT * FROM playernames;
id | name
----+------------------
38 | Abe Lincoln
39 | Richard Nixon
40 | Ronald Reagan
(3 rows)
SELECT * FROM matches;
match_id | winner | loser
----------+--------+-------
6 | 38 | 39
8 | 38 | 39
9 | 39 | 38
10 | 38 | 39
11 | 40 | 39
(5 rows)
我需要创建一个返回四列的查询:id,name,wins,matchs。但是,即使有了联接和子查询,我似乎也无法在一个查询中获得全部信息。我最近来的是执行两个单独的查询。这是用于计算每位玩家总胜利的查询:
SELECT playernames.id, name, COUNT(*) AS wins
FROM matches, playernames
WHERE winner = playernames.id
GROUP BY playernames.id, winner;
id | name | wins
----+------------------+------
38 | Abe Lincoln | 3
39 | Richard Nixon | 1
40 | Ronald Reagan | 1
(3 rows)
但是如果我想正确计算每个玩家的比赛总数,我必须发出一个单独的查询:
SELECT playernames.id, name, COUNT(*)
FROM matches, playernames
WHERE playernames.id = winner
OR playernames.id = loser
GROUP BY playernames.id;
id | name | count
----+------------------+-------
40 | Ronald Reagan | 1
38 | Abe Lincoln | 4
39 | Richard Nixon | 5
(3 rows)
我避免了将这些错误组合到一个查询中时所做的许多错误尝试,从而使该问题变得混乱。谁能建议我如何正确地将这两个查询合并为一个?
我将使用一个联接来获得玩家参加的所有比赛,然后计算一个case
表达式以仅提取他赢得的比赛:
SELECT playernames.id, name,
COUNT(CASE playernames.id WHEN winner THEN 1 ELSE NULL END) AS wins,
COUNT(match_id) AS matches
FROM playernames
LEFT JOIN matches ON playernames.id IN (winner, loser)
GROUP BY playernames.id, name;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句