继承同一个表的Mysql Inner Join表

Question

我有以下数据库架构。 VHS和DVD表都继承自Suport_Media，并且都包含一部Film(Film翻译成英文就是电影)。我想创建一个 View ，其中包含有关所有可用 Suport_Media 及其包含的电影的信息。为此，我必须将表 suport_media、dvd、vhs 和 film 合并到一个具有内部联接的表中。问题是我不知道如何将 VHS 和 DVD 组合到一张表中，然后将其与 Suport_Media 和 Film 连接。

我尝试过以下代码:

CREATE
VIEW Informatii_Suport_Media AS
SELECT Titlu, Actori_Principali, Tip, Durata_Film, Data_Lansare, Pret
FROM VHS V
INNER JOIN DVD D ON V.Suport_Media_ID <> D.Suport_Media_ID
INNER JOIN DVD D1 ON Suport_Media.Suport_Media_ID = D.Suport_Media_ID
INNER JOIN Film F ON F.Film_ID = D1.Film_ID
GROUP BY V.Suport_Media_ID
ORDER BY V.Suport_Media_ID ASC;

架构位于: https://imageshack.com/i/hl0DNwc1p

Answer 1

将 DVD 和 VHS 分别与其他表连接，然后使用 UNION 组合起来

CREATE VIEW Informatii_Suport_Media AS
SELECT Titlu, Actori_Principali, Tip, Durata_Film, Data_Lansare, Pret
FROM DVD D
INNER JOIN Support_Media S ON S.Suport_Media_ID = D.Suport_Media_ID
INNER JOIN Film F ON F.Film_ID = D.Film_ID
GROUP BY S.Suport_Media_ID
UNION
SELECT Titlu, Actori_Principali, Tip, Durata_Film, Data_Lansare, Pret
FROM VHS V
INNER JOIN Support_Media S ON S.Suport_Media_ID = V.Suport_Media_ID
INNER JOIN Film F ON F.Film_ID = V.Film_ID
GROUP BY S.Suport_Media_ID

绕过在 View 的 FROM 子句中放置子查询的限制的另一种方法是创建另一个 View :

CREATE VIEW DVD_VHS AS
SELECT Support_Media_ID, Film_ID
FROM DVD
UNION
SELECT Support_Media_ID, Film_ID
FROM VHS;

CREATE VIEW Informatii_Suport_Media AS
SELECT Titlu, Actori_Principali, Tip, Durata_Film, Data_Lansare, Pret
FROM DVD_VHS DV
INNER JOIN Support_Media S ON S.Suport_Media_ID = DV.Suport_Media_ID
INNER JOIN Film F ON F.Film_ID = DV.Film_ID
GROUP BY S.Suport_Media_ID
ORDER BY S.Support_Media_ID ASC