我有 3 个表:NAMES、REGISTRATIONS 和 RENEWALS。我正在使用 LEFT JOIN 连接具有公共(public) ID 的 3 个表。

我需要统计每个用户的REGISTRATIONS数量，以及RENEWALS数量。我试过在 GROUP BY 字段中使用不同的选项，但似乎都不起作用。

这是 SELECT 语句:

SELECT 
    names.name_id AS 'Names ID'
    ,names.name AS Name
    ,count(registrations.date) AS Registrations
    ,count(renewals.date) AS Renewals

FROM names

LEFT JOIN registrations

    ON names.name_id = registrations.name_id

LEFT JOIN renewals

    ON renewals.name_id = registrations.name_id

GROUP BY names.name_id, registrations.name_id, renewals.name_id;

这是 3 个表:

    TABLE: names
    +---------+------+
    | name_id | name |
    +---------+------+
    |       1 | Ana  |
    |       2 | John |
    |       3 | Paul |
    +---------+------+

    TABLE: registrations
    +-----------------+---------+---------------------+-------+
    | registration_id | name_id | date                | value |
    +-----------------+---------+---------------------+-------+
    |               1 |       1 | 2014-01-30 13:15:02 | 15    |
    |               2 |       2 | 2014-05-01 18:01:44 | 15    |
    |               3 |       2 | 2014-07-08 15:10:43 | 20    |
    |               4 |       3 | 2012-09-28 17:45:32 | 15    |
    |               5 |       3 | 2014-01-09 18:26:14 | 20    |
    |               6 |       3 | 2015-01-10 13:22:01 | 25    |
    +-----------------+---------+---------------------+-------+

    TABLE: renewals
    +------------+---------+---------------------+-------+
    | renewal_id | name_id | date                | value |
    +------------+---------+---------------------+-------+
    |          1 |       1 | 2015-01-30 00:00:00 | 5     |
    |          2 |       1 | 2016-02-12 00:00:00 | 5     |
    |          3 |       1 | 2015-06-01 00:00:00 | 5     |
    |          4 |       1 | 2013-11-24 00:00:00 | 5     |
    |          5 |       2 | 2015-01-27 00:00:00 | 5     |
    +------------+---------+---------------------+-------+

这是我得到的错误结果:

    +----------+------+---------------+----------+
    | Names ID | Name | Registrations | Renewals |
    +----------+------+---------------+----------+
    |        1 | Ana  |             4 |        4 |
    |        2 | John |             2 |        2 |
    |        3 | Paul |             3 |        0 |
    +----------+------+---------------+----------+

我期望的正确结果是:

    +----------+------+---------------+----------+
    | Names ID | Name | Registrations | Renewals |
    +----------+------+---------------+----------+
    |        1 | Ana  |             1 |        4 |
    |        2 | John |             2 |        1 |
    |        3 | Paul |             3 |        0 |
    +----------+------+---------------+----------+

如何修复查询以获得正确的结果？

最佳答案

试试这个:

SELECT 
    names.name_id AS 'Names ID'
    ,names.name AS Name
    ,count(distinct registrations.registration_id) AS Registrations
    ,count(distinct renewals.renewal_id) AS Renewals
FROM names    
LEFT JOIN registrations    
    ON names.name_id = registrations.name_id    
LEFT JOIN renewals    
    ON renewals.name_id = registrations.name_id    
GROUP BY names.name_id, registrations.name_id, renewals.name_id;

每当我遇到此类问题时，我发现如果您的服务器可以接受，运行 select * 查询会很有帮助。像这样:

SELECT *
FROM names    
LEFT JOIN registrations    
    ON names.name_id = registrations.name_id    
LEFT JOIN renewals    
    ON renewals.name_id = registrations.name_id    ;

这会让你看到你真正在数什么。

关于Mysql 在使用多个 JOIN 时返回不正确的 COUNT？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/40428342/