MySQL的 - GROUP_CONCAT querywithout组
问题描述:
我有3个表:设计,类别和design_categoryMySQL的 - GROUP_CONCAT querywithout组
我需要,一排用diferent类别中的每个设计,而是采用与CONCAT类别一栏。接下来我要做的:
SELECT d.id, d.name, c.id, GROUP_CONCAT(c.id)
FROM design d
LEFT JOIN design_category dc ON dc.design_id = d.id
LEFT JOIN category c ON dc.category_id = c.id
GROUP BY d.id
+----+-----------------------------------+------+--------------------+
| id | name | id | GROUP_CONCAT(c.id) |
+----+-----------------------------------+------+--------------------+
| 2 | Design 1 | 177 | 177,178 |
| 4 | Design 2 | 179 | 179,177,178 |
| 5 | Design 3 | 177 | 177,178 |
| 6 | Design 4 | 177 | 177,178 |
| 7 | Design 5 | 177 | 177,178 |
| 8 | Design 6 | 181 | 181,180 |
+----+-----------------------------------+------+--------------------+
但我不会这样的事情:
+----+-----------------------------------+------+--------------------+
| id | name | id | GROUP_CONCAT(c.id) |
+----+-----------------------------------+------+--------------------+
| 2 | Design 1 | 177 | 177,178 |
| 2 | Design 1 | 178 | 177,178 |
| 4 | Design 2 | 177 | 179,177,178 |
| 4 | Design 2 | 178 | 179,177,178 |
| 4 | Design 2 | 179 | 179,177,178 |
| 5 | Design 3 | 177 | 177,178 |
| 5 | Design 3 | 178 | 177,178 |
| 6 | Design 4 | 177 | 177,178 |
| 6 | Design 4 | 178 | 177,178 |
| 7 | Design 5 | 177 | 177,178 |
| 7 | Design 5 | 178 | 177,178 |
| 8 | Design 6 | 180 | 181,180 |
| 8 | Design 6 | 181 | 181,180 |
+----+-----------------------------------+------+--------------------+
有可能在MySQL?
答
这应该工作:
select x.id, x.name, y.id, z.grouped_id from FROM design x
LEFT JOIN design_category xy ON xy.design_id = x.id
LEFT JOIN category y ON xy.category_id = y.id
left join(
SELECT d.id id , GROUP_CONCAT(c.id) grouped_id
FROM design d
LEFT JOIN design_category dc ON dc.design_id = d.id
LEFT JOIN category c ON dc.category_id = c.id
GROUP BY d.id) zon z.id= x.id
我想改变'c.id'到'分钟(c.id)'在SQL应该工作。但我没有测试。 –