Postgres嵌套SQL查询以计数字段 [英] Postgres nested SQL query to count field

问题描述

我有几张桌子：

users - id

users_matches - user_id, match_id, team

matches - id, winner

我想计算一下用户拥有许多胜利，失败和联系。 team 是一个整数，可以是1或2。 winner 也是一个整数，但可以是1（team

I'd like to count how many wins, losses, and ties a user has. team is an integer that could be 1 or 2. winner is also an integer, but it can be 1 (team 1 wins), 2 (team 2 wins), or 3 (tie).

我不确定如何组合分组的 count 在Postgres中带有嵌套查询。

I'm not sure how to mix a grouped count with a nested query in Postgres.

推荐答案

假定参照完整性和Postgres 9.4：

Assuming referential integrity and Postgres 9.4:

SELECT *, total - wins - ties AS losses
FROM (
   SELECT count(*) AS total
        , count(*) FILTER (WHERE m.winner = um.team) AS wins
        , count(*) FILTER (WHERE m.winner = 3) AS ties
   FROM   users_matches um
   JOIN   matches m ON m.id = um.match_id
   WHERE  um.user_id = 123;  -- for one given user
) sub;

关于总计 FILTER 子句（引入Postgres 9.4）：

About the aggregate FILTER clause (introduced with Postgres 9.4):

• 如何简化此游戏统计信息查询？

• How can I simplify this game statistics query?

这篇关于Postgres嵌套SQL查询以计数字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！