如何解决 MySQL 中不明确的列名错误?
可能的重复:
1052:字段列表中的列“id”不明确 < /p>
我有两个表,我想通过 sb_id 连接表(两个表的 sb_id 相同)。所以,我这样使用我的查询:
SELECT Name,
class
FROM student_info,
student_class
WHERE id = 1
AND student_info.sb_id = student_class.sb_id;
它显示错误:
1052:where 子句中的“id”不明确
另一件事,我想通过使用“JOIN”仅显示一个结果。
Possible Duplicate:
1052: Column 'id' in field list is ambiguous
I have two tables, and I want to connect the tables by sb_id
(sb_id
is same for two tables). So, I used my query like this:
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
Another thing,I want to show just a single result,by using "JOIN".
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(10)
您的
where id=1
应该是where Student_info.sb_id = 1
吗?您可以将其更改为如下连接:
SELECT Name,class
来自学生信息
INNER JOIN Student_class ON Student_info.sb_id = Student_class.sb_id
哪里student_info.sb_id=1
Should your
where id=1
bewhere 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
这意味着两个表中都存在 id 列,只需将 id 替换为 Student_info.id 或 Student_class.id 即可
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
在 where 子句中的 ID 列前面加上表名作为前缀。它很困惑,因为 ID 位于两个表中,并且它不知道您指的是哪一个。
或者
取决于您在编写查询时的意思。
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.
or
depending on which one you meant when you wrote the query.
这是因为您的两个表都有
id
字段,因此您需要指定要使用哪个id
(您可以使用表别名,也可以不使用表别名,我更喜欢使用,但这真的取决于你)。此外,使用JOIN
来连接表比将所有内容都放在FROM
中要好得多。我会将您的查询重写为It's because both of your tables have
id
field, so you need to specify whichid
you want to use (you may or may not use table aliases, I prefer to use, but it's really up to you). Also, usingJOIN
for joining tables is much better practice than putting everything inFROM
. I'd rewrite your query to这意味着两个表都有
id
,并且您需要在它前面加上它出现的表的前缀(如果两个表中的id相同,则任何一个都可以)。只要恰好有一条带有
student_info.id=1
的记录和一条带有匹配sb_id 的
。结果与使用 INNER JOIN 的结果相同 - 换句话说,两条记录必须存在并且连接在一起。student_class
记录,这就会根据您的需要返回单个结果相应的 INNER JOIN 语法如下所示:
That means that both tables have
id
and you need to prefix it with the table that it appears in (if it is the same in both, either will do).This will return a single result, as you desire, as long as there is exactly one record with
student_info.id=1
and astudent_class
record with a matchingsb_id
. The result is the same as if you usedINNER JOIN
— in other words, both records must exist and are joined together.The corresponding
INNER JOIN
syntax would look like this:问题在于您的
WHERE
语句,您需要使用:或:
The problem is with your
WHERE
statment, you need to use:or:
从student_info中选择a.Name,a.class作为a,加入student_class作为b
在 a.sb_id = b.sb_id 上
其中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;