力扣1076(MySQL)-员工项目Ⅱ(简单)
题目:
编写一个SQL查询,报告所有雇员最多的项目。
查询结果格式如下所示:
解题思路:
方法一:将两个表联结,以project_id进行分组,统计员工数降序排序,然后筛选出第一条数据。
1 select project_id 2 from project a 3 join employee b 4 on a.employee_id = b.employee_id 5 group by project_id 6 order by count(a.employee_id) desc 7 limit 1
方法二:子查询,先查询出最大数,然后在用主查询的统计数等于子查询的最大数。
1 select project_id 2 from project_1075 a 3 group by project_id 4 having count(employee_id) = ( 5 select count(*) as cout 6 from project_1075 7 group by project_id 8 order by cout desc 9 limit 1 10 )