mysql - SQL Count 返回太多行

Question

我有以下 SQL 语句:

    SELECT 
    O.id,
    O.name,
    (SELECT 
            COUNT(U.id)
        FROM
            User U
        WHERE
            U.organization_id = O.id) AS num_users
FROM
    Organization O

User 表和 Organization 表由一个叫做 organization_id 的东西绑定(bind)

当我执行这条语句时，我得到以下(坏)结果:

id |  name   | num_users
------------------------
 1 |  MyOrg  |     2
 2 |  MyOrg  |     0
 3 | testOrg |     0

谁能告诉我如何才能得到只会产生 1 行 pr 的结果。组织？

请注意，我已经尝试使用 DISTINCT 但没有得到更好的结果

Answer 1

SQL Fiddle演示以下查询:

SELECT GROUP_CONCAT(DISTINCT o.id SEPARATOR ', '), O.name, 
       COUNT(U.organization_id) AS MyCount
FROM Organization O
LEFT JOIN User U ON U.organization_id = O.id
GROUP BY O.name;

或者，如果您不需要 id 字段，则:

SELECT O.name, COUNT(U.organization_id) AS MyCount
FROM Organization O
LEFT JOIN User U ON U.organization_id = O.id
GROUP BY O.name;