SQL SQL重复列名错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9260936/
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
SQL Duplicate column name error
提问by Steve
I am trying to find an error in a massive SQL statement (not mine) - I have cut a lot of it out to make it readable - even pared down it still throws the error
我试图在大量 SQL 语句（不是我的）中找到错误 - 我已经删除了很多以使其可读 - 即使减少它仍然会抛出错误
SELECT DISTINCT Profiles.ID
FROM
(select * from Profiles RIGHT JOIN FriendList ON (FriendList.Profile = 15237)
order by LastLoggedIn DESC ) as Profiles
This returns an error
这将返回一个错误
Duplicate column name 'ID'
重复的列名“ID”
I have tested the the last part (select * from Profiles ... order by LastLoggedIn DESC) and it works fine by itself
我已经测试了最后一部分 ( select * from Profiles ... order by LastLoggedIn DESC) 并且它本身可以正常工作
I have tried to troubleshoot by changing column names in the DISTINCT section without any luck.
我试图通过更改 DISTINCT 部分中的列名来进行故障排除，但没有任何运气。
One solution I read was to remove the DISTINCT, but that didn't help.
我读过的一种解决方案是删除 DISTINCT，但这没有帮助。
I just can't see where the duplicate column error can be coming from. Could it be a database integrity problem?
我只是看不到重复列错误的来源。会不会是数据库完整性问题？
Any help much appreciated.
非常感谢任何帮助。
采纳答案by Blorgbeard is out
Your Profileand FriendListtables both have an IDcolumn. Because you say select *, you're getting two columns named IDin the sub-select which is aliased to Profiles, and SQL doesn't know which one Profiles.IDrefers to (note that Profileshere is referring to the alias of the sub-query, not the table of the same name).
你Profile和FriendList表都有一ID列。因为您说select *，您ID在子选择中命名了两列，其别名为Profiles，而 SQL 不知道哪个是Profiles.ID指代（注意，Profiles这里指的是子查询的别名，而不是表同名）。
Since you only need the ID column, you can change it to this:
由于您只需要 ID 列，您可以将其更改为：
SELECT DISTINCT Profiles.ID FROM
( select Profiles.ID from Profiles RIGHT JOIN FriendList ON (FriendList.Profile = 15237)
order by LastLoggedIn DESC ) as Profiles
回答by Tundey
Replace the "select *" with "select col1, col2..." and the error should become apparent (i.e. multiple columns named "ID"). Nothing to do with distinct or database integrity.
将“select *”替换为“select col1, col2...”，错误应该变得明显（即多个名为“ID”的列）。与不同或数据库完整性无关。
回答by peroija
you have a table called Profiles and you are "creating" a temp table called Profiles in your From, that would be my guess as to what is causing the problem. call your temp bananas and try SELECT DISTINCT bananas.ID FROMand see if that works
您有一个名为 Profiles 的表，并且您在 From 中“创建”了一个名为 Profiles 的临时表，这是我对导致问题的原因的猜测。打电话给你的临时香蕉并尝试SELECT DISTINCT bananas.ID FROM看看是否有效
回答by peroija
As the error says, each of the tables that you're joining together has a column named ID. You'll have to specify which IDcolumn you want (Profiles.IDor FriendList.ID) or include IDin the join conditions.
正如错误所说，您连接在一起的每个表都有一个名为ID. 您必须指定ID您想要的列（Profiles.ID或FriendList.ID）或包含ID在联接条件中。
回答by Ned Batchelder
Profiles and FriendList both have an ID column. You are asking to call the entire join "Profiles", and then using Profiles.ID, but SQL doesn't know which ID you mean.
Profiles 和 FriendList 都有一个 ID 列。您要求调用整个联接“Profiles”，然后使用 Profiles.ID，但 SQL 不知道您指的是哪个 ID。
