你的结果无法工作的原因是因为你没有任何连接语句。

SELECT IFNULL(COUNT(User.Id), 0) AS UserCount, Company.* 
FROM Company
LEFT JOIN User ON User.CompanyId = Company.Id AND User.CanAccessSystem = true
WHERE Company.CanAccessSystem = true
GROUP BY Company.Id

应该没问题。左连接的重点是主表条目应该始终出现，但是左连接的条目不一定需要出现。

IFNULL() 仅用于返回 0，因为在这种情况下没有适当的用户将呈现 NULL 值。由于 MySQL 不支持布尔值，我不太确定您如何处理布尔值。

我认为我会从一个子查询开始，生成（不相交的）联合：

• A company ID and a (non-zero) count of the users that can access the system where at least one user can access the system
• A company ID and a zero count when no user can access the system

假设 User.CanAccessSystem IS NULL 是为了考虑到左连接而需要的因素，那么就会导致：

SELECT Company.ID, COUNT(*) AS UserCount
    FROM Company, User
    WHERE Company.ID = User.CompanyID
      AND User.CanAccessSystem = true
UNION
SELECT Company.ID, 0 AS UserCount
    FROM Company
    WHERE NOT EXISTS (SELECT * FROM User
                         WHERE Company.ID = User.CompanyID
                           AND User.CanAccessSystem = true)

您可以使用 AND Company.CanAccessSystem = true 过滤两个部分，如果大多数公司无法访问系统，则可能会有益处-或者您可以推迟到最终处理阶段。

然后，您需要将此结果与公司进行直接连接，确保公司能够访问系统的过滤条件在该线路的某个地方应用。

从名义上来看，这将导致以下（尚未测试的）代码：

SELECT UserCount, Company.*
    FROM Company JOIN
        (SELECT Company.ID AS ID, COUNT(*) AS UserCount
             FROM Company, User
             WHERE Company.ID = User.CompanyID
               AND User.CanAccessSystem = true
         UNION
         SELECT Company.ID AS ID, 0 AS UserCount
             FROM Company
             WHERE NOT EXISTS (SELECT * FROM User
                                  WHERE Company.ID = User.CompanyID
                                    AND User.CanAccessSystem = true)
        ) AS NumUsers
         ON Company.ID = NumUsers.ID
    WHERE Company.CanAccessSystem = true

这是一个小技巧，可以利用布尔值为一或零来进行求和（canaccess），以获取可以访问系统的用户数量。为避免与 NULL 值相关的问题，有 COALESCE 可将 NULL 值转换为 1（即可以访问系统）。

mysql> select * from companies;
+------+------------+-----------+
| name | company_id | canaccess |
+------+------------+-----------+
| foo  |          1 |         1 |
| bar  |          2 |         1 |
| baz  |          3 |         1 |
| quux |          4 |         1 |
+------+------------+-----------+
4 rows in set (0.00 sec)

mysql> select * from users;
+---------+------------+-----------+
| user_id | company_id | canaccess |
+---------+------------+-----------+
|       1 |          1 |         1 |
|       2 |          1 |         0 |
|       3 |          1 |         1 |
|       4 |          2 |      NULL |
|       5 |          2 |         1 |
|       6 |          2 |         0 |
|       7 |          3 |         1 |
|       8 |          3 |         0 |
|       9 |          4 |         0 |
+---------+------------+-----------+
9 rows in set (0.00 sec)

mysql> select company_id, sum(canaccess) from 
(select users.user_id, coalesce(users.canaccess,1) as canaccess, 
users.company_id, companies.name from companies join users 
on (users.company_id = companies.company_id) where companies.canaccess = 1 ) foo 
group by company_id;
+------------+----------------+
| company_id | sum(canaccess) |
+------------+----------------+
|          1 |              2 |
|          2 |              2 |
|          3 |              1 |
|          4 |              0 |
+------------+----------------+
4 rows in set (0.00 sec)

你应该统计用户数量，这些用户的User.CanAccessSystem值为true。可以参考一下下面的做法。

 count(case when User.CanAccessSystem then true end)

如果User.CanAccessSystem为false（默认情况下），则案例表达式将返回NULL，count（expr）计算表达式不为空的项数。 （请注意，在翻译中，代码格式可能会被破坏）

那么...这个？

SELECT COUNT(case when User.CanAccessSystem then true end) AS UserCount, Company.*
FROM Company LEFT JOIN User
ON Company.id = user.companyId   -- I had to guess, this seems to be missing in your query
WHERE Company.CanAccessSystem= true
GROUP BY Company.Id

p.s. 为了不排除没有用户的公司，我使用LEFT JOIN而不是INNER JOIN。

我没有安装MySQL... 我尝试在MSSQL上运行，并且我相信，在稍微修改一下以适用于MySQL后，它将处理您的情况...

SELECT c.CompanyID, c.CompanyName, SUM(CASE u.CanAccessSystem WHEN 1 then 1 else 0 end) 
FROM Company c LEFT OUTER JOIN
    [User] u
ON u.CompanyID = c.CompanyID
WHERE c.CanAccessSystem = 1
GROUP BY c.CompanyID, c.CompanyName