ROW_NUMBER() 方法只支持mysql8.0以上版本
可以获取最小值
SELECT
e.depart_id,
e.NAME AS employee_name,
e.depart_name,
e.entry_time,
ROW_NUMBER() OVER ( PARTITION BY e.depart_id ORDER BY e.entry_time ) AS row_num
FROM
employee e
JOIN information_summary inf ON e.id = inf.emp_id
GROUP BY
e.depart_id,
e.NAME,
e.depart_name
查询结果
获取每个部门最大值 和 最小值 信息
查询结果为这个 如果获取最小值 这 将 row_num 等于 一 即可
查询结果为这个 如果获取最大值 这 将 e.entry_time 后面 加 一个 desc 并等于 一 即可
最终mysql
SELECT
depart_id,
employee_name,
depart_name,
entry_time
FROM
(
SELECT
e.depart_id,
e.NAME AS employee_name,
e.depart_name,
e.entry_time,
ROW_NUMBER() OVER ( PARTITION BY e.depart_id ORDER BY e.entry_time ) AS row_num
FROM
employee e
JOIN information_summary inf ON e.id = inf.emp_id
GROUP BY
e.depart_id,
e.NAME,
e.depart_name
) AS ranked_sales
WHERE
row_num = 1
获取每个部门入职时间最早的人员信息