如何获取LEAST计算的最早时间戳来源列？含NULL值处理

没问题，我来帮你搞定这个需求！你的原SQL语句之所以在有NULL值时出错，是因为NULL参与比较运算会返回NULL，导致CASE的分支判断不符合预期。比如当某个时间戳为NULL时，timestamp_2 <= timestamp_3这种比较会返回NULL，CASE会跳过这个分支，直接走到ELSE，结果就不对了。

下面给你几种更简洁且能正确处理NULL的实现方式，你可以根据自己使用的数据库来选择：

方法1：用UNPIVOT（适合SQL Server、Oracle等支持的数据库） #

这种方法把列转成行，然后分组取最小时间和对应的来源，逻辑非常清晰：

WITH unpivoted_data AS (
    SELECT 
        timestamp_1,
        timestamp_2,
        timestamp_3,
        timestamp_4,
        timestamp_val,
        source,
        -- 给每个行的时间戳排序，最小的排第1
        ROW_NUMBER() OVER (
            PARTITION BY timestamp_1, timestamp_2, timestamp_3, timestamp_4 
            ORDER BY timestamp_val ASC
        ) AS rn
    FROM time
    -- 把三个时间戳列转成(source, timestamp_val)的行
    UNPIVOT (
        timestamp_val FOR source IN (timestamp_1, timestamp_2, timestamp_3)
    ) AS unpivoted
)
SELECT 
    timestamp_1,
    timestamp_2,
    timestamp_3,
    timestamp_4,
    timestamp_val AS MIN_time,
    source AS MIN_source
FROM unpivoted_data
WHERE rn = 1; -- 取最小的那个时间戳

如果你的表有主键（比如id），还可以用分组的方式，写法更简洁：

SELECT 
    t.timestamp_1,
    t.timestamp_2,
    t.timestamp_3,
    t.timestamp_4,
    MIN(u.timestamp_val) AS MIN_time,
    MAX(u.source) AS MIN_source -- 因为最小时间对应的source唯一，MAX/MIN都可以
FROM time t
JOIN (
    SELECT 
        id,
        timestamp_val,
        source
    FROM time
    UNPIVOT (
        timestamp_val FOR source IN (timestamp_1, timestamp_2, timestamp_3)
    ) AS u
) u ON t.id = u.id
GROUP BY t.id, t.timestamp_1, t.timestamp_2, t.timestamp_3, t.timestamp_4;

方法2：优化CASE语句（通用所有数据库） #

我们可以用COALESCE把NULL转为一个极晚的时间（比如'9999-12-31'），这样NULL就不会干扰比较逻辑，同时处理全NULL的情况：

SELECT
    timestamp_1,
    timestamp_2,
    timestamp_3,
    timestamp_4,
    -- 全NULL时返回NULL，否则取最小时间
    CASE 
        WHEN timestamp_1 IS NULL AND timestamp_2 IS NULL AND timestamp_3 IS NULL THEN NULL
        ELSE LEAST(
            COALESCE(timestamp_1, '9999-12-31'),
            COALESCE(timestamp_2, '9999-12-31'),
            COALESCE(timestamp_3, '9999-12-31')
        ) 
    END AS MIN_time,
    -- 判断最小时间的来源
    CASE
        WHEN timestamp_1 IS NULL AND timestamp_2 IS NULL AND timestamp_3 IS NULL THEN NULL
        WHEN COALESCE(timestamp_1, '9999-12-31') <= COALESCE(timestamp_2, '9999-12-31') 
             AND COALESCE(timestamp_1, '9999-12-31') <= COALESCE(timestamp_3, '9999-12-31') THEN 'timestamp_1'
        WHEN COALESCE(timestamp_2, '9999-12-31') <= COALESCE(timestamp_3, '9999-12-31') THEN 'timestamp_2'
        ELSE 'timestamp_3'
    END AS MIN_source
FROM time;

方法3：用VALUES子句构造行（通用所有数据库） #

这种方法不需要依赖数据库特定语法，把三个时间戳和来源转为临时行，再取最小的：

SELECT
    t.timestamp_1,
    t.timestamp_2,
    t.timestamp_3,
    t.timestamp_4,
    -- 取三个时间戳中的最小值
    (SELECT MIN(ts) FROM (VALUES (t.timestamp_1), (t.timestamp_2), (t.timestamp_3)) AS v(ts)) AS MIN_time,
    -- 找到最小值对应的来源
    (SELECT src 
     FROM (VALUES 
             (t.timestamp_1, 'timestamp_1'), 
             (t.timestamp_2, 'timestamp_2'), 
             (t.timestamp_3, 'timestamp_3')
          ) AS v(ts, src)
     WHERE ts = (SELECT MIN(ts) FROM (VALUES (t.timestamp_1), (t.timestamp_2), (t.timestamp_3)) AS v2(ts))
    ) AS MIN_source
FROM time t;

如果存在多个时间戳相同且都是最小值的情况，这个查询会返回第一个匹配的来源；如果需要把所有来源都列出来，可以用STRING_AGG(src, ',')这类函数（根据数据库调整）。

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