前言
- 本文对 PostgreSQL 14.x 适用
- 实现的方式是用
unnest
将
正文
-
表
jounal
结构示例id author 2 [张三, 李四, 王五] 2 [张三, 李四] 3 [张三]
-
聚合结果
item cnt 张三 3 李四 2 王五 1
-
sql 语句写法一
select item, count(item) as cnt from (select unnest(journal.author) as item from journal) as tmp group by item
-
sql 语句写法二
select item, count(distinct id) as cnt from journal, unnest(journal.author) as item group by item
-
sql 语句写法三
select item, count(item) as cnt from journal, unnest(journal.author) as item group by item
相关链接
- How to group result by array column in Postgres?
- Group by unique items in an array column and count records that contain each item
本文出自 qbit snap
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net