mp3s
ID
Title
Description
标签
ID
Title
艺术家
ID
Title
artist_relations
mp3ID //call to mp3s.ID
artistID // call to artists.ID
tag_relations
mp3ID //call to mp3s.ID
tagID // call to tags.ID
我在一个查询中需要这个结果:
row1 : mp3ID | mp3Title | mp3Description | tag1,tag2,tag3 | artist1
row2 : mp3ID | mp3Title | mp3Description | tag20,tag22 | artist8,artist5
...
select
mp3.ID as mp3id,
mp3.title as mp3title,
mp3.description as mp3description,
group_concat(distinct artist.title) as artists,
group_concat(distinct tag.title) as tags
from mp3s as mp3
left join artist_relations as ar
on ar.MP3ID = mp3.ID
left join artists artist
on ar.ARTISTID = artist.ID
left join tag_relations as tr
on tr.MP3ID = mp3.ID
left join tags as tag
on tag.ID = tr.TAGID
group by mp3.ID
我试图使用您描述的结构在专门创建的 db 上重现并得到以下结果:
|mp3_id | mp3_title | mp3_description | 艺术家 | 标签 |
| 1 |第一 |第一个 |泽姆菲拉,脾脏 |a,b |
| 2 |第二 |第二个 |列宁格勒,深紫色,齐柏林飞艇 |C,D,E |