时间戳

首页 > 解决方案 > 多个连接和总和的问题

sql - 多个连接和总和的问题

问题描述

如果有以下三个 PostgreSQL 表：

帖子表：

postid | title | author | created

投票表：

postid | username | vote

如果用户对帖子投了赞成票，则 vote 等于 1，如果用户没有投票，则为 0，如果用户对帖子投反对票，则为 -1。

评论表：

commentID | parentID | postID | content | author | created

如果评论不是回复，则 parentID 为 null。

我现在想收到每个帖子的标题、作者、创建日期、所有投票的总和以及当前登录用户的投票和评论数量。我已经对用户的投票有疑问，并在这里询问，有人帮我得到了以下查询：

SELECT post.postID as postID, post.title as title, post.author as author,
       post.created as created,
       COALESCE(sum(votes.vote), 0) as voteCount,
       COALESCE(sum(votes.vote) FILTER (WHERE votes.username = :username), 0) as userVote
       FROM post 
       LEFT JOIN votes ON post.postID = votes.postID 
       GROUP BY post.postID 
       ORDER BY voteCount DESC

现在我尝试另一个LEFT JOIN来获取这样的评论数量：

COUNT(DISTINCT comments) FILTER (WHERE comments.parentID IS NULL) as numComments
LEFT JOIN comments on post.postID = comments.postID

然而，虽然评论的数量有效，但每个帖子的投票数是错误的，因为由于其他加入行似乎多次出现产生错误的总和，我在想办法解决这个问题时遇到了一些麻烦。

我已经尝试将评论数作为子查询获取，以便它独立于未成功的投票数。任何进一步的帮助将不胜感激！:-)

标签： sqlpostgresqlsuminner-joinsql-null

解决方案

分别计算值。joins 导致笛卡尔积。这是相关子查询或横向连接有帮助的地方：

SELECT p.*, v.*, c.*
FROM post p CROSS JOIN LATERAL
     (SELECT SUM(v.vote) as voteCount,
             SUM(v.vote) FILTER (WHERE v.username = :username), 0) as userVote
      FROM votes v
      WHERE p.postID = v.postID 
     ) v CROSS JOIN LATERAL
     (SELECT SUM(c.vote) as commentCount,
             SUM(c.vote) FILTER (WHERE c.username = :username), 0) as userVote
      FROM comments c
      WHERE p.postID = c.postID 
     ) c
ORDER BY voteCount DESC;

推荐阅读