mysql - 加入条件出错

Question

我有 4 个表如下 -

tbl_confession -

 Confession_id  User_id  title  message
 1              1        new    foo
 2              1        abcd   yes
 4              1        bar    no

tbl_comment -

Comment_id  user_id confession_id  message
1           2       1              foobar
2           2       1              barfoo

tbl_confessionlike -

id  confession_id  user_id
1   1              1
2   1              2
3   2              2

tbl_confessionview -

 id  user_id  confession_id
 1   1        1

预期的结果应该是——

 ConfressionId  title  message  total_comments  total_likes  total_views
 1              new    foo      2               2            1
 2              abcd   yes      0               1            0
 4              bar    no       0               0            0

我用来实现上述结果的查询是 -

SELECT c.confession_id
      ,c.title
      ,c.message
      ,COUNT(co.comment_id)
      ,COUNT(cl.id)
      ,COUNT(cv.ID)
FROM tbl_confession c
LEFT JOIN tbl_comment co on c.confession_id = co.confession_id
LEFT JOIN tbl_confessionlike cl on c.confession_id = cl.confession_id
LEFT JOIN tbl_confessionview cv on c.confession_id = cv.confession_id
GROUP BY c.confession_id
        ,c.title
        ,c.message

输出结果-

ConfressionId  title  message  total_comments  total_likes  total_views
 1              new    foo      4               4            4
 2              abcd   yes      0               1            0
 4              bar    no       0               0            0

但是我计数为 4 而不是 2。此外，如果我想看到每个用户的相同结果，则 user_id = 1 保持不变，但其他用户为空。我无法解决错误。提前致谢。

Answer 1

我认为您遇到了问题，因为您是根据表 tbl_confession 中的数据进行分组的。在您的示例中，有 2 个评论、2 个赞和 1 个 View 生成 2 * 2 * 1 = 4 记录，当您在主目录中使用 c.confession_id 进行分组时查询时，count() 将返回 4。我建议将每个部分表上的记录分组，然后将它们连接到主查询，如下所示:

     SELECT c.confession_id,
          c.title,
          c.message,
          comment.total_comment,
          likes.total_likes,
          views.totla_views
    FROM tbl_confession c
    INNER JOIN 
        (SELECT 
          confession_id,
          COUNT(Comment_id) AS total_comment
          FROM tbl_comment
          GROUP BY confession_id
        ) 
        AS comment ON comment.confession_id = c.confession_id
    INNER JOIN
        (SELECT
          confession_id,
          COUNT(id) as total_likes
          FROM tbl_confessionlike
          GROUP BY confession_id
        )
        AS likes ON likes.confession_id = c.confession_id
    INNER JOIN
        (SELECT
          confession_id,
          COUNT(id) AS totla_views
          FROM tbl_confessionview
          GROUP BY confession_id
        )
        AS views ON views.confession_id = c.confession_id
    WHERE c.user_id = some_id