左聯接顯示聯接表中的多個值

有這個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 等），我想查看 table 中的名稱Footballer。

id  attacker    midfield    defender    
1   AbbaDude    BravoDude   BravoDude   
2   AbbaDude    NULL        Fix         
12  AbbaDude    Dude        NULL        

uj5u.com熱心網友回復：

您可以使用此查詢：

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.defender

uj5u.com熱心網友回復：

您需要加入表 Footballer 三次，因此我們使用別名來知道我們指的是哪個加入。我喜歡使用一個或兩個字母別名，但一個字母可以讓你知道你指的是什么。
這里 a 代表前鋒，m 代表中場，d 代表后衛。

select
  t.id,
  a.name attacker,
  m.name midfield,
  d.name defender
from Team t
left join Footballer a on t.attacker = a.id
left join Footballer m on t.midfield = m.id
left join Footballer d on t.defender = d.id

編號 | 攻擊者 | 中場 | 后衛
-: | :------- | :-------- | :--------
 1 | 阿巴杜德 | 布拉沃杜德 | BravoDude
 2 | 阿巴杜德 | 空      | 使固定      
12 | 阿巴杜德 | 老兄 | 空值     

db<>在這里擺弄

uj5u.com熱心網友回復：

您需要 Footballer 表中的 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

標籤：mysql sql 加入 左连接

上一篇：如何顯示包含“Twist”作為五項之一的收據編號及其總價？

下一篇：如何在MySQL中使用子字串決議文本和數字？