带有 LEFT JOIN 的 MySQL count()

Question

同一个问题问了很多问题。但先听我说完。我有两个表使用 LEFT JOIN 连接，第一个表是用户表，第二个表只是一个引用表分表

用户表

 user_id |  name   |   division_id
 0       |  ABC    |     0
 1       |  DEF    |     1
 2       |  GHI    |     1

划分表

id  |  name   | protect class
1   |  div_1  |      1

我的原始查询

SELECT count(usr.user_id) 
FROM user as usr 
    LEFT JOIN division as dv 
        ON dv.id = usr.division_id
WHERE 
    dv.protect_class != '1'

但是当我想选择没有分区的用户时

SELECT count(usr.user_id) 
    FROM m_users as usr 
        LEFT JOIN m_division as dv 
            ON dv.id = usr.division_id OR  usr.division_id = '0'
    WHERE 
         dv.protect_class != '1'

事情变得有点奇怪，没有 division_id 或等于 0 的用户会根据获得的部门条目数被多次计数。

我也试过添加 distinct

SELECT distinct count(usr.user_id)
    FROM m_users as usr 
        LEFT JOIN m_division as dv 
            ON dv.id = usr.division_id OR usr.division_id = '0'
    WHERE 
        dv.protect_class != '1'

但我仍然没有运气。当我查询这个时

SELECT distinct usr.*
    FROM m_users as usr 
        LEFT JOIN m_division as dv 
            ON dv.id = usr.division_id OR usr.division_id = '0'
    WHERE 
        dv.protect_class != '1'

然后物理地(手动)计数，计数是正确的，然后如果我应用 count() 函数，计数是错误的。所以我只想征求意见，如何在不计算 LEFT JOIN 中的重复项的情况下计算条目数？

Answer 1

您可以尝试以下 - count(distinct usr.user_id)

SELECT count(distinct  usr.user_id)
    FROM m_users as usr 
        LEFT JOIN m_division as dv 
            ON dv.id = usr.division_id OR usr.division_id = '0'
    WHERE 
        dv.protect_class != '1'