我有下表
select * from department;
+--------------+--------------+--------+------------+---------------+
| departmentid | name | budget | startdate | administrator |
+==============+==============+========+============+===============+
| 101 | Computer Sci | 1000 | 2010-12-26 | XYZ |
| 102 | ECE | 500 | 2015-02-15 | ABC |
| 103 | EEE | 1500 | 2016-08-25 | PQR |
| 104 | Mech | 2500 | 2017-08-22 | LMN |
+--------------+--------------+--------+------------+---------------+
select * from course;
+----------+-------------------+---------+--------------+
| courseid | title | credits | departmentid |
+==========+===================+=========+==============+
| 1001 | Data structures | 12 | 101 |
| 1002 | Algorithms | 12 | 101 |
| 1003 | Graphics | 20 | 101 |
| 2001 | DSP | 20 | 102 |
| 2002 | Matlab | 20 | 102 |
| 2003 | Maths | 10 | 102 |
| 3001 | CAD | 10 | 104 |
| 4001 | Power electronics | 10 | 103 |
| 4002 | Semi conductors | 20 | 103 |
+----------+-------------------+---------+--------------+
select * from student_grade;
+--------------+----------+----------+-------+
| enrollmentid | courseid | personid | grade |
+==============+==========+==========+=======+
| 1 | 1001 | 1 | A |
| 2 | 1002 | 1 | B |
| 3 | 1003 | 1 | A |
| 4 | 3001 | 3 | A |
| 5 | 3001 | 2 | B |
| 6 | 4001 | 4 | A |
| 7 | 4002 | 4 | A |
+--------------+----------+----------+-------+
select * from person;
+----------+----------+-----------+------------+----------------+
| personid | lastname | firstname | hiredate | enrollmentdate |
+==========+==========+===========+============+================+
| 1 | Goudar | Anil | 2016-08-16 | 2016-08-17 |
| 2 | Goudar | Sunil | 2018-09-16 | 2018-09-27 |
| 3 | Dambal | Abhi | 2018-05-07 | 2018-06-17 |
| 4 | Desai | Arun | 2018-05-07 | 2018-06-17 |
| 5 | Xam | Sam | 2018-12-08 | 2018-12-08 |
| 6 | Chatpati | Mangal | 2018-10-10 | 2018-10-08 |
| 9 | Shankar | Dev | 2018-10-10 | 2018-10-08 |
| 10 | Shankar | Mahadev | 2018-08-10 | 2018-08-11 |
+----------+----------+-----------+------------+----------------+
现在，我想获取部门详细信息以及该部门的学生人数。
这是我的查询
select d.departmentid, d.name, sg.personid from department d inner join course c on c.departmentid = d.departmentid inner join student_grade sg on sg.courseid = c.courseid;
+--------------+--------------+----------+
| departmentid | name | personid |
+==============+==============+==========+
| 101 | Computer Sci | 1 |
| 101 | Computer Sci | 1 |
| 101 | Computer Sci | 1 |
| 104 | Mech | 3 |
| 104 | Mech | 2 |
| 103 | EEE | 4 |
| 103 | EEE | 4 |
+--------------+--------------+----------+
但是我想像group by子句那样获取每个部门的count（distinct（personid））。但是我收到以下查询错误。
select d.departmentid, d.name, count(distinct(sg.personid)) from department d inner join course c on c.departmentid = d.departmentid inner join student_grade sg on sg.courseid = c.courseid;
Cannot use non GROUP BY column 'departmentid' in query results without an aggregate function
请帮我，我要去哪里错了。
最佳答案
使用汇总功能时必须使用分组依据
select d.departmentid, d.name,
count(distinct(sg.personid))
from department d inner join course c on c.departmentid = d.departmentid inner join student_grade sg on sg.courseid = c.courseid;
group by d.departmentid, d.name
关于mysql - sql内部联接超过2个表和聚合函数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52232011/