PostgreSQL如何获取COUNT(*)的最大值 实现发帖评论Top10用户查询

你的现有语句已经正确统计了每个用户的发帖数和评论数，只需补充排序和截取规则即可满足需求，以下是两种常见场景的调整方案：

方案1：按发帖数+评论数总活跃度排序取前10 #

如果是筛选总互动量最高的用户，对两个统计字段求和后排序即可：

SELECT 
  fullname, 
  (SELECT COUNT(*) FROM posts WHERE posts.author_id = users.id) AS total_posts,
  (SELECT COUNT(*) FROM comments WHERE comments.author_id = users.id) AS total_comments 
FROM users
-- 可选：过滤掉没有任何发帖/评论的用户
WHERE EXISTS (SELECT 1 FROM posts WHERE posts.author_id = users.id)
   OR EXISTS (SELECT 1 FROM comments WHERE comments.author_id = users.id)
ORDER BY (total_posts + total_comments) DESC
LIMIT 10;

方案2：优先按发帖数排序，发帖数相同再按评论数排序取前10 #

如果是优先考量发帖量，仅在发帖量一致时对比评论量，调整ORDER BY规则即可：

SELECT 
  fullname, 
  (SELECT COUNT(*) FROM posts WHERE posts.author_id = users.id) AS total_posts,
  (SELECT COUNT(*) FROM comments WHERE comments.author_id = users.id) AS total_comments 
FROM users
ORDER BY total_posts DESC, total_comments DESC
LIMIT 10;

大数据量场景优化写法 #

如果三张表数据量较大，子查询的执行效率偏低，可以改用JOIN+预聚合的写法提升性能：

SELECT 
  u.fullname,
  COALESCE(p.post_cnt, 0) AS total_posts,
  COALESCE(c.comment_cnt, 0) AS total_comments
FROM users u
LEFT JOIN (
  SELECT author_id, COUNT(*) AS post_cnt FROM posts GROUP BY author_id
) p ON u.id = p.author_id
LEFT JOIN (
  SELECT author_id, COUNT(*) AS comment_cnt FROM comments GROUP BY author_id
) c ON u.id = c.author_id
ORDER BY total_posts DESC, total_comments DESC
LIMIT 10;

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