MySQL group_concat 与 where 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22297878/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL group_concat with where clause
提问by telefoontoestel
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.
我在 Group_Concat 和 where 过滤器上遇到了这个问题。在我的表中，我得到了链接到客户端的模块名称。我想按模块名称搜索客户端，但在 concat 组中，我仍然想查看客户端拥有的所有模块。目前它将显示所有具有这些模块的客户端，但它只会显示该特定模块。我不知道如何让它们一起工作。
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 |
+-------------------------+
Query:
询问：
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)
Results:
结果：
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 |
+------------------------------------------------------+
回答by Amit Singh
You can Try Like this.
你可以试试这样。
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
回答by G one
You are using client_module.module_idchange it to client_module.client_id.
Use group bywith group_cancat
您正在使用client_module.module_id将其更改为client_module.client_id. 使用group by与group_cancat
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 client_module.client_id IN (1,2,4)
group by client.id, client.name
回答by echo_Me
try that
试试看
SELECT client.id, client.name, GROUP_CONCAT(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.id IN (1,2,4)
group by client.id
You have this wrong
client_module.module.idfixed toclient_module.module_idAND you are already saying to your WHERE clause to return only 1 and 2 .
Added Group by to work when you have Group_Concat
你有这个错误
client_module.module.id修复client_module.module_id并且您已经对您的 WHERE 子句说只返回 1 和 2 。
当你有 Group_Concat 时添加了 Group by 来工作
