我正在写一个查询,以获取所有球队的所有球员.我没有在应用程序中循环,而是决定使用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
这个查询给我的结果如下,在结果集中,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"}
有没有办法像这样返回结果
team_id team_name playerdetails
1 Australia {"5##Glenn Donald McGrath","6##Shane Warne","2##Steve Waugh","1##Adam Gilchrist"}
我已经使用子查询实现了它,但是我想知道是否可以在没有子查询的情况下编写它
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
sqfiddle人在这里.我使用的是Postgresql 8.4
[EDIT]
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