假设我有3个SQL表:
表水果:
|----------|------------|
| fruit_id | fruit_name |
|----------|------------|
| 1 | Banana |
| 2 | Apple |
| 3 | Pear |
|----------|------------|
表颜色:
|----------|------------|
| color_id | color_name |
|----------|------------|
| 91 | Yellow |
| 92 | Green |
| 93 | Red |
|----------|------------|
表协会:
|----------|----------|
| fruit_id | color_id |
|----------|----------|
| 1 | 91 |
| 1 | 92 |
| 2 | 91 |
| 2 | 92 |
| 2 | 93 |
| 3 | 93 |
|----------|----------|
我如何列出水果和相关的颜色:
|----------|----------|
| fruit_id | color_id |
|----------|----------|
| 1 | 91,92 |
| 2 | 91,92,93 |
| 3 | 93 |
|----------|----------|
我尝试的是:
SELECT
fruit_id,
GROUP_CONCAT(COALESCE(color_id, "0")) AS color_id
FROM fruits
JOIN associatives
LEFT JOIN colors
ON FIND_IN_SET(fruit_id, color_id)
GROUP BY fruit_id
如果您没有颜色的水果,则只需要LEFT JOIN
。但是您的示例数据并未显示。无论如何,我从@hackerman调整样本,包括另一种水果,以显示如何处理该情况
SQL演示
select f.fruit_id, GROUP_CONCAT( COALESCE (a.color_id, 0) ) as color_id
from fruits f
left join associatives a
ON f.fruit_id = a.fruit_id
group by f.fruit_id
您只需要查询associates
表即可获得所需的结果:
select fruit_id, GROUP_CONCAT(color_id) as color_id
from associatives
group by fruit_id
演示: sqlfiddle demo