Musicbrainz SQL查询以获取发布组的曲目

Question

我使用musicbrainz.orgpostgresql数据库，我已经安装在本地，并通过python访问。

如何创建一个SQL查询来输出发布组的所有跟踪？我能够获得与特定乐队相关的适当艺术家信息和发行版，但使用以下查询，发行版的曲目是错误的：

strsql_band = "SELECT artist.id, artist.gid, artist.name, artist.comment FROM artist WHERE artist.name=%s AND type>1 ORDER BY type, artist.last_updated DESC"
strsql_memberid = "SELECT entity0, link.begin_date_year, link.end_date_year FROM l_artist_artist l JOIN link ON l.link=link.id WHERE entity1=%s AND link_type=103"
strsql_release = "SELECT id, release_group.name FROM release_group WHERE artist_credit=%s"
# This does not return the correct tracks for the release
strsql_track = "SELECT id, position, name, length FROM track WHERE artist_credit=%s LIMIT 15"

任何帮助都将不胜感激，我已经为此绞尽脑汁好几个小时了。

Answer 1

中音和音轨表将为您提供此信息

以下是适用于我的案例的查询：

SELECT
    array_agg(track.name) as track_list,
    release.gid as release_gid,
    release.release_group as release_group_id
FROM track
INNER JOIN medium
 ON track.medium=medium.id
INNER JOIN release
 ON medium.release=release.id
GROUP BY release.gid, release.release_group limit 5;

样本结果

release_gid release_group   track_list
00000363-74c9-4c3b-bef7-a433eb9687e3    950559  {"Small Screen",Alice,Dauntless,Lullaby,"Grinding the Mill","Four Colors","2000 Gods","This Life","More Than Meets the Eye","The World"}
00000ba0-d5e2-41d7-9c5d-48e8eb04226a    388941  {"Ai, Vanna","Dod māmiņa, kam dodama","Mēs sūdzēsimies Strazdbūrā","Ar Eiropu plecos","Mīļais mans (Oi ļuļi, oi ļuļi)","Tu tik lūr, monamūr","Kocēni, Dikļi un Tožas","Sev augstu laimi vēlēju","Happy mērsrags","Tur es dzēru, tur man tika","Pritonā pie eglītes","Kur ir manas dāvaniņas","Iespraud svecīti","Ints un Ģirts","Mazs putniņš kājām... (Vai Rīga gatava?)","Melngalvju blūzs",Siergriezējs,"Zeva dziesma","Kentaura skaistumkopšanas salonā","Es sakūru uguntiņu (Gaisā skrēja)","Tu nesit... (Nestandarta žoklis)"}
00000ff3-8918-400f-89d5-ec80f52465bf    530779  {"SING IT","Precious Days",トキメキ}
0000172d-6e43-4d7d-8647-da718593a97a    2382060 {"J'ai besoin d'un doliprane","Cœur brisé","Mon proprio","J'me demande","Extra extra terrestre","Bienvenue chez moi (dans ma coloc)","L'amour c'est du pipeau","La caissière du casino",Jean-Marc,"Toutes des salopes"}