另一列中具有公用值的列的SQL聚合 [英] SQL aggregation of columns with common value in another column

问题描述

我有这两个表格

我有一个对PostgreSQL的查询有疑问

PLAYER

  playerID title 
 1 Rondo 
 2 Allen 
 3皮尔斯
 4加内特
 5珀金斯< 
  

PLAYS

  playerID TeamID 
 1 1 
 1 2 
 1 3 
 2 1 
 2 3 
 3 1 
 3 3 
  

这就是我的查询

  SELECT DISTINCT concat（N.playerID，'：'，N.title），TID 
 FROM player N 
 INNER JOIN（
 SELECT DISTINCT P.playerID作为PID，teamID作为TID 
 FROM播放P 
）AS derivedTable 
 ON N.playerID = PID 
 ORDER BY concat 
  
 
 
 
查询的结果是： 
 
 
  1：Rondo| 1 
1：Rondo| 2 
1：Rondo| 3 
2：Allen| 1 
2：Allen| 3 
3：Pierce| 1 
3：Pierce| 3 
  
但我想要那样的东西
 1：Rondo| 1，2，3 
2：Allen| 1，3 
3：Pierce| 1，3 
  
我可以使用array_agg，但我真的不知道如何使用
 
解决方案
使用 string_agg（）  
 
 
 SELECT concat（N.playerID，'：'，N.title），
 string_agg（p.TeamID :: text，'，'）as teamid_list 
 FROM player N 
 JOIN播放p ON n.playerID = p.playerID 
 GROUP BY n.playerID，n.title 
 ORDER BY 1; 
  
您的派生表不是必需的（更明显的是）
I have an unresolved doubt about a query I'm making in PostgreSQL.


I have these 2 tables 

PLAYER
playerID      title
1             Rondo
2             Allen
3             Pierce
4             Garnett
5             Perkins<
PLAYS
playerID      TeamID
   1             1
   1             2
   1             3
   2             1
   2             3
   3             1
   3             3
and that's my query 
SELECT DISTINCT concat(N.playerID, ':', N.title), TID 
FROM player N
INNER JOIN (
 SELECT DISTINCT P.playerID  as PID,  teamID as TID
 FROM plays P
 ) AS derivedTable 
ON N.playerID = PID
ORDER BY concat
the result of the query is:
"1:Rondo"  |  1 
"1:Rondo"  |  2
"1:Rondo"  |  3
"2:Allen"  |  1
"2:Allen"  |  3
"3:Pierce" |  1
"3:Pierce" |  3
but I want something like that
"1:Rondo"  |  1, 2, 3
"2:Allen"  |  1, 3
"3:Pierce" |  1, 3
I could use an array_agg, but i really dunno how
解决方案
Use string_agg()
SELECT concat(N.playerID, ':', N.title), 
       string_agg(p.TeamID::text, ',') as teamid_list
FROM player N
  JOIN plays p ON n.playerID = p.playerID
GROUP BY n.playerID, n.title
ORDER BY 1;
Your derived table is not necessary (and the distinct even more so)

                        
这篇关于另一列中具有公用值的列的SQL聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！