SQL查询未找到SUM和COUNT中的所有行

卡利姆斯

select coalesce(ratings.positive,0) as positive,coalesce(ratings.negative,0) as negative,articles.id,x.username,commentnumb, 
    articles.category,
    articles."createdAt",
    articles.id,
    articles.title,
    articles."updatedAt"
    FROM articles
    LEFT JOIN (SELECT id AS userId,username,about FROM users) x ON articles.user_id = x.userId
    LEFT JOIN (SELECT id,
        article_id,
        sum(case when rating = '1' then 1 else 0 end) as positive,
        sum(case when rating = '0' then 1 else 0 end) as negative
        from article_ratings
        GROUP by id
        ) as ratings ON ratings.article_id = articles.id
    LEFT JOIN (SELECT article_id,id,
       count(article_id) as commentNumb
       from article_comments
       GROUP by id
       ) as comments ON comments.article_id = articles.id
    WHERE articles."createdAt" <= :date
    group by ratings.positive,ratings.negative,articles.id,x.username,commentnumb
    order by articles."createdAt" desc
    LIMIT 10

该代码正在运行，但是与SUM和COUNT函数相比，我有更多的注释和更多的评级。

如何解决此查询？

这是使用postgres。

我做了一些实验，似乎第三个加入评论的人是引起问题的人。

文卡塔拉曼R

在派生表中，理想情况下，应该使用article_id进行分组。但是，您正在根据ID进行分组。因此，您获得的不仅仅是派生表中的必要行。我已修改查询以满足您的需求。

    SELECT COALESCE(ratings.positive,0) AS positive,COALESCE(ratings.negative,0) AS negative,articles.id,x.username,commentnumb, 
        articles.category,
        articles."createdAt",
        articles.id,
        articles.title,
        articles."updatedAt"
        FROM articles
        LEFT OUTER JOIN (SELECT id AS userId,username,about FROM users) x ON articles.user_id = x.userId
        LEFT OUTER JOIN (SELECT article_id,
            SUM(case when rating = '1' then 1 else 0 end) as positive,
            SUM(case when rating = '0' then 1 else 0 end) as negative
            FROM article_ratings
            GROUP by article_id 
            ) AS ratings ON ratings.article_id = articles.id
        LEFT OUTER JOIN (SELECT article_id,
           count(article_id) as commentNumb
           FROM article_comments
           GROUP by article_id
           ) AS comments ON comments.article_id = articles.id
        WHERE articles."createdAt" <= :date
        ORDER BY articles."createdAt" desc
        LIMIT 10;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。