PostgreSQL查询：按关联comments数降序返回posts表记录

现有posts表与comments表为一对多关联，两表初始结构与数据如下：

Posts表 #

id | text
---------
1  | "foo"
2  | "bar"
3  | "baz"

Comments表 #

id | post_id | text
-------------------
1  | 2       | "lorem"
2  | 2       | "ipsum"
3  | 3       | "dolor"

需求为返回所有posts条目，按关联评论数从高到低排序，输出格式匹配如下预期：

id | text
---------
2  | "bar"    (2 Total comments)
3  | "baz"    (1 Total comments)
1  | "foo"    (0 Total comments)

可直接运行的查询语句 #

SELECT
  p.id,
  CONCAT(p.text, '    (', COUNT(c.id), ' Total comments)') AS text
FROM posts p
LEFT JOIN comments c
  ON p.id = c.post_id
GROUP BY p.id
ORDER BY COUNT(c.id) DESC;

关键点说明 #

• 必须使用LEFT JOIN关联评论表，否则没有任何评论的post（比如id=1的foo）会因为匹配不到评论记录被过滤，无法出现在结果中
• 统计评论数时用COUNT(c.id)而非COUNT(*)：左连接场景下，无评论的post对应的评论表字段值全为NULL，COUNT函数会自动忽略NULL值，能正确统计到0条评论
• PostgreSQL支持主键分组后直接查询同表其他字段，所以GROUP BY只需要写posts表主键p.id即可，不需要额外加p.text，不会触发非聚合字段报错
• 字符串拼接用||运算符或者CONCAT函数都可以，效果一致

内容的提问来源于stack exchange，提问作者cashews