我是 SQL 世界的新手。我想用列中的相应条目替换Games.home_team_id
and 。Games.away_team_id
Teams.name
首先,我从初始化一个小数据表开始:
CREATE TABLE Games (id,away_team_id INT,away_team_score INT,home_team_id INT, home_team_score INT); 创建表 插入游戏(id、away_team_id、away_team_score、home_team_id、home_team_score) 价值观 (1,1,1,2,4), (2,1,3,3,2), (3,1,1,4,1), (4,2,0,3,2), (5,2,3,4,1), (6,3,5,4,2) ; 插入 0 6
然后我创建一个参考表的模板
CREATE TABLE Teams (id INT, name VARCHAR(63); 创建表 插入团队(ID、名称) 价值观 (1, 'Oogabooga FC'), (2, 'FC Milawnchair'), (3,“罗恩的足球联队”), (4,“塔城足球俱乐部”) ; 插入 0 4
我想让表格显示为:
| 编号 | away_team_name | away_team_score | home_team_name | home_team_score | -----+----------------+-----------------+--------- ------+-------- | 1 | 奥加布加足球俱乐部 | 1 | FC Milawnchair | 4 | ...
我设法得到一个连接查询来显示Teams.name
该away_team_name
字段中的第一个值JOIN
:
选择 Games.id, Teams.name AS away_team_name, Games.away_team_score, Teams.name 作为 home_team_name, Games.home_team_score 来自游戏 加入团队 Teams.id = Games.away_team_id ; | 编号 | away_team_name | away_team_score | home_team_name | home_team_score | -----+----------------+-----------------+--------- ------+-------- | 1 | 奥加布加足球俱乐部 | 1 | 奥加布加足球俱乐部 | 4 | ...
但是现在当我调用它两次时我被卡住了,因为JOIN
它显示了错误:
选择 Games.id, Teams.name AS away_team_name, Games.away_team_score, Teams.name 作为 home_team_name, Games.home_team_score 来自游戏 加入团队 Teams.id = Games.away_team_id 加入团队 Teams.id = Games.home_team_id ; 错误:多次指定表名“teams”
您如何在连接中两次引用同一个表的同一列的相同引用?
您需要为表的至少一个实例指定别名;最好两者兼而有之。
SELECT
Games.id,
Away.name AS away_team_name,
Games.away_team_score,
Home.name AS home_team_name,
Games.home_team_score
FROM Games
JOIN Teams AS Away ON Away.id = Games.away_team_id
JOIN Teams AS Home ON Home.id = Games.home_team_id
说明:当您两次加入同一个表时,DBMS(在您的情况下为 PostgreSQL)无法识别您在使用其字段时引用的表;解决此问题的方法是为联接表分配别名,就像为列分配别名一样。SELECT
通过这种方式,您可以在,JOIN
和WHERE
语句中指定要引用的连接实例。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句