mysql使用组联接

Question

这是我的桌子

mysql> select * from professor;
    +-------+--------+--------+--------+------+
    | empid | name   | status | salary | age  |
    +-------+--------+--------+--------+------+
    |     1 | Arun   |      1 |   2000 |   23 |
    |     2 | Benoy  |      0 |   3000 |   25 |
    |     3 | Chacko |      1 |   1000 |   36 |
    |     4 | Divin  |      0 |   5000 |   32 |
    |     5 | Edwin  |      1 |   2500 |   55 |
    +-------+--------+--------+--------+------+
    5 rows in set (0.00 sec)

mysql> select * from works;
+----------+-------+---------+
| courseid | empid | classid |
+----------+-------+---------+
|        1 |     1 |      10 |
|        2 |     2 |       9 |
|        3 |     3 |       8 |
|        4 |     4 |      10 |
|        5 |     5 |       9 |
|        6 |     1 |       9 |
|        2 |     3 |      10 |
|        2 |     1 |       7 |
|        4 |     2 |       6 |
|        7 |     5 |       6 |
|        3 |     5 |       2 |
|        2 |     4 |       6 |
|        2 |     5 |       2 |
+----------+-------+---------+
15 rows in set (0.00 sec)

mysql> select * from course;
+----------+------------+--------+
| courseid | coursename | points |
+----------+------------+--------+
|        1 | Maths      |      4 |
|        2 | Science    |      4 |
|        3 | English    |     85 |
|        4 | Social     |      4 |
|        5 | Malayalam  |     99 |
|        6 | Arts       |     40 |
|        7 | Biology    |    100 |
+----------+------------+--------+
7 rows in set (0.00 sec)

问题是：

Return the names of full professors who have taught at least two courses in one Class 

我的问题是：

select professor.name from professor
inner join works
on professor.empid=works.empid
group by works.empid
having count(distinct works.courseid)>=2

我现在得到的输出是：

Arun
Benoy
Chacko
Divin
Edwin

我应该得到‘埃德温’的输出，因为他是唯一一个在同一堂课上教过两门课的人。请帮帮忙

Answer 1

你必须考虑到你的小组是错误的，把它改为这个

   group by classid,works.empid

就像这样，你会在分类里数数课程。