Mysql-group先分组后排序

1、先分组后排序、考虑并列第一

 

#错误写法
SELECT
    b.depName AS departId,
    a.`name` AS employee,
    max(salary) AS salary
FROM
    cs_employee a
LEFT JOIN cs_depart b ON a.departId = b.id
GROUP BY
    a.departId


#正确写法（先取出部门不为空的工资最大值，然后关联查询）
SELECT
    a.max AS salary,
    b.`name` AS employee,
    c.depName AS departId
FROM
    (SELECT MAX(salary) max FROM cs_employee t WHERE t.departId is NOT NULL GROUP BY t.departId ) a 
LEFT JOIN cs_employee b ON  a.max = b.salary
LEFT JOIN cs_depart c ON b.departId = c.id
ORDER BY b.departId