在PostgreSQL中查找给定团队的所有比赛 [英] Find all matches for given team in PostgreSQL

问题描述

我要在桌子上找到个匹配项。两队的比赛都有名称和 uuid （ team_a_uuid

I've to a table matches. Matches have a name and a uuid for both teams (team_a_uuid and team_b_uuid) that play against each other.

uuid | name | team_a_uuid | team_b_uuid
-----|------|-------------|------------
d7e..| foo  | 5db46a15-...| 82732895-..
334..| bar  | 75ab1a39-...| 9fcedf80-..
...  | ...  | ...         | ...

我有第二张桌子 teams 。

uuid | player_1_uuid | player_2_uuid
-----|---------------|--------------
729..| f432f7bc-63...| e022ccb6-7...
d0f..| c9548a8e-b7...| a28441cb-2...
...  | ...           | ...

这就是我想要得到的：给我一支球队，比赛uuid

Here is what I'd like to get: Give me a team and the match uuids it has played in

uuid | player_1_uuid | player_2_uuid | match_uuids
-----|---------------|---------------|---------------------
729..| f432f7bc-63...| e022ccb6-7... | {'d7e...', '334...'}
d0f..| c9548a8e-b7...| a28441cb-2... | {'abc...', 'def...'}

我真的被困在这里。干杯！

I'm really stuck here. Cheers!

推荐答案

键是 array_agg（）。您可以通过显式 join 或相关子查询来做到这一点：

The key is array_agg(). You can do this either with an explicit join or a correlated subquery:

select t.*,
       (select array_agg(m.uuid)
        from matches m
        where t.uuid in (m.team_a_uuid, m.team_b_uuid)
       ) as match_uuids
from teams t;

这篇关于在PostgreSQL中查找给定团队的所有比赛的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！