目录
- 前言
- 表jounal结构示例
- sql 语句
前言
- 本文对 PostgreSQL 14.x 适用
- 实现的方式是用unnest将
表jounal结构示例
id author
2 [张三, 李四, 王五]
2 [张三, 李四]
3 [张三]
聚合结果
item cnt张三 3李四 2王五 1
sql 语句
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
以上就是PostgreSQL对数组元素聚合基本方法示例的详细内容,更多关于PostgreSQL 数组元素聚合的资料请关注悠久资源其它相关文章!