我可以在多个条件下连接两个表吗？例如，我们可以在 ON 关键字之后进行两列匹配吗？

Question

我在某处读到，如果有 N 个表，则应该有 N-1 条件。？如果只有两个表，我们可以在 ON 关键字后加入多个条件吗？ 例如在 a.id=b.id 和 a.name=b.name 和 a.class=b.class 上

Answer 1

您可以在多个条件下联接，也可以在多个条件下联接同一个表。

例如，假设我们有下表：

create table a1
(weddingTable INT(3),
 tableSeat INT(3),
 tableSeatID INT(6),
 Name varchar(10));

insert into a1
 (weddingTable, tableSeat, tableSeatID, Name)
 values (001,001,001001,'Bob'),
 (001,002,001002,'Joe'),
 (001,003,001003,'Dan'),
 (002,001,002001,'Mark');

create table a2
 (weddingTable int(3),
 tableSeat int(3),
 Meal varchar(10));

insert into a2
(weddingTable, tableSeat, Meal)
values 
(001,001,'Bob'),
(001,002,'Dan'),
(001,003,'Salmon'),
(002,001,'Steak');

让我们做一个查询示例：

select a1.* 
from a1
inner join a2 on a1.weddingTable=a2.weddingTable
and a1.tableSeat=a2.tableSeat 
and a1.Name=a2.Meal

结果：

weddingTable    tableSeat   tableSeatID  Name
        1          1            1001      Bob

查看另一个示例的演示 演示

同一个表多个连接条件。

CREATE TABLE CORPORATE(
E_NAME VARCHAR(10),
E_ID INT,
E_DEPT VARCHAR(10),
E_LOC VARCHAR(10));

INSERT INTO CORPORATE VALUES('RAM',1,'HR','DELHI');
INSERT INTO CORPORATE VALUES('RAM',1,'SALES','DELHI');
INSERT INTO CORPORATE VALUES('VARUN',2,'IT','BANGALORE');
INSERT INTO CORPORATE VALUES('VARUN',2,'MARKETING','HYDERABAD');
INSERT INTO CORPORATE VALUES('RAVI',3,'FINANCE','KOCHI');
INSERT INTO CORPORATE VALUES('RAVI',3,'FINANCE','TRIVANDRUM');

SELECT C1.E_NAME,
       C1.E_ID,
       C1.E_DEPT,
       C1.E_LOC 
FROM CORPORATE AS C1
INNER JOIN CORPORATE AS C2 ON C1.E_ID=C2.E_ID 
AND C1.E_DEPT<>C2.E_DEPT AND C1.E_LOC<>C2.E_LOC;

演示