MySQL 1052：字段列表中的列“id”不明确
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6638520/
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
1052: Column 'id' in field list is ambiguous
提问by Wern Ancheta
I have 2 tables. tbl_namesand tbl_sectionwhich has both the idfield in them. How do I go about selecting the idfield, because I always get this error:
我有2张桌子。tbl_names并且tbl_section这两个id领域都在其中。我该如何选择该id字段，因为我总是收到此错误：
1052: Column 'id' in field list is ambiguous
Here's my query:
这是我的查询：
SELECT id, name, section
FROM tbl_names, tbl_section
WHERE tbl_names.id = tbl_section.id
I could just select all the fields and avoid the error. But that would be a waste in performance. What should I do?
我可以选择所有字段并避免错误。但这会浪费性能。我该怎么办？
回答by OMG Ponies
SQL supports qualifying a column by prefixing the reference with either the full table name:
SQL 支持通过使用完整表名作为引用前缀来限定列：
SELECT tbl_names.id, tbl_section.id, name, section
FROM tbl_names
JOIN tbl_section ON tbl_section.id = tbl_names.id
...or a table alias:
...或表别名：
SELECT n.id, s.id, n.name, s.section
FROM tbl_names n
JOIN tbl_section s ON s.id = n.id
The table alias is the recommended approach -- why type more than you have to?
表别名是推荐的方法——为什么要输入更多？
Why Do These Queries Look Different?
为什么这些查询看起来不同？
Secondly, my answers use ANSI-92 JOIN syntax (yours is ANSI-89). While they perform the same, ANSI-89 syntax does not support OUTER joins (RIGHT, LEFT, FULL). ANSI-89 syntax should be considered deprecated, there are many on SO who will not vote for ANSI-89 syntax to reinforce that. For more information, see this question.
其次，我的回答使用 ANSI-92 JOIN 语法（你的是 ANSI-89）。虽然它们执行相同的操作，但 ANSI-89 语法不支持 OUTER 连接（RIGHT、LEFT、FULL）。ANSI-89 语法应该被视为已弃用，SO 上有很多人不会投票支持 ANSI-89 语法来加强这一点。有关更多信息，请参阅此问题。
回答by Taryn
In your SELECTstatement you need to preface your id with the table you want to choose it from.
在您的SELECT声明中，您需要在您的 id 前面加上您要从中选择它的表。
SELECT tbl_names.id, name, section
FROM tbl_names
INNER JOIN tbl_section
ON tbl_names.id = tbl_section.id
OR
或者
SELECT tbl_section.id, name, section
FROM tbl_names
INNER JOIN tbl_section
ON tbl_names.id = tbl_section.id
回答by halfdan
You would do that by providing a fully qualified name, e.g.:
您可以通过提供一个完全限定的名称来做到这一点，例如：
SELECT tbl_names.id as id, name, section FROM tbl_names, tbl_section WHERE tbl_names.id = tbl_section.id
Which would give you the id of tbl_names
这会给你 tbl_names 的 id
回答by Bryan Legend
What you are probably really wanting to do here is use the union operator like this:
您可能真正想做的是使用联合运算符，如下所示：
(select ID from Logo where AccountID = 1 and Rendered = 'True')
union
(select ID from Design where AccountID = 1 and Rendered = 'True')
order by ID limit 0, 51
Here's the docs for it https://dev.mysql.com/doc/refman/5.0/en/union.html
回答by M.J
Already there are lots of answers to your question, You can do it like this also. You can give your table an alias name and use that in the select query like this:
你的问题已经有很多答案了，你也可以这样做。你可以给你的表一个别名，并在选择查询中使用它，如下所示：
SELECT a.id, b.id, name, section
FROM tbl_names as a
LEFT JOIN tbl_section as b ON a.id = b.id;
回答by vog
The simplest solution is a join with USINGinstead of ON. That way, the database "knows" that both idcolumns are actually the same, and won't nitpick on that:
最简单的解决方案是连接 withUSING而不是ON。这样，数据库“知道”两id列实际上是相同的，并且不会对此挑剔：
SELECT id, name, section
FROM tbl_names
JOIN tbl_section USING (id)
If idis the only common column name in tbl_namesand tbl_section, you can even use a NATURAL JOIN:
ifid是tbl_namesand 中唯一的公共列名tbl_section，您甚至可以使用 a NATURAL JOIN：
SELECT id, name, section
FROM tbl_names
NATURAL JOIN tbl_section
回答by Festole
If the format of the id's in the two table varies then you want to join them, as such you can select to use an id from one-main table, say if you have table_customesand table_orders, and tha id for orders is like "101","102"..."110", just use one for customers
如果两个表中 id 的格式不同，那么您想加入它们，因此您可以选择使用一个主表中的 id，例如，如果您有table_customesand table_orders，订单的 id 就像“ 101”， “ 102”……“ 110”，只给客户用一个
select customers.id, name, amount, date from customers.orders;
回答by nikunj
SELECT tbl_names.id, tbl_names.name, tbl_names.section
FROM tbl_names, tbl_section
WHERE tbl_names.id = tbl_section.id
