左联接显示联接表中的多个值
左联接显示联接表中的多个值
提问于 2022-03-15 10:15:09
具有以下SQL：
create table Team(id integer, attacker int, midfield int, defender int);
insert into Team(id, attacker, midfield, defender) values(1, 50, 51, 51);
insert into Team(id, attacker, midfield, defender) values(2, 50, NULL, 55);
insert into Team(id, attacker, midfield, defender) values(12, 50, 53, NULL);
create table Footballer(id integer, name varchar(100));
insert into Footballer(id, name) values (50, "AbbaDude");
insert into Footballer(id, name) values (51, "BravoDude");
insert into Footballer(id, name) values (52, "Charlie");
insert into Footballer(id, name) values (53, "Dude");
insert into Footballer(id, name) values (54, "Elfonso");
insert into Footballer(id, name) values (55, "Fix");
select t.id, t.attacker, t.midfield, t.defender, f.name from Team t
left join Footballer f on f.id = t.id我明白了：
id attacker midfield defender name
1 50 51 51 NULL
2 50 NULL 55 NULL
12 50 53 NULL NULL与数字(50、51、55等)不同，我希望看到表Footballer中的名称。
id attacker midfield defender
1 AbbaDude BravoDude BravoDude
2 AbbaDude NULL Fix
12 AbbaDude Dude NULL 回答 2
Stack Overflow用户
回答已采纳
发布于 2022-03-15 10:25:03
您可以使用以下查询：
select t.id, at.name, md.name, df.name
from Team t
left join Footballer at on at.id = t.attacker
left join Footballer md on md.id = t.midfield
left join Footballer df on df.id = t.defenderStack Overflow用户
发布于 2022-03-15 10:22:51
你需要temId值在足球运动员表。所以，你必须使用这个代码：
create table Footballer(id integer,teamId integer , name varchar(100))
insert into Footballer(id, name) values (50,[teamId], "AbbaDude");
insert into Footballer(id, name) values (51,,[teamId], "BravoDude");
select t.id, t.attacker, t.midfield, t.defender, f.name from Team t
left join Footballer f on f.teamId = t.id所有代码：
create table Team(id integer, attacker int, midfield int, defender int);
insert into Team(id, attacker, midfield, defender) values(1, 50, 51, 51);
insert into Team(id, attacker, midfield, defender) values(2, 50, NULL, 55);
insert into Team(id, attacker, midfield, defender) values(12, 50, 53, NULL);
create table Footballer(id integer,teamID INTEGER, name varchar(100));
insert into Footballer(id,teamID, name) values (50,1, 'AbbaDude');
insert into Footballer(id,teamID, name) values (51,2, 'BravoDude');
insert into Footballer(id,teamID, name) values (52,1, 'Charlie');
insert into Footballer(id,teamID, name) values (53,12, 'Dude');
insert into Footballer(id,teamID, name) values (54,12, 'Elfonso');
insert into Footballer(id,teamID, name) values (55,1, 'Fix');
select t.id, t.attacker, t.midfield, t.defender, f.name from Team t
left join Footballer f on f.teamID = t.id页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71480448
复制相关文章
相似问题
腾讯云开发者
