如何用SQL基于其他列值比较同列两值并将结果存入新列

针对你的需求，核心思路是先按用户分组提取对应阶段的时间，再计算差值并填充到所有行中。以下是不同数据库环境的实现方案：

MySQL 实现 #

假设你的表名为stage_times，可以使用窗口函数来实现：

SELECT
    Time,
    Stage,
    Name,
    -- 将分钟差格式化为 HH:MM 格式
    CONCAT(
        LPAD(FLOOR(TIMESTAMPDIFF(MINUTE, stage1_time, stage3_time)/60), 2, '0'),
        ':',
        LPAD(MOD(TIMESTAMPDIFF(MINUTE, stage1_time, stage3_time), 60), 2, '0')
    ) AS Comp_Time
FROM (
    SELECT
        *,
        -- 按用户分组提取 Stage 1 的时间
        MAX(CASE WHEN Stage = 1 THEN CAST(Time AS TIME) END) OVER (PARTITION BY Name) AS stage1_time,
        -- 按用户分组提取 Stage 3 的时间
        MAX(CASE WHEN Stage = 3 THEN CAST(Time AS TIME) END) OVER (PARTITION BY Name) AS stage3_time
    FROM stage_times
) AS sub_query;

PostgreSQL 实现 #

如果使用PostgreSQL，时间处理语法略有不同：

SELECT
    "Time",
    Stage,
    Name,
    -- 直接计算时间差并格式化
    TO_CHAR(stage3_time - stage1_time, 'HH24:MI') AS Comp_Time
FROM (
    SELECT
        *,
        MAX(CASE WHEN Stage = 1 THEN "Time"::TIME END) OVER (PARTITION BY Name) AS stage1_time,
        MAX(CASE WHEN Stage = 3 THEN "Time"::TIME END) OVER (PARTITION BY Name) AS stage3_time
    FROM stage_times
) AS sub_query;

关键逻辑说明 #

1. 内层子查询通过OVER (PARTITION BY Name)窗口函数，将每个用户的Stage 1和Stage 3时间值填充到该用户的所有行中，避免了聚合操作丢失Stage 2的数据。
2. 外层查询计算两个时间的差值，并格式化为你需要的HH:MM字符串格式。

如果之前尝试CTE没有成功，大概率是没有用窗口函数关联分组后的时间到每一行，而是直接用聚合函数导致数据行数被压缩。

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