将RIGHT JOIN与COUNT相结合 [英] Combining RIGHT JOIN with COUNT

问题描述

我正在尝试获取每个用户changes_cc表中条目数量的列表.并非所有用户都在其中输入条目，但是由于某种原因，它为每个具有0个条目的用户返回"1".我假设这是因为它正在统计JOIN ed表中的条目.我怎样才能使它变为"0"?

I'm trying to get a list of the number of entries in the changes_cc table by each user. Not all users have made entries into it, however for some reason it's returning "1" for each user that has 0 entries. I'm assuming that it's because it's counting the entries in the JOINed table. How can I make it so that it is "0" instead?

SELECT COUNT(*) as num, users.id, realname, username
            FROM changes_cc
            RIGHT JOIN users
                ON changes_cc.user_id = users.id
            GROUP BY users.id

推荐答案

我认为这应该工作-在changes_cc表中计算一个特定字段，而不是*:

I think this should work -- count a specific field in the changes_cc table vs counting *:

SELECT u.id, realname, username, COUNT(c.id) as num
FROM users u 
    LEFT JOIN changes_cc c 
        ON u.user_id = c.id
GROUP BY u.id

相对于RIGHT JOIN，我更喜欢阅读LEFT JOIN，但是它们都是OUTER JOINs并且工作相同.

I prefer reading a LEFT JOIN over a RIGHT JOIN, but they are both OUTER JOINs and work the same.

这篇关于将RIGHT JOIN与COUNT相结合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！