工单数据库需求：横向展示工单操作及状态间天数间隔

Let's break down how to solve this problem—we need to pivot each case's latest 10 unique operations into horizontal columns, plus calculate the day gap between each status and the previous one. Below is a step-by-step SQL solution (using standard SQL, adjust syntax for your specific database if needed):

Step 1: Prepare the Data (Deduplicate & Calculate Differences) #

First, we'll clean up duplicate operations for each case, then calculate the day difference between consecutive statuses, and rank the operations by recency.

WITH deduplicated_ops AS (
    -- Keep only the latest occurrence of each operation per case
    SELECT 
        Case_number,
        Operation_Name,
        MAX(STR_TO_DATE(Date, '%d.%m.%Y')) AS latest_date -- Adjust date conversion for your DB
    FROM your_table_name
    GROUP BY Case_number, Operation_Name
),
ordered_with_diffs AS (
    -- Rank operations by recency (1 = newest) and calculate day gaps from previous status
    SELECT 
        Case_number,
        Operation_Name,
        latest_date,
        ROW_NUMBER() OVER (PARTITION BY Case_number ORDER BY latest_date DESC) AS op_rank,
        DATEDIFF(
            day,
            LAG(latest_date) OVER (PARTITION BY Case_number ORDER BY latest_date ASC),
            latest_date
        ) AS days_since_previous
    FROM deduplicated_ops
),
top_10_latest_ops AS (
    -- Filter to only the 10 newest unique operations per case
    SELECT *
    FROM ordered_with_diffs
    WHERE op_rank <= 10
)

Step 2: Pivot to Horizontal Columns #

We'll use conditional aggregation to turn the ranked operations into horizontal columns (this works across most databases, unlike vendor-specific PIVOT syntax):

SELECT 
    Case_number,
    -- Columns for the newest operation (rank 1)
    MAX(CASE WHEN op_rank = 1 THEN Operation_Name END) AS op1_name,
    MAX(CASE WHEN op_rank = 1 THEN latest_date END) AS op1_date,
    MAX(CASE WHEN op_rank = 1 THEN days_since_previous END) AS op1_days_since_prev,
    -- Columns for the 2nd newest operation
    MAX(CASE WHEN op_rank = 2 THEN Operation_Name END) AS op2_name,
    MAX(CASE WHEN op_rank = 2 THEN latest_date END) AS op2_date,
    MAX(CASE WHEN op_rank = 2 THEN days_since_previous END) AS op2_days_since_prev,
    -- Repeat this pattern up to op10
    MAX(CASE WHEN op_rank = 3 THEN Operation_Name END) AS op3_name,
    MAX(CASE WHEN op_rank = 3 THEN latest_date END) AS op3_date,
    MAX(CASE WHEN op_rank = 3 THEN days_since_previous END) AS op3_days_since_prev,
    MAX(CASE WHEN op_rank = 4 THEN Operation_Name END) AS op4_name,
    MAX(CASE WHEN op_rank = 4 THEN latest_date END) AS op4_date,
    MAX(CASE WHEN op_rank = 4 THEN days_since_previous END) AS op4_days_since_prev,
    MAX(CASE WHEN op_rank = 5 THEN Operation_Name END) AS op5_name,
    MAX(CASE WHEN op_rank = 5 THEN latest_date END) AS op5_date,
    MAX(CASE WHEN op_rank = 5 THEN days_since_previous END) AS op5_days_since_prev,
    MAX(CASE WHEN op_rank = 6 THEN Operation_Name END) AS op6_name,
    MAX(CASE WHEN op_rank = 6 THEN latest_date END) AS op6_date,
    MAX(CASE WHEN op_rank = 6 THEN days_since_previous END) AS op6_days_since_prev,
    MAX(CASE WHEN op_rank = 7 THEN Operation_Name END) AS op7_name,
    MAX(CASE WHEN op_rank = 7 THEN latest_date END) AS op7_date,
    MAX(CASE WHEN op_rank = 7 THEN days_since_previous END) AS op7_days_since_prev,
    MAX(CASE WHEN op_rank = 8 THEN Operation_Name END) AS op8_name,
    MAX(CASE WHEN op_rank = 8 THEN latest_date END) AS op8_date,
    MAX(CASE WHEN op_rank = 8 THEN days_since_previous END) AS op8_days_since_prev,
    MAX(CASE WHEN op_rank = 9 THEN Operation_Name END) AS op9_name,
    MAX(CASE WHEN op_rank = 9 THEN latest_date END) AS op9_date,
    MAX(CASE WHEN op_rank = 9 THEN days_since_previous END) AS op9_days_since_prev,
    MAX(CASE WHEN op_rank = 10 THEN Operation_Name END) AS op10_name,
    MAX(CASE WHEN op_rank = 10 THEN latest_date END) AS op10_date,
    MAX(CASE WHEN op_rank = 10 THEN days_since_previous END) AS op10_days_since_prev
FROM top_10_latest_ops
GROUP BY Case_number
ORDER BY Case_number;

Key Notes: #

• Date Conversion: Adjust STR_TO_DATE to match your database's date parsing function (e.g., TO_DATE for Oracle/PostgreSQL, CONVERT for SQL Server).
• No Deduplication? If you want to include repeated operations (not just unique ones), remove the deduplicated_ops CTE and directly apply ROW_NUMBER to the raw table ordered by date descending.
• Pivot Alternatives: If your database supports PIVOT (like SQL Server), you can use that instead of conditional aggregation for a more concise query.

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