英文:
column reference "group_id" is ambiguous when join
问题
我在连接两个表时遇到了一些问题：
Groups（分组）：group_id（分组ID），group_name（分组名称）
Students（学生）：student_id（学生ID），group_id（分组ID），first_name（名），last_name（姓）
我想要计算每个分组有多少名学生，然后输出分组名称。但是当我写成这样时：
public String findGroupsByStudentsNumber(int expectedStudentsNumber) {
return "SELECT group_id, COUNT(student_id) AS Number_of_students " +
"FROM students " +
"LEFT JOIN groups ON group_id = groups.group_id " +
"GROUP BY group_id " +
"HAVING Number_of_students <= " + expectedStudentsNumber +
" ORDER BY group_id";
}
出现了错误提示：“列引用"group_id"不明确”。
英文:
I have some troubles with join two tables:
Groups: group_id, group_name
Students: student_id, group_id, first_name, last_name
I wanna count how many students have each group and then output group name, but when i write like this:
public String findGroupsByStudentsNumber(int expectedStudentsNumber) {
return " SELECT group_id, COUNT(student_id) AS Number_of_students " +
"FROM students " +
"LEFT JOIN groups ON group_id = groups.group_id " +
"GROUP BY group_id " +
"HAVING Number_of_students <=" + expectedStudentsNumber +
" ORDER BY group_id";
}
there is an error "column reference "group_id" is ambiguous".
答案1
得分: 1
因为两个表格都有一个 group_id 列，所以在查询中不能使用简单的 group_id，而不指定你要引用哪个表格的列。以下是可能的一种写法：
SELECT students.group_id, COUNT(student_id) AS 学生人数
FROM students
LEFT JOIN groups ON students.group_id = groups.group_id
GROUP BY students.group_id
HAVING 学生人数 <= X
ORDER BY students.group_id
英文:
Since both tables have a group_id column, you can't have a plain group_id in your query without specifying which one you're referring to. This is one way it could look:
SELECT students.group_id, COUNT(student_id) AS Number_of_students
FROM students
LEFT JOIN groups ON students.group_id = groups.group_id
GROUP BY students.group_id
HAVING Number_of_students <= X
ORDER BY students.group_id
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论