这些是表:
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 |
| 7 | George | 0 | 1500 | 46 |
+-------+--------+--------+--------+------+
6 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 |
| 2 | 4 | 6 |
| 2 | 5 | 2 |
| 4 | 2 | 6 |
+----------+-------+---------+
11 rows in set (0.00 sec)
mysql> select * from course;
+----------+------------+--------+
| courseid | coursename | points |
+----------+------------+--------+
| 1 | Maths | 100 |
| 2 | Science | 80 |
| 3 | English | 85 |
| 4 | Social | 90 |
| 5 | Malayalam | 99 |
| 6 | Arts | 40 |
| 7 | Biology | 100 |
+----------+------------+--------+
7 rows in set (0.00 sec)
问题是 :
返回至少由5种不同的课程教授的课程
雇员
我的查询是:
select course.coursename from course
inner join works
on course.courseid=works.courseid
group by works.empid
having count(empid)>=5;
结果应该是“科学”。但是我得到0个结果。请帮忙。
最佳答案
尝试这个
select course.coursename from course
inner join works
on course.courseid=works.courseid
group by course.coursename
having count(Distinct(works.empid))>=5;
关于mysql - mysql使用having子句加入,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/21474651/