MySQL 为什么此 SQL 代码会给出错误 1066（非唯一表/别名：'user'）？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1435177/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does this SQL code give error 1066 (Not unique table/alias: 'user')?
提问by neobeacon
This is my table structure:
这是我的表结构：
The error message is:
错误信息是：
#1066 - Not unique table/alias: 'user'
#1066 - 不是唯一的表/别名：'user'
The following is my code.
以下是我的代码。
SELECT article.* , section.title, category.title, user.name, user.name
FROM article
INNER JOIN section ON article.section_id = section.id
INNER JOIN category ON article.category_id = category.id
INNER JOIN user ON article.author_id = user.id
LEFT JOIN user ON article.modified_by = user.id
WHERE article.id = '1'
回答by codeulike
You need to give the user table an alias the second time you join to it
您需要在第二次加入用户表时为其指定别名
e.g.
例如
SELECT article . * , section.title, category.title, user.name, u2.name
FROM article
INNER JOIN section ON article.section_id = section.id
INNER JOIN category ON article.category_id = category.id
INNER JOIN user ON article.author_id = user.id
LEFT JOIN user u2 ON article.modified_by = u2.id
WHERE article.id = '1'
回答by OMG Ponies
Your error is because you have:
你的错误是因为你有：
JOIN user ON article.author_id = user.id
LEFT JOIN user ON article.modified_by = user.id
You have two instances of the same table, but the database can't determine which is which. To fix this, you need to use table aliases:
您有同一个表的两个实例，但数据库无法确定哪个是哪个。要解决此问题，您需要使用表别名：
JOIN USER u ON article.author_id = u.id
LEFT JOIN USER u2 ON article.modified_by = u2.id
It's good habit to always alias your tables, unless you like writing the full table name all the time when you don't have situations like these.
总是给你的表取别名是一个好习惯，除非你喜欢在没有这样的情况时一直写完整的表名。
The next issues to address will be:
接下来要解决的问题是：
SELECT article.* , section.title, category.title, user.name, user.name
1) Never use SELECT *- always spell out the columns you want, even if it is the entire table. Read this SO Question to understand why.
1) 从不使用SELECT *- 总是拼出你想要的列，即使它是整个表格。 阅读此 SO 问题以了解原因。
2) You'll get ambiguous column errors relating to the user.namecolumns because again, the database can't tell which table instance to pull data from. Using table aliases fixes the issue:
2) 你会得到与列相关的不明确的列错误，user.name因为同样，数据库无法判断从哪个表实例中提取数据。使用表别名解决了这个问题：
SELECT article.* , section.title, category.title, u.name, u2.name
回答by Steve Kass
You have mentioned "user" twice in your FROM clause. You must provide a table alias to at least one mention so each mention of user. can be pinned to one or the other instance:
您在 FROM 子句中两次提到“用户”。您必须为至少一个提及项提供表别名，以便每次提及用户。可以固定到一个或另一个实例：
FROM article INNER JOIN section
ON article.section_id = section.id
INNER JOIN category ON article.category_id = category.id
INNER JOIN user **AS user1** ON article.author\_id = **user1**.id
LEFT JOIN user **AS user2** ON article.modified\_by = **user2**.id
WHERE article.id = '1'
(You may need something different - I guessed which user is which, but the SQL engine won't guess.)
（你可能需要一些不同的东西——我猜到了哪个用户是哪个，但 SQL 引擎不会猜到。）
Also, maybe you only needed one "user". Who knows?
此外，也许您只需要一个“用户”。谁知道？
回答by Anindyo Bose
SELECT art.* , sec.section.title, cat.title, use1.name, use2.name as modifiedby
FROM article art
INNER JOIN section sec ON art.section_id = sec.section.id
INNER JOIN category cat ON art.category_id = cat.id
INNER JOIN user use1 ON art.author_id = use1.id
LEFT JOIN user use2 ON art.modified_by = use2.id
WHERE art.id = '1';
Hope This Might Help
希望这可能会有所帮助
