员工部门数据转换需求：生成当前/过往部门编号映射表

Got it, let's work through this problem together. You have an employee tenure table tracking each department stint with EMP_NUM, start/end dates, and department numbers, and you need to collapse that into a single row per employee showing their current department plus all previous ones. Here's a solid SQL-based approach that works across most modern databases:

Step 1: Identify Current vs. Previous Departments #

First, we need to rank each employee's tenure records to distinguish their current (most recent) department from past ones. For "current" we'll prioritize records with no end date (active employment) or the latest end date.

Step 2: Aggregate into the Desired Format #

Once we have rankings, we can pivot the data to pull the current department and aggregate all past departments into a single field.

Example SQL Code (Standard SQL) #

Assume your table is named employee_tenure with columns EMP_NUM, start_date, end_date, dept_num:

WITH ranked_tenure AS (
    SELECT 
        EMP_NUM,
        dept_num,
        -- Rank records so the current/latest department is #1
        ROW_NUMBER() OVER (
            PARTITION BY EMP_NUM 
            ORDER BY COALESCE(end_date, '9999-12-31') DESC
        ) AS tenure_rank
    FROM employee_tenure
)
SELECT 
    EMP_NUM,
    -- Grab the current department (rank 1)
    MAX(CASE WHEN tenure_rank = 1 THEN dept_num END) AS Current_Department_number,
    -- Aggregate all previous departments into a comma-separated list
    STRING_AGG(CASE WHEN tenure_rank > 1 THEN dept_num END, ', ') AS Previous_Department_number
FROM ranked_tenure
GROUP BY EMP_NUM;

Breakdown of the Code #

• CTE ranked_tenure: Uses ROW_NUMBER() to assign a rank to each employee's tenure. COALESCE(end_date, '9999-12-31') ensures active records (no end date) are treated as the most recent.
• Main Query:
  • MAX(CASE...) picks out the current department (since each employee only has one rank=1 record).
  • STRING_AGG combines all non-current departments into a readable list. If an employee has no past departments, this field will return NULL (you can use COALESCE(STRING_AGG(...), 'No previous departments') to make it friendlier).

Adjustments for Different SQL Dialects #

• MySQL: Replace STRING_AGG with GROUP_CONCAT:

  GROUP_CONCAT(CASE WHEN tenure_rank > 1 THEN dept_num END SEPARATOR ', ') AS Previous_Department_number
  

• Oracle: Use LISTAGG instead:

  LISTAGG(CASE WHEN tenure_rank > 1 THEN dept_num END, ', ') WITHIN GROUP (ORDER BY start_date) AS Previous_Department_number
  

• SQL Server: STRING_AGG works, but if you need to sort past departments by date, add WITHIN GROUP (ORDER BY start_date DESC):

  STRING_AGG(CASE WHEN tenure_rank > 1 THEN dept_num END, ', ') WITHIN GROUP (ORDER BY start_date DESC) AS Previous_Department_number
  

Example Output #

For your sample employee 102:

If employee 102 is still in department 20 (no end date), the output remains the same because the COALESCE trick ensures the active record is ranked #1.

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