PostgreSQL统计数组中的元素个数
PostgreSQL大约 912 字需求
统计文章表中的标签数组字段中的不同分类个数
unnest
PostgreSQL
提供了unnest
关键词,将数组拆解为一条条记录(不去重)。
select unnest(topics) as unnest_topic from post;
输出
z-blog=# select unnest(topics) as unnest_topic from post;
unnest_topic
--------------
Java
Linux
OpenResty
Linux
SEO
(5 行记录)
统计分类个数
select count(1) as topic_count, unnest(topics) unnest_topic from post group by unnest_topic order by topic_count desc
输出
z-blog=# select count(1) as topic_count, unnest(topics) unnest_topic from post group by unnest_topic order by topic_count desc;
topic_count | unnest_topic
-------------+----------------
80 | PostgreSQL
77 | Linux
73 | OpenResty
69 | Java
59 | MySQL
(5 行记录)
去重分类分页查询
分类总数
select count(distinct unnest_topic) as unnest_topic_count from post, unnest(topics) as unnest_topic;
分类分页
select distinct unnest(topics) as unnest_topic from post order by unnest_topic limit 10 offset 30;
阅读 1000 · 发布于 2020-03-26
————        END        ————
扫描下方二维码关注公众号和小程序↓↓↓

昵称: