想象一下資料：
id category
1 A
1 B
2 A
2 R
2 C
3 Z
我的目標是輸出：
id categories
1 {"A","B"}
2 {"A","R","C"}
3 {"Z"}
但是在使用代碼時：
select distinct id,
array(select distinct category::varchar from test) as categories
from my_table
我得到：
id categories
1 {"A","B","R","C","Z"}
2 {"A","B","R","C","Z"}
3 {"A","B","R","C","Z"}
如何獲得所需的輸出？Group by 在這種情況下不起作用，因為我沒有使用聚合函式。
uj5u.com熱心網友回復：
使用JSON_AGG聚合函式怎么樣？
SELECT id,
JSON_AGG(category) AS category
FROM tab
GROUP BY id
ORDER BY id
在此處查看演示。
uj5u.com熱心網友回復：
假設表有名稱test
select distinct id,
array(select distinct category::varchar from test b where b.id = a.id) as categories
from test a
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/491456.html
標籤：sql 数组 PostgreSQL 通过...分组 聚合函数