sql中all的用法

编写一个SQL查询，报告所有雇员最多的项目。

                   Table: Employee                         Table: Project                               

当最大值有多行时：（适用于最大值有一行和多行的情况）

select project_id from Project
group by project_id
having count(employee_id)>=all(select count(employee_id) from Project group by  project_id);

 

select project_id
from
(select project_id,rank() over (order by count(employee_id) desc) as num
from project
group by project_id) a
where num=1;

当最大值只有一个时：

select P.project_id from Project P left join Employee E
on P.employee_id=E.employee_id  
group by P.project_id
order by count(*) desc
limit 1