MySQL中使用GROUP_CONCAT配合WHERE查询同时拥有指定模块的客户

背景说明 #

需要从以下三张表中筛选出同时持有module 3和module 4的所有客户，预期仅返回client4。

表结构 #

Client
+--------------------+
| id      |  name    |
+--------------------+
| 1       | client1  |
| 2       | client2  |
| 3       | client3  |
| 4       | client4  |
+--------------------+

Module
+--------------------+
| id      |  name    |
+--------------------+
| 1       | module1  |
| 2       | module2  |
| 3       | module3  |
| 4       | module4  |
+--------------------+

Client_Module
+-------------------------+
| client_id  | module_id  |
+-------------------------+
| 1          | 2          |
| 1          | 3          |
| 2          | 1          |
| 2          | 2          |
| 2          | 4          |
| 3          | 4          |
| 4          | 1          |
| 4          | 2          |
| 4          | 3          |
| 4          | 4          |
+-------------------------+

原有写法错误原因 #

你编写的SQL中WHERE条件client_module.module_id = 4 AND client_module.module_id = 3逻辑不成立：单条Client_Module记录的module_id只能取一个值，不可能同时等于3和4，因此查询永远返回空结果。
如果仅用client_module.module_id IN (3,4)，只会筛选出持有两个模块中任意一个的客户，无法满足“同时持有”的要求。

正确实现方案 #

方案1：GROUP BY + HAVING计数校验（推荐，适合多模块匹配场景） #

SELECT     client.id, client.name, GROUP_CONCAT(DISTINCT module.id ORDER BY module.id) AS modules
FROM       client
LEFT JOIN  client_module ON client_module.client_id = client.id
LEFT JOIN  module ON module.id = client_module.module_id
WHERE      client_module.module_id IN (3,4)
GROUP BY   client.id, client.name
HAVING     COUNT(DISTINCT client_module.module_id) = 2;

逻辑说明：

• 先用WHERE条件过滤出所有和3、4模块相关的关联记录，缩小计算范围
• 按客户维度分组后，统计每个客户匹配到的不同目标模块数量，计数等于2就说明同时持有两个要求的模块
• DISTINCT用于规避Client_Module表存在重复关联数据时的计数错误，如果表中已经设置了client_id和module_id的联合唯一约束，可以去掉DISTINCT提升查询性能

方案2：多次关联匹配（适合匹配模块数量少的场景） #

SELECT DISTINCT c.id, c.name
FROM client c
INNER JOIN client_module cm1 ON c.id = cm1.client_id AND cm1.module_id = 3
INNER JOIN client_module cm2 ON c.id = cm2.client_id AND cm2.module_id = 4;

通过两次关联分别校验客户是否持有3号、4号模块，只有同时满足两个关联条件的客户才会被返回。

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