我正在尝试使用不同的别名获取记录,但它们是相关的。
我使用的服务器是WAMP。我已经成功地将我的项目/脚本连接到 PhpMyAdmin 数据库,并且可以使用一些查询在我的 PHP 文件上看到一些记录并成功插入。
但是,当我尝试使用不同的别名获取记录时,我收到一条错误消息:">MySQL 说:#1066 - 不唯一表/别名:'团队'">?
您能否解释一下发生了什么,因为在数据库中,当我检查这些记录的 ID 时,它们显示它们已连接/从另一个表中提取记录。
以下是我的MySQL查询,我尝试了几次更改,但每次尝试后都没有成功:
SELECT DISTINCT fixtures.fixture_id, fixtures.fixture_date,
fixtures.fixture_time, fixtures.home_teamID,
fixtures.away_teamID, fixtures.comp_id
From fixtures
JOIN teams ON fixtures.home_teamID = teams.team_id
JOIN teams ON fixtures.away_teamID = teams.team_id
JOIN competitions ON fixtures.comp_id = competitions.comp_id
ORDER BY fixture.id ASC
来自查询的错误消息指出:
SELECT DISTINCT fixtures.fixture_id, fixtures.fixture_date,
fixtures.fixture_time, fixtures.home_teamID,
fixtures.away_teamID, fixtures.comp_id
From fixtures
JOIN teams ON fixtures.home_teamID = teams.team_id
JOIN teams ON fixtures.away_teamID = teams.team_id
JOIN competitions ON fixtures.comp_id = competitions.comp_id
ORDER BY fixture.id ASC
LIMIT 0, 25
请向我解释我做错了什么,请注意我是使用PHP和MySQL的这个平台的新手。提前谢谢。
如果你想在同一个表中加入两个ime,你需要别名
SELECT DISTINCT fixtures.fixture_id
, fixtures.fixture_date
, fixtures.fixture_time
, fixtures.home_teamID
, fixtures.away_teamID
, fixtures.comp_id
, a.team_name home_team
, b.team_name away_team
From fixtures
JOIN teams a ON fixtures.home_teamID = a.team_id
JOIN teams b ON fixtures.away_teamID = b.team_id
JOIN competitions ON fixtures.comp_id = competitions.comp_id
ORDER BY fixture.id ASC
在此示例中,A 和 B 是同一表的别名,以两种方式使用,一种用于home_team,一种用于way_team
对于 @Tim Biegeleisen 建议的更紧凑的代码,您可以使用别名作为
SELECT DISTINCT f.fixture_id
, f.fixture_date
, f.fixture_time
, f.home_teamID
, f.away_teamID
, f.comp_id
, a.team_name home_team
, b.team_name away_team
From fixtures f
JOIN teams a ON f.home_teamID = a.team_id
JOIN teams b ON f.away_teamID = b.team_id
JOIN competitions c ON f.comp_id = c.comp_id
ORDER BY f.id ASC
你加入了两次"团队",所以当你提到"团队"时,它不知道你指的是home_teamID加入的第一个团队还是away_teamID加入的第二个团队。
您可以通过为每个联接指定不同的名称来解决此问题,如下所示:
SELECT DISTINCT fixtures.fixture_id, fixtures.fixture_date,
fixtures.fixture_time, fixtures.home_teamID,
fixtures.away_teamID, fixtures.comp_id
From fixtures
JOIN teams AS hometeams ON fixtures.home_teamID = hometeams.team_id
JOIN teams AS awayteams ON fixtures.away_teamID = awayteams.team_id
JOIN competitions ON fixtures.comp_id = competitions.comp_id
ORDER BY fixture.id ASC