联接时列引用"group_id"不明确

Question

我在连接两个表时遇到了一些问题：

组：group_id，group_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";
    }

出现错误“列引用”groupid“不明确”。

Answer 1

由于两个表都有一个group_id列，因此在查询中不能有一个普通的group_id，而不指定您指的是哪一个。这是它的一种外观：

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