php - mysql 调用表 mp3、艺术家、标签

Question

mp3

ID 
Title 
Description

标签

ID
Title

艺术家

ID
Title

艺术家关系

mp3ID //call to mp3s.ID
artistID // call to artists.ID

标签关系

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
...

Answer 1

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

我尝试用您描述的结构在专门创建的数据库上进行重现，并得到了这个结果:

| mp3_id | mp3_标题 | mp3_描述 |艺术家 |标签 |

| 1 |第一 |第一个 | zemfira，脾| a,b |

| 2 |第二 |第二个 |列宁格勒，深紫色，齐柏林飞艇| c、d、e |