PostgreSQL 中的 GROUP BY 和 COUNT [英] GROUP BY and COUNT in 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 条记录:

Returns n records in Postgresql:

 count_all | post_id
-----------+---------
 1         | 6
 3         | 4
 3         | 5
 3         | 1
 1         | 9
 1         | 10
(6 rows)

我只想检索返回的记录数:6.

I just want to retrieve the number of records returned: 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://www.postgresql.org/docs/current/static/sql-expressions.html" 4.2.7. 聚合表达式"部分/www.postgresql.org/docs/current/static/sql-expressions.html.

根据欧文的评论纠正了我粗心的错误.

Corrected my careless mistake per Erwin's comment.

这篇关于PostgreSQL 中的 GROUP BY 和 COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！