mysql使用having子句加入

mysql使用having子句加入

这些是表:

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/

10-11 22:25