如何实现SQL查询：基于t1表生成含Final Date的结果集

Here's a clean, efficient solution using common table expressions (CTEs) to implement your business logic. This approach scans the table once to compute necessary aggregates, making it both readable and performant:

WITH id_metadata AS (
    SELECT 
        ID,
        `DATE`,
        `INDEX`,
        -- Assign a rank to identify the latest record per ID
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY `DATE` DESC) AS record_rank,
        -- Get the most recent date for each ID
        MAX(`DATE`) OVER (PARTITION BY ID) AS latest_id_date,
        -- Get the newest date where INDEX was 'Y' (null if no such records exist)
        MAX(CASE WHEN `INDEX` = 'Y' THEN `DATE` ELSE NULL END) OVER (PARTITION BY ID) AS latest_y_date
    FROM t1
)
SELECT 
    ID,
    `DATE`,
    `INDEX`,
    -- Apply your business rule to determine Final Date
    CASE 
        WHEN record_rank = 1 AND `INDEX` = 'N' THEN
            COALESCE(latest_y_date, latest_id_date)
        ELSE `DATE` -- Fallback (we filter to only latest records below)
    END AS `Final Date`
FROM id_metadata
-- Keep only the latest record per ID
WHERE record_rank = 1;

Breakdown of how this works: #

1. CTE (id_metadata):

   • record_rank: Numbers each record in descending order of date for every ID. The latest record gets a rank of 1.
   • latest_id_date: Uses a window function to calculate the most recent date for each ID.
   • latest_y_date: Uses a conditional MAX to find the newest date where INDEX was 'Y' for each ID (returns NULL if there are no 'Y' entries).

2. Main Query:

   • Filters to only the latest record per ID (record_rank = 1).
   • Uses CASE to apply your rule: if the latest record has INDEX = 'N', use the most recent 'Y' date (if it exists) via COALESCE; otherwise, stick with the latest date.

If your SQL dialect doesn't support CTEs, here's an alternative using correlated subqueries:

SELECT 
    t.ID,
    t.`DATE`,
    t.`INDEX`,
    CASE 
        WHEN t.`INDEX` = 'N' THEN
            COALESCE(
                (SELECT MAX(`DATE`) FROM t1 WHERE ID = t.ID AND `INDEX` = 'Y'),
                t.`DATE`
            )
        ELSE t.`DATE`
    END AS `Final Date`
FROM t1 t
WHERE t.`DATE` = (SELECT MAX(`DATE`) FROM t1 WHERE ID = t.ID);

Both queries will produce your desired output:

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