本文介绍了在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中查找给定团队的所有比赛的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！