mysql - 查询SQL在一个字段中选择双id

Question

早上好，我想显示与以逗号分隔的 id 字段关联的所有名称。

我想complaint_type.name在投诉表中填写所有内容complaint.id_type_complaint：

My expectation select result is : 
SELECT complaint.id, complaint.notes, complaint_type.name
FROM complaint
LEFT JOIN complaint_type ON complaint_type.id = complaint.id_complaint_type
result :
+-------------------------------
|1 | Wet and broke | Broke, Wet
|2 | Lost Goods    | Lost
+-------------------------------

(complaint_type table)
+---+--------+
|id | name   |
+---+--------+
|1  | Broke  |
|2  | Lost   |
|3  | Wet    |
+---+--------+

(complaint table)
+---------------------------------------+
| id | notes         | id_type_complain |
+---------------------------------------+
|1   | Wet and Broke | 1,3              |
|2   | Lost Goods    | 2                |

标签： mysqlsql

Answer 1

你几乎得到了答案。您可以使用FIND_IN_SET()andGROUP_CONCAT()来解决您的问题：

SELECT c.id, c.notes, GROUP_CONCAT(t.name) AS complaint_names
FROM complaint AS c LEFT JOIN complaint_type AS t
ON FIND_IN_SET(t.id, c.id_type_complain)
GROUP BY c.notes
ORDER BY c.id

这将导致：

+----------------------------------+
|id| notes         |complaint_names|
+----------------------------------+
|1 | Wet and broke | Broke, Wet    |
|2 | Lost Goods    | Lost          |
+----------------------------------+

SQLFiddle 演示