mysql 5.7 order by group by 同时使用

一.表结构

员工OID,部门OID,员工名称

二.需求描述

查询每个部门中员工OID最大的员工信息

预期结果:

mysql老版本中解决方式:

SELECT * FROM (SELECT * FROM employee ORDER BY employee_oid DESC) t GROUP BY department_oid;

执行结果如下(与预期不符):

三.解决方案

1.方案一:

select * from (SELECT * FROM employee order by employee_oid desc limit 0,100) t group by department_oid;

2.方案二:

SELECT * FROM employee WHERE employee_oid IN (SELECT MAX(employee_oid) FROM employee GROUP BY department_oid) ; 根据employee_oid遍历查询

3.方案三:

SELECT 

   department_oid,

   SUBSTRING_INDEX(GROUP_CONCAT(employee_name ORDER BY employee_oid DESC),',',1) AS employee_name,
   SUBSTRING_INDEX(GROUP_CONCAT(employee_oid ORDER BY employee_oid DESC),',',1) AS employee_oid 

FROM employee 

GROUP BY department_oid ;