sqlite - 在 SQLite 和 Count 中连接两个表

Question

我有两个名为“喜欢”和“评论”的表，我想要一个包含每个特定用户的喜欢和评论计数的表，我在 SQLite 中编写了以下查询，但结果不适用于所有用户，计数两个表中用户的值都是点赞数和评论数的倍数。

SELECT 
  likes.liker_name, likes.liker_id, likes.profile_picture , 
  COUNT(comments.commenter_name) AS comment_count, COUNT( likes.liker_id) AS like_count
FROM likes  
LEFT JOIN comments 
  ON likes.liker_name = comments.commenter_name 
GROUP BY 
  likes.liker_name 
ORDER BY 
  COUNT( likes.liker_id) DESC

如何获得两个表中存在的用户的正确计数值？

Answer 1

问题是:有些用户有评论但没有点赞，有些用户有点赞但没有评论，有些用户两者都有，有些则没有。因此，我建议使用联合查询并再次求和

SELECT
    u.name, u.id, u.profile_picture,
    SUM(u.like_count) AS like_count, SUM(u.comment_count) AS comment_count
FROM (
    SELECT
        liker_name AS name, liker_id AS id, profile_picture,
        COUNT(*) AS like_count, 0 AS comment_count
    FROM
        likes
    GROUP BY
        liker_name, liker_id, profile_picture
    UNION ALL
    SELECT
        commenter_name AS name, commenter_id AS id, profile_picture,
        0 AS like_count, COUNT(*) AS comment_count
    FROM
        comments
    GROUP BY
        commenter_name, commenter_id, profile_picture
) AS u
GROUP BY
    u.name, u.id, u.profile_picture

如果您有单独的用户表，您还可以将点赞计数和评论计数子查询保留到用户表中

SELECT
    u.name, u.id, u.profile_picture, l.cnt AS like_count, c.cnt AS comment_count
FROM
    users u
    LEFT JOIN
        (SELECT liker_id, COUNT(*) AS cnt
        FROM likes
        GROUP BY liker_id
        ) AS l
        ON u.user_id = l.liker_id
    LEFT JOIN
        (SELECT commenter_id, COUNT(*) AS cnt
        FROM comments
        GROUP BY commenter_id
        ) AS c
        ON u.user_id = c.commenter_id
WHERE l.cnt > 0 OR c.cnt > 0

无论你怎么做，你都必须在单独的子查询中计算评论和点赞。如果您在加入后进行计数，那么您将对结果进行求和，其中记录可能会重复(左侧的记录)，并且您会得到错误的计数。