我是SQL的新手，正在尝试获取联接和引用的句柄。
数据库如下所示：
玩家们
id | name
----+-------
1 | john
2 | tim
3 | frank
4 | tony
5 | mark
火柴
id | winner | loser
----+--------+-------
1 | 2 | 4
2 | 3 | 4
3 | 4 | 5
4 | 1 | 4
我想得到这个结果：
id | winner | loser
----+--------+-------
1 | tim | tony
2 | frank | tony
3 | tony | mark
4 | john | tony
到目前为止，我可以做到这一点：
select matches.id, players.name as winner, players.name as loser
from matches, players
where winner = players.id;
但是，如果我添加and loser = players.id，它不返回任何行。
我也尝试过这个：
select matches.id, players.name as winner, players.name as loser
from matches
join players on matches.winner = players.id
join players on matches.loser = players.id;
产生ERROR: table name "players" specified more than once。
我已经在Google上搜索了一个小时，可以找到很多案件，这些案件几乎可以满足我的要求，但是我仍然不知道如何用同一张表中的两个名称替换两个ID。
您几乎在那里：
select matches.id, w.name as winner, l.name as loser
from matches
join players w on matches.winner = w.id
join players l on matches.loser = l.id;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句