早上好,我想显示与以逗号分隔的 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 |
你几乎得到了答案。您可以使用FIND_IN_SET()
和GROUP_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 |
+----------------------------------+
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句