mysql - SQL：查询两个单独的表并返回计数值，然后按两个返回分组

Question

我正在努力使这个查询正确。我有返回的表来显示昵称和两个相关的值。这些表是：用户（昵称、姓名等）postAuth（昵称、postID 等） postComm（昵称、comID 等）

如果 numPosts 和 numComments 中返回的两个值都为 NULL（也就是说，如果他们没有评论或创建帖子），我试图让它不包括用户。我不确定如何让它发挥作用。代码如下

SELECT u.nickname, pa.numPosts, pc.numComments
FROM Users u
LEFT JOIN
(SELECT pa.nickname, COUNT(pa.nickname) AS numPosts
    FROM postAuthors pa
    GROUP BY pa.nickname
)pa
ON u.nickname = pa.nickname LEFT JOIN 
(SELECT pc.nickname, COUNT(pc.nickname) AS numComments
    FROM postComments pc
    GROUP BY pc.nickname
)pc
ON u.nickname = pc.nickname;

这创造了

Nickname | numPosts | numComments

bob              1             2
sally            2             1
tommy         NULL          NULL
 etc.......

我不想tommy被展示。

标签： mysqlsqljoingroup-by

Answer 1

只需添加WHERE pc.nickname IS NOT NULL OR pa.nickname IS NOT NULL到查询即

SELECT u.nickname, pa.numPosts, pc.numComments
FROM Users u
LEFT JOIN
(SELECT pa.nickname, COUNT(pa.nickname) AS numPosts
    FROM postAuthors pa
    GROUP BY pa.nickname
)pa
ON u.nickname = pa.nickname LEFT JOIN 
(SELECT pc.nickname, COUNT(pc.nickname) AS numComments
    FROM postComments pc
    GROUP BY pc.nickname
)pc
ON u.nickname = pc.nickname
WHERE pc.nickname IS NOT NULL OR pa.nickname IS NOT NULL

这将排除在 postAuthors 和 postComments 中都没有值的任何用户。