MySQL group_concat与where子句 [英] MySQL group_concat with where clause

问题描述

我遇到了Group_Concat和where过滤器的问题.在我的表中，我得到了链接到客户端的模块名称.我想按模块名称搜索客户端，但是在concat组中，我仍然想查看该客户端拥有的所有模块.当前它将显示具有这些模块的所有客户端，但仅显示该特定模块.我不知道如何使它们一起工作.

I got this problem with Group_Concat and a where filter. In my table i got module names which are linked to a client. I want to search clients by module name, but in the group concat i still want to see all modules that are owned by the client. currently it will display all clients with those modules, but it will only display that specific module. I can't figure out how to make them both work together.

关于如何获得预期结果的任何建议?

Any suggestions on how to get my expected result??

这些是一些基本表，我尝试过的查询以及获得的结果和我真正想要的结果

These are some basic tables and the query i tried along with results i get and the result i really wanted

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          |
+-------------------------+

查询:

SELECT     client.id, client.name, GROUP_CONCAT(module.name) 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      module.id IN (1,2)

结果:

Received
+--------------------------------------------------+
| id     | name     | modules                      |
+--------------------------------------------------+
| 1      | client1  | module2                      |
| 2      | client2  | module1,module2              |
| 4      | client4  | module1,module2              |
+--------------------------------------------------+

Expected
+------------------------------------------------------+
| id     | name     | modules                          |
+------------------------------------------------------+
| 1      | client1  | module2,module3                  |
| 2      | client2  | module1,module2,module4          |
| 4      | client4  | module1,module2,module3,module4  |
+------------------------------------------------------+

推荐答案

您可以尝试这样.

SELECT     client.id, client.name, GROUP_CONCAT(module.name) 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
group by client.id Having Find_In_Set('module1',modules)>0 or Find_In_Set('module2',modules)>0

SQL小提琴演示

这篇关于MySQL group_concat与where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！