项目员工II

Table: Project

  1. +-------------+---------+
  2. | Column Name | Type    |
  3. +-------------+---------+
  4. | project_id  | int     |
  5. | employee_id | int     |
  6. +-------------+---------+
  7. 主键为 (project_id, employee_id)。
  8. employee_id 是员工表 Employee 表的外键。

Table: Employee

  1. +------------------+---------+
  2. | Column Name      | Type    |
  3. +------------------+---------+
  4. | employee_id      | int     |
  5. | name             | varchar |
  6. | experience_years | int     |
  7. +------------------+---------+
  8. 主键是 employee_id。

编写一个SQL查询，报告所有雇员最多的项目。
查询结果格式如下所示：

  1. Project table:
  2. +-------------+-------------+
  3. | project_id  | employee_id |
  4. +-------------+-------------+
  5. | 1           | 1           |
  6. | 1           | 2           |
  7. | 1           | 3           |
  8. | 2           | 1           |
  9. | 2           | 4           |
  10. +-------------+-------------+
  12. Employee table:
  13. +-------------+--------+------------------+
  14. | employee_id | name   | experience_years |
  15. +-------------+--------+------------------+
  16. | 1           | Khaled | 3                |
  17. | 2           | Ali    | 2                |
  18. | 3           | John   | 1                |
  19. | 4           | Doe    | 2                |
  20. +-------------+--------+------------------+
  22. Result table:
  23. +-------------+
  24. | project_id  |
  25. +-------------+
  26. | 1           |
  27. +-------------+
  28. 第一个项目有3名员工，第二个项目有2名员工。
  1. select 
  2. project_id
  3. from 
  4. (
  5. select 
  6. project_id,
  7. count(employee_id) cnt 
  8. from 
  9. Project 
  10. group by 
  11. project_id  
  12. ) t 
  13. where 
  14. t.cnt = max(cnt)
  1. Invalid use of group function

where后面是不能跟聚合函数的。

聚合函数要使用的话，有一个前提，那就是是必须要有结果集，根据Mysql的执行步骤，当程序执行到where的时候，mysql是没有结果集的，所以聚合函数不能用在where后面。但是聚合函数为什么就可以放在having后面呢，原因是使用having，前面一定要有分组，而分组的时候就已经有结果了，所以就有结果集了，满足聚合函数前面一定要有结果集的要求。

  1. select 
  2. project_id
  3. from 
  4. Project 
  5. group by 
  6. project_id  
  7. having 
  8. count(employee_id) = max (count(employee_id) )
  1. FUNCTION test.max does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

子查询的解决方案

  1. project_id
  2. from 
  3. Project 
  4. group by 
  5. project_id  
  6. having 
  7. count(employee_id) =
  8. (
  9.     select 
  10.     count(employee_id)
  11.     from 
  12.     Project
  13.     group by 
  14.     project_id  
  15.     order by 
  16.     count(employee_id )
  17.     desc limit 1
  18. )

all的解决方案：

  1. select 
  2. project_id
  3. from 
  4. Project 
  5. group by 
  6. project_id  
  7. having 
  8. count(employee_id) >= all(select count(employee_id) from  Project group by project_id  )