PostgreSQL中的GROUP BY和COUNT
发布时间:2020-12-13 16:40:27 所属栏目:百科 来源:网络整理
导读:查询: 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条记录: count_all | post_id-----------+--------- 1 | 6 3 | 4 3 | 5 3 | 1 1 | 9 1 | 10(6
查询:
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条记录: count_all | post_id -----------+--------- 1 | 6 3 | 4 3 | 5 3 | 1 1 | 9 1 | 10 (6 rows) 我只想检索返回的记录数:6。 我使用一个子查询来实现我想要的,但这似乎不是最佳的: 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中如何获取上下文中的记录数?
我想你只需要COUNT(DISTINCT post_id)从投票。
请参阅http://www.postgresql.org/docs/current/static/sql-expressions.html中的“4.2.7。汇总表达式”部分。 编辑:根据Erwin的评论纠正我粗心大意的错误。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |