求一种特殊多表连接查询sql语句

[cnzxy]

mysql> SELECT * FROM content;
+----+------+
| id | tags |
+----+------+
| 1 | 1,2 |
| 2 | 2,3 |
+----+------+


mysql> SELECT * FROM tag;
+----+-------+
| id | name |
+----+-------+
| 1 | php |
| 2 | mysql |
| 3 | java |

有上面两个表，查content表的时候，想得到每条记录中tags用逗号分割的每个tag名称，怎么做？
也就是说有没有一种查询语句可以得到下面的结果：
| id | tags_name |
+----+------+
| 1 | php, mysql |
| 2 | mysql, java |
+----+------+

[fangzhaoguo]

[Quote=引用楼主 cnzxy 的回复:]
mysql> SELECT * FROM content;
+----+------+
| id | tags |
+----+------+
| 1 | 1,2 |
| 2 | 2,3 |
+----+------+


mysql> SELECT * FROM tag;
+----+-------+
| id | name |
+----+-------+
……
[/Quote]

貌似这个得知道你的数据是怎么存储的呀

[nicenight]

select id, group_concat(name) as tags_name
from
(
    select content.id, tag.name
    from content left join tag
    on find_in_set(tag.id, content.tags)
    order by tag.id
) as t
group by id

[ACMAIN_CHM]

select c.id,group_concat(t.name )
from content c  ,tag t
where find_in_set(t.id,  c.tags)
group by  c.id

[nicenight]

看了版主的，才发现自己多此一举了，中间表就直接可以得结果的……

    select content.id, group_concat(tag.name)
    from content left join tag
    on find_in_set(tag.id, content.tags)
    group by content.id