mysql求众数

求每个部门工资的众数

思路：先求出每个部门每个工资水平的数量，然后求每个部门每个工资数量的最大值，关联，关联条件是同部门工资出现的数量等于工资出现次数的最大值

select a.DepartmentId, a.salary
from(
    select DepartmentId, salary, count(*) as count
    from Employee
    group by DepartmentId, Salary
    ) as a
join(
select DepartmentId, max(coun) as m_salary
from(
    select DepartmentId, salary, count(*) as coun
    from Employee
    group by DepartmentId, Salary
    ) as a
group by DepartmentId
    ) as b
on a.DepartmentId=b.DepartmentId and a.count=b.m_Salary