题64:
编写一个SQL查询,报告所有雇员最多的项目。
其中:
- Project表:主键为 (project_id, employee_id),employee_id 是员工表 Employee 表的外键;
- Employee表:主键是 employee_id。
解题思路:
(1)先查询最多雇员数;
(2)然后根据雇员数 = 最多雇员数的项目查询出项目即可。
select project_id
from Project
group by project_id
having count(*) = (
select count(*) as jishu
from Project
group by project_id
order by jishu desc
limit 0,1 --查出最多雇员数
);