时间戳

首页 > 解决方案 > 带附加表的三向内连接

mysql - 带附加表的三向内连接

问题描述

我有一个包含以下表格的音乐数据库：

艺术家 (ID, ArtistName)
专辑 (ID, ArtistID, AlbumName)
表演者 (ID, ArtistID, PerformerName)
AlbumPerformers (ID, AlbumID, 歌手, 吉他, 贝斯, 鼓)

这是一个练习模式，因为我正在学习 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？

谢谢。

标签： mysqlinner-join

解决方案

您需要执行额外的 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 |                                                                                         
+------------+-----------+----------------+--------+-------------+-----------------+

推荐阅读