MySQL多表关联统计满足distance>5条件的唯一客户数问题

需求 #

从多表生成报表，统计每个公司拥有distance>5的唯一客户数。

数据库结构 #

Companies表 #

| id | title |
| --------   | 
| 1  | apple |

Clients表 #

| id | name   |
| ---|--------|
| 1  | Jordan |

company_clients表 #

| ID | company_id | client_id  |
| -- | ---------- | ---------- |
| 1  |    1       |     1      |

service_clients表 #

| ID | client_id | service_name |
| -- | --------- | -----------  |
| 1  | 1         | x1           |
| 2  | 1         | x2           |
| 3  | 1         | x3           |

service_client_dailies表 #

| ID | service_id | service_date | distance |
| -- | --------- | ------------- | -------- |
| 1  | 1         | 2023-02-03    | 15       |
| 1  | 1         | 2023-02-04    | 0        |
| 1  | 1         | 2023-02-05    | 7        |
| 1  | 2         | 2023-02-05    | 15       |
| 1  | 2         | 2023-02-07    | 6        |

现有查询语句 #

SELECT
    companies.title,
       COUNT ( company_clients.ID ) 
FROM
    companies
    LEFT JOIN company_clients ON company_clients.company_id = companies.ID 
    INNER JOIN service_clients ON service_clients.client_id = company_clients.client_id 
    INNER JOIN service_client_dailies ON service_client_dailes.service_id = service_clients.id 
    WHERE  service_client_dailies.distance > 5
GROUP BY 
    companies.id,company_clients.client_id

问题描述 #

现有查询无法得到正确结果，例如期望Apple公司对应5个符合条件的客户，但结果不符合预期。

原查询存在以下几个核心问题：

• 拼写错误：关联表时写了service_client_dailes，正确表名是service_client_dailies，直接导致关联逻辑失效。
• 重复计数：直接COUNT(company_clients.ID)会因为一个客户对应多条满足distance>5的记录而重复统计，无法得到唯一客户数。
• 分组逻辑错误：按companies.id,company_clients.client_id分组会让每个客户单独成组，无法按公司汇总唯一客户数量。
• 过滤逻辑错误：INNER JOIN会把没有符合条件客户的公司直接过滤掉，且WHERE子句的条件会进一步丢失这类公司的数据。

正确查询语句（保留所有公司，无符合条件则计数为0） #

SELECT
    c.title AS company_title,
    COUNT(DISTINCT cc.client_id) AS qualified_unique_clients
FROM
    companies c
LEFT JOIN company_clients cc ON cc.company_id = c.id
LEFT JOIN service_clients sc ON sc.client_id = cc.client_id
LEFT JOIN service_client_dailies scd ON scd.service_id = sc.id AND scd.distance > 5
GROUP BY
    c.id, c.title

语句说明 #

1. 用COUNT(DISTINCT cc.client_id)确保每个客户仅被统计一次，避免因多条服务记录重复计数。
2. 将distance>5的条件移到LEFT JOIN的ON子句中，避免过滤掉没有符合条件客户的公司。
3. 修正表名拼写错误，保证关联逻辑正常运行。
4. 按公司的id和title分组，确保每个公司仅返回一行统计结果。

可选：仅统计有符合条件客户的公司 #

如果不需要保留无符合条件客户的公司，可以调整为以下语句：

SELECT
    c.title AS company_title,
    COUNT(DISTINCT cc.client_id) AS qualified_unique_clients
FROM
    companies c
INNER JOIN company_clients cc ON cc.company_id = c.id
INNER JOIN service_clients sc ON sc.client_id = cc.client_id
INNER JOIN service_client_dailies scd ON scd.service_id = sc.id
WHERE scd.distance > 5
GROUP BY
    c.id, c.title

内容的提问来源于stack exchange，提问作者lock