如何解决 MySQL 中不明确的列名错误？

How can I resolve an ambiguous column name error in MySQL?

提问by Adn

Possible Duplicate:
1052: Column 'id' in field list is ambiguous

可能重复：
1052：字段列表中的列“id”不明确

I have two tables, and I want to connect the tables by sb_id(sb_idis same for two tables). So, I used my query like this:

我有两个表，我想通过sb_id(sb_id两个表相同)连接表。所以，我像这样使用了我的查询：

SELECT Name, 
       class 
  FROM student_info,
       student_class 
 WHERE id = 1 
   AND student_info.sb_id = student_class.sb_id;

And it's showed error:

它显示错误：

1052: 'id' in where clause is ambiguous

1052: where 子句中的“id”不明确

Another thing,I want to show just a single result,by using "JOIN".

另一件事，我想通过使用“JOIN”只显示一个结果。

回答by Nicole

That means that both tables have idand you need to prefix it with the table that it appears in (if it is the same in both, either will do).

这意味着两个表都有id并且您需要用它出现的表作为前缀（如果两者相同，则两者都可以）。

SELECT
  name,
  class
FROM student_info, student_class
WHERE
  student_info.id=1
  AND student_info.sb_id=student_class.sb_id;

This will return a single result, as you desire, as long as there is exactly one record with student_info.id=1and a student_classrecord with a matching sb_id. The result is the same as if you used INNER JOIN— in other words, both records must exist and are joined together.

这将返回一个结果，你的愿望，只要有一个记录与student_info.id=1和student_class具有匹配的记录sb_id。结果与您使用的结果相同INNER JOIN- 换句话说，两个记录都必须存在并且连接在一起。

The corresponding INNER JOINsyntax would look like this:

相应的INNER JOIN语法如下所示：

SELECT
  name,
  class,
FROM student_info
INNER JOIN student_class ON student_info.sb_id = student_class.sb_id
WHERE student_info.id = 1

回答by jeroen

The problem is with your WHEREstatment, you need to use:

问题在于您的WHERE声明，您需要使用：

where student_info.id=1

or:

或者：

where student_class.id=1

回答by DGM

SELECT Name,class from student_info join student_class on student_info.sb_id=student_class.sb_id where student_info.id=1;

回答by simnom

SELECT student_info.Name,student_info.class from student_info inner join student_class on student_info.sb_id=student_class.sb_id where student_info.id=1;

回答by Muhammad Usama

this means id column is present in both tables, just replace the id with student_info.id or student_class.id which ever is intended

这意味着两个表中都存在 id 列，只需将 id 替换为 student_info.id 或 student_class.id 即可

回答by JohnFx

Prefix the ID column in the where clause with the table name. It is confused because ID is in both tables and it doesn't know which one you mean.

使用表名作为 where 子句中的 ID 列的前缀。它很困惑，因为 ID 在两个表中，它不知道您指的是哪一个。

WHERE student_info.id=1

or

或者

WHERE student_class.id=1

depending on which one you meant when you wrote the query.

取决于您在编写查询时的意思。

回答by a1ex07

It's because both of your tables have idfield, so you need to specify which idyou want to use (you may or may not use table aliases, I prefer to use, but it's really up to you). Also, using JOINfor joining tables is much better practice than putting everything in FROM. I'd rewrite your query to

这是因为你的两个表都有id字段，所以你需要指定id你想使用哪个（你可能会或可能不会使用表别名，我更喜欢使用，但这真的取决于你）。此外，JOIN用于连接表比将所有内容放入FROM. 我会将您的查询重写为

SELECT Name,class 
from student_info si
INNER JOIN student_class sc ON (sc.sb_id = si.sb_id)
WHERE si.id = 1  // or sc.id =1, whatever makes sense

回答by CatchingMonkey

SELECT Name,class 
FROM student_info,student_class 
WHERE student_info.sb_id=1 AND student_info.sb_id=student_class.sb_id;

回答by ethrbunny

SELECT a.Name,a.class from student_info as a join student_class as b on a.sb_id = b.sb_id where id=1;

SELECT a.Name,a.class from student_info as a join student_class as b on a.sb_id = b.sb_id where id=1;

回答by Jeff Lambert

Should your where id=1be where student_info.sb_id = 1?

你的应该where id=1是where student_info.sb_id = 1？

You could change it to a join like this:

您可以将其更改为这样的连接：

SELECT Name,class FROM student_info INNER JOIN student_class ON student_info.sb_id = student_class.sb_id WHERE student_info.sb_id=1

SELECT Name,class FROM student_info INNER JOIN student_class ON student_info.sb_id = student_class.sb_id WHERE student_info.sb_id=1