英文:
postgres aggregate results from joined table
问题
我有以下表格
players：id，姓名，...
votes：id，player_id，周 - 换句话说，在投票中，每个player_id将有多条记录
我想获得name (来自players)，vs：week[] (来自votes，其中player_id == players.id)
也就是说，我想知道每个玩家在哪些周投票
select players.name, array_agg(votes.week) as weeks
from public.votes
join public.players
ON votes.player_id = players.id
group by players.name;
这将为每个玩家返回一行，其中周以列表的形式显示。可能吗？
英文:
Newbie Postgres question.
I have the following tables
players: id, name, ...
votes: id, player_id, week - i.e. in votes there will be multiple records for each player_id
I want to get name (from players), vs: week[] (from votes where player_id == players.id)
i.e. I want to know which weeks each player voted in
select players.name, votes.week
from public.votes
join public.players
ON votes.player_id = players.id
gets me the number of results in the votes in table, but that includes endless duplicates of name. I want 1 row per player, with the weeks in a list
Is that possible?
答案1
得分: 0
你可以按照球员的姓名分组，并使用 array_agg 获取一组周数：
SELECT players.name, ARRAY_AGG(votes.week)
FROM public.votes
JOIN public.players ON votes.player_id = players.id
GROUP BY playes.name
英文:
You could group by the players' name and use array_agg to get an array of weeks:
SELECT players.name, ARRAY_AGG(votes.week)
FROM public.votes
JOIN public.players ON votes.player_id = players.id
GROUP BY playes.name
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论