我有一个带有下表的音乐数据库:
艺术家(ID,ArtistName)
专辑(ID,ArtistID,专辑名称)
表演者(ID,ArtistID,PerformerName)
AlbumPerformers(ID,专辑ID,歌手,吉他,贝斯,鼓)
这是我正在学习MySQL时的一种实践模式,因此我可能没有在设置它方面做得最好,但是我想拥有尽可能多的表以便为我提供更多选择。
我想提出一个内部联接查询,该查询将为我提供艺术家,专辑和乐队中每个位置的名称。
以下内容为我提供了此信息,但将PerformerID代替了Performer名称:
SELECT artists.Artist, albums.AlbumName, albumperformers.singer
FROM artists
INNER JOIN albums ON artists.artist_id=albums.artist_id
INNER JOIN albumperformers ON albumperformers.album_id=albums.album_id;
我该如何构造它,以便给我表演者名称而不是ID?
谢谢。
最佳答案
您需要执行其他JOIN来解决歌手,贝斯,吉他和鼓领域,这似乎是Performers.ID的FK。
我已经使用以下表格模式进行了测试:
CREATE TABLE Artists (ID INT, ArtistName VARCHAR(255));
CREATE TABLE Albums (ID INT, ArtistID INT, AlbumName VARCHAR(255));
CREATE TABLE Performers (ID INT, ArtistID INT, PerformerName VARCHAR(255));
CREATE TABLE AlbumPerformers (ID INT, AlbumID INT, singer INT, guitar INT, bass INT, drumer INT);
以下语句:
SELECT
Artists.ArtistName,
Albums.AlbumName,
PerformerSinger.PerformerName as Singer,
PerformerDrumer.PerformerName as Drumer,
PerformerGuitar.PerformerName as Guitar,
PerformerBass.PerformerName as Bass
FROM Artists
INNER JOIN Albums ON Albums.ArtistID = Artists.ID
INNER JOIN AlbumPerformers ON AlbumPerformers.AlbumID = Albums.ID
INNER JOIN Performers AS PerformerSinger on AlbumPerformers.singer = PerformerSinger.ID
INNER JOIN Performers AS PerformerDrumer on AlbumPerformers.drumer = PerformerDrumer.ID
INNER JOIN Performers AS PerformerGuitar ON AlbumPerformers.guitar = PerformerGuitar.ID
INNER JOIN Performers AS PerformerBass ON AlbumPerformers.bass = PerformerBass.ID;
产生期望的结果:
+------------+-----------+----------------+--------+-------------+-----------------+
| ArtistName | AlbumName | Singer | Drumer | Guitar | Bass |
+------------+-----------+----------------+--------+-------------+-----------------+
| Beatles | Revolver | Paul McCartney | Ringo | John Lennon | George Harrison |
+------------+-----------+----------------+--------+-------------+-----------------+
关于mysql - 三向内部联接,带有附加表,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/56821092/