postgresql 返回 array_agg() 中的第一个元素
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30997186/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return first element in array_agg()
提问by Nandakumar V
I am writing a query to get all players for all teams. Instead of looping in the application, I decided to get the players of all teams in a single query using array_agg(). I have written the query as follows:
我正在编写一个查询以获取所有团队的所有球员。我没有在应用程序中循环，而是决定使用array_agg(). 我已将查询编写如下：
SELECT team_id, array_agg(team_name) AS teamname,
array_agg(player_id||'##'||player_name) AS playerdetails
FROM team
INNER JOIN players ON team_id = player_team
GROUP BY team_id
This query gives me the result as below, in the result set the teamnameis being repeated (exactly to the no. of players)
这个查询给了我如下的结果，在结果集中teamname重复（正好是玩家数量）
team_id team_name playerdetails
1 {Australia,Australia,Australia,Australia} {"5##Glenn Donald McGrath","6##Shane Warne","2##Steve Waugh","1##Adam Gilchrist"}
2 {India,India,India,India} {"8##Kapil Dev","11##Saurav Ganguly","3##Rahul Dravid","9##Sachin Tendulkar"}
3 {"South Africa","South Africa","South Africa","South Africa"} {"12##Gary Kristen","4##Shaun Pollock","7##Jacques Kallis","10##Alan Donald"}
Is there any way to return the result like this
有没有办法像这样返回结果
team_id team_name playerdetails
1 Australia {"5##Glenn Donald McGrath","6##Shane Warne","2##Steve Waugh","1##Adam Gilchrist"}
I have achieved it using a subquery, but want to know if its possible to write it without the subquery
我已经使用子查询实现了它，但是想知道是否可以在没有子查询的情况下编写它
SELECT team_id, teamname[1], playerdetails
FROM (
SELECT team_id, array_agg(team_name) AS teamname,
array_agg(player_id||'##'||player_name) AS playerdetails
FROM team
INNER JOIN players ON team_id = player_team
GROUP BY team_id) AS tempresult
The sqfiddleis here. And I am using Postgresql 8.4
该sqfiddle在这里。我正在使用 Postgresql 8.4
[EDIT]
I was actually thinking of hack to the GROUP BYlimitation column "team.team_status" must appear in the GROUP BY clause or be used in an aggregate functionwhen try to retrieve the a column which was not been specified in group by
[编辑]当尝试检索未在 group by 中指定的列时，
我实际上是在考虑破解GROUP BY限制column "team.team_status" must appear in the GROUP BY clause or be used in an aggregate function
SELECT team_id, array_agg(team_name) AS teamname,
array_agg(player_id||'##'||player_name) AS playerdetails,
team_status -- could be replaced by something like array_agg(team_status)[0] or customfunction(team_status)
FROM team
INNER JOIN players ON team_id = player_team
GROUP BY team_id
回答by Nandakumar V
It was actually a mistake from my part... the answer to my first question lies in that query itself. I just have to enclose the (array_agg(team_name))[1], earlier I tried it without the brackets.
这实际上是我的一个错误......我的第一个问题的答案在于该查询本身。我只需要附上(array_agg(team_name))[1]，之前我在没有括号的情况下尝试过。
SELECT team_id, (array_agg(team_name))[1] AS teamname,
array_agg(player_id||'##'||player_name) AS playerdetails
FROM team
INNER JOIN players ON team_id = player_team
GROUP BY team_id
回答by Patrick
Very simply, do not aggregate the team_namebut GROUP BYit:
很简单，不要聚合team_name但GROUP BY它：
SELECT team_id, team_name, array_agg(player_id||'##'||player_name) AS playerdetails
FROM team
JOIN players ON team_id = player_team
GROUP BY team_id, team_name;
