使用 PFK 的 MySQL View

Question

我有这个表:

Create table table1 (
 id Int UNSIGNED NOT NULL,
 name VARCHAR(255) NOT NULL, 
 Primary Key(id),
 Unique Key `name` (`name`)) 
ENGINE = InnoDB;

Create table table2 (
 id1 Int UNSIGNED NOT NULL,
 id2 Int UNSIGNED NOT NULL,
 Primary Key (id1,id2)) 
ENGINE = InnoDB;

Alter table table2 add Foreign Key (id1) references table1 (id) 
on delete  restrict on update  restrict;
Alter table table2 add Foreign Key (id2) references table1 (id) 
on delete  restrict on update  restrict;

我想问，是否有可能如何创建一种 View 类型，其中包含两个用户的名称(来自table1)，这在表2？感谢您的帮助。

Answer 1

试试这个:

CREATE OR REPLACE VIEW ViewName
AS
SELECT
    T1.Name Name1, T2.Name Name2
FROM Table2 T
    JOIN Table1 T1
        ON T.Id1 = T1.Id
    JOIN Table1 T2
        ON T.Id2 = T2.Id;