关于在SQL查询中新增Old Job、New Job、From Date三列的技术问询

Absolutely, this is totally achievable—you just need to use window functions to pull the "old" and "new" job titles from the same job_title column, since they’re based on the chronological order of each employee’s role history.

Here’s How to Implement It #

The core idea is grouping by each employee, sorting their job records by the valid_from date, then using LAG() or LEAD() to grab adjacent job titles based on your definition of "old" vs "new":

Scenario 1: Old Job = Previous Role, New Job = Current Role

If "Old Job" refers to the role the employee held before their current one, use LAG() to fetch the prior job title in the sequence:

SELECT
    emp_no AS "Emp No",
    emp_name AS "Emp Name",
    -- Pull the job title from the employee's previous role
    LAG(job_title) OVER (
        PARTITION BY emp_no 
        ORDER BY valid_from ASC
    ) AS "Old Job",
    -- Current role is treated as the "New Job"
    job_title AS "New Job",
    valid_from AS "From Date"
FROM tableA;

• PARTITION BY emp_no: Makes sure we only compare job titles for the same employee (no cross-employee mixing).
• ORDER BY valid_from ASC: Ensures jobs are sorted by start date to maintain chronological order.
• Edge case note: For an employee’s very first role, Old Job will return NULL (since there’s no prior role). Use COALESCE(LAG(...), 'No Previous Job') if you want to replace that with a friendlier value.

Scenario 2: Old Job = Current Role, New Job = Next Role

If you want "Old Job" to be the current role and "New Job" to be the next role the employee moved into, swap to LEAD() instead:

SELECT
    emp_no AS "Emp No",
    emp_name AS "Emp Name",
    job_title AS "Old Job",
    -- Pull the job title from the employee's next role
    LEAD(job_title) OVER (
        PARTITION BY emp_no 
        ORDER BY valid_from ASC
    ) AS "New Job",
    valid_from AS "From Date"
FROM tableA;

Here, LEAD() grabs the next job in the sequence. The employee’s final role will have NULL for New Job (since there’s no subsequent role).

Final Thoughts #

As long as tableA contains historical job records for each employee (with valid_from marking when each role started), this approach works flawlessly. Just pick LAG() or LEAD() based on exactly how you define "old" and "new" roles.

内容的提问来源于stack exchange，提问作者Yasir