如何获取最早两日期的最大TurnTime及构建ID前两轮最大TurnTime查询

咱们一个一个来拆解你的问题，我会给出适配大多数主流数据库（比如PostgreSQL、MySQL、SQL Server）的通用SQL示例，你可以根据自己的数据库类型微调函数细节（比如日期差计算）。

问题1：如何检索最早两个日期对应的最大TurnTime？ #

我先梳理下两种可能的需求场景，你可以根据实际情况选择：

场景1：全局范围内最早两个日期对应的最大TurnTime #

也就是先找出整个数据集里最早的两个不同日期（假设你指的是Beginning_Date字段），再在这些日期的记录里挑出最大的TurnTime：

WITH top_two_global_dates AS (
    -- 获取全局最早的两个不同日期
    SELECT DISTINCT Beginning_Date
    FROM your_table
    ORDER BY Beginning_Date ASC
    LIMIT 2
)
SELECT MAX(TurnTime) AS max_turn_time
FROM your_table
WHERE Beginning_Date IN (SELECT Beginning_Date FROM top_two_global_dates);

场景2：每个ID各自最早两个日期对应的最大TurnTime #

针对每个ID，先找出它最早的两个日期的记录，再取这些记录里的TurnTime最大值：

WITH id_dates_ranked AS (
    -- 给每个ID的记录按日期排序，标记是第几个最早的日期
    SELECT 
        ID,
        TurnTime,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Beginning_Date ASC) AS date_rank
    FROM your_table
)
SELECT 
    ID,
    MAX(TurnTime) AS max_turn_time_top_two_dates
FROM id_dates_ranked
WHERE date_rank <= 2 -- 只保留前两个最早的日期记录
GROUP BY ID;

问题2：获取每个ID前两轮的最大TurnTime #

根据你定义的轮次规则（第一轮是每个ID的最小Beginning_Date到最小End_Date，第二轮不能重复使用任一日期），我的思路是：先标记出第一轮的记录，再筛选出未被第一轮使用的记录来标记第二轮，最后按ID和轮次取最大TurnTime。

假设TurnTime是已有字段的情况 #

WITH id_round1 AS (
    -- 标记第一轮的记录：包含当前ID最小Beginning_Date或最小End_Date的记录
    SELECT 
        ID,
        TurnTime,
        1 AS round_num
    FROM your_table t1
    WHERE Beginning_Date = (SELECT MIN(Beginning_Date) FROM your_table t2 WHERE t2.ID = t1.ID)
       OR End_Date = (SELECT MIN(End_Date) FROM your_table t2 WHERE t2.ID = t1.ID)
),
round2_candidates AS (
    -- 筛选出第一轮未使用的记录：排除包含第一轮日期的所有记录
    SELECT 
        ID,
        TurnTime,
        Beginning_Date,
        End_Date
    FROM your_table t1
    WHERE NOT EXISTS (
        SELECT 1 FROM id_round1 r1 
        WHERE r1.ID = t1.ID 
          AND (t1.Beginning_Date IN (SELECT Beginning_Date FROM your_table t2 WHERE t2.ID = t1.ID AND (t2.Beginning_Date = (SELECT MIN(Beginning_Date) FROM your_table t3 WHERE t3.ID = t1.ID) OR t2.End_Date = (SELECT MIN(End_Date) FROM your_table t3 WHERE t3.ID = t1.ID)))
             OR t1.End_Date IN (SELECT End_Date FROM your_table t2 WHERE t2.ID = t1.ID AND (t2.Beginning_Date = (SELECT MIN(Beginning_Date) FROM your_table t3 WHERE t3.ID = t1.ID) OR t2.End_Date = (SELECT MIN(End_Date) FROM your_table t3 WHERE t3.ID = t1.ID))))
    )
),
id_round2 AS (
    -- 标记第二轮的记录：剩余记录中包含当前ID最小Beginning_Date或最小End_Date的记录
    SELECT 
        ID,
        TurnTime,
        2 AS round_num
    FROM round2_candidates t1
    WHERE Beginning_Date = (SELECT MIN(Beginning_Date) FROM round2_candidates t2 WHERE t2.ID = t1.ID)
       OR End_Date = (SELECT MIN(End_Date) FROM round2_candidates t2 WHERE t2.ID = t1.ID)
),
all_rounds AS (
    -- 合并两轮的记录
    SELECT * FROM id_round1
    UNION ALL
    SELECT * FROM id_round2
)
-- 按ID和轮次分组，取每轮的最大TurnTime
SELECT 
    ID,
    round_num,
    MAX(TurnTime) AS max_turn_time
FROM all_rounds
GROUP BY ID, round_num
ORDER BY ID, round_num;

如果TurnTime需要计算（End_Date - Beginning_Date） #

如果你的表中没有现成的TurnTime字段，需要通过日期差计算，你可以把上面的TurnTime替换成对应数据库的日期差函数：

• PostgreSQL：End_Date - Beginning_Date
• MySQL：DATEDIFF(End_Date, Beginning_Date)
• SQL Server：DATEDIFF(day, Beginning_Date, End_Date)

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