>编写查询以查找每个员工第一份工作的最后一天。
使用 HR 架构
TABLE - job_history
employee_id
start_date
end_date`
job_id
department_id
我试过这个。假设日期不重叠,这将起作用 请提出任何其他方式。
select employee_id, min(end_date)
from job_history
group by employee_id;
如果作业日期从不重叠,则查询正常。
如果他们这样做,那么以下MySQL 8.x查询就可以了:
select
employee_id,
end_date
from (
select
employee_id,
start_date,
end_date,
row_number() over(partition by employee_id order by start_date) as rn
from job_history
) x
where rn = 1
带NOT EXISTS
:
select j.employee_id, j.end_date
from job_history j
where not exists (
select 1 from job_history
where employee_id = j.employee_id and start_date < j.start_date
)
或:
select j.employee_id, j.end_date
from job_history j
where j.start_date = (
select min(start_date)
from job_history
where employee_id = j.employee_id
)
如果员工在同一天首先开始 2 个不同的工作,则这些查询将返回两个结束日期,因为没有列可以区分 2。但是你可以得到它们之间的最小结束日期,用
select j.employee_id, min(j.end_date)
并添加:
group by j.employee_id
使用以下查询:
SELECT employee_id,
last_value(end_date)OVER(PARTITION BY
employee_id) AS last_day, first_value(job_id)
OVER(PARTITION BY employee_id) AS first_job
FROM job_history;