本文介绍了GROUP BY和COUNT在PostgreSQL中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
查询:
SELECT COUNT(*) as count_all, posts.id as post_id FROM posts INNER JOIN votes ON votes.post_id = posts.id GROUP BY posts.id;在Postgresql中返回 n p>
Returns n records in Postgresql:
count_all | post_id -----------+--------- 1 | 6 3 | 4 3 | 5 3 | 1 1 | 9 1 | 10 (6 rows)我只想检索返回的记录数: code> 6 。
我使用子查询来实现我想要的,但这似乎不是最佳的:
I used a subquery to achieve what I want, but this doesn't seem optimum:
SELECT COUNT(*) FROM ( SELECT COUNT(*) as count_all, posts.id as post_id FROM posts INNER JOIN votes ON votes.post_id = posts.id GROUP BY posts.id ) as x;我如何在PostgreSQL中获得此上下文中的记录数?
How would I get the number of records in this context right in PostgreSQL?
推荐答案我想你只需要 COUNT(DISTINCT post_id)FROM votes 。
请参阅 http中的4.2.7。汇总表达式部分://www.postgresql/docs/current/static/sql-expressions.html 。
编辑:根据Erwin的评论更正了我的粗心错误。
Corrected my careless mistake per Erwin's comment.
更多推荐
GROUP BY和COUNT在PostgreSQL中
发布评论