sql - PostgreSQL:如果表存在则返回函数否则 View 中为0

Question

我无法让 PostgreSQL 识别匹配表为空，因此应该为下面 View 中的 wins 和 matches 列输出 0。相反，我一直看到一个空洞的观点。当我应该为每一行获取“玩家 ID”、“玩家 ID”、0、0 时。可能是什么问题？

CREATE VIEW player_standings AS
SELECT 
    players.id,
    players.name,
    CASE WHEN EXISTS (SELECT * FROM matches) THEN COUNT(matches.winner) ELSE 0 END AS wins,
    CASE WHEN EXISTS (SELECT * FROM matches) THEN COUNT(matches.winner) + COUNT(matches.loser) ELSE 0 END AS matches
FROM players
INNER JOIN matches
    ON players.id = matches.winner
GROUP BY players.id
ORDER BY
    wins DESC;

Answer 1

1. 您不需要 case 因为 count(null) 给出 0。
2. 你应该left join 匹配，以防给定玩家没有匹配。
3. 您应该加入 比赛两次以获得输赢次数。

---

CREATE OR REPLACE VIEW player_standings AS
SELECT 
    p.id,
    p.name,
    COUNT(m1.winner) AS wins,
    COUNT(m1.winner) + COUNT(m2.loser) AS matches
FROM players p
LEFT JOIN matches m1
    ON p.id = m1.winner
LEFT JOIN matches m2
    ON p.id = m2.loser
GROUP BY p.id, p.name
ORDER BY
    wins DESC;