如何使用Oracle SQL获取多列中的首个非零/非空值与最后一个非零/非空值

嘿，这个需求我之前帮不少开发者解决过！针对Oracle SQL里从D1到D31这类多列中提取首个和最后一个非零、非空值的问题，我给你两种实用方案，适配不同场景：

当你有大量列（比如D1到D31共31列）时，把列转成行的方式会更灵活，后期加列也容易维护。核心思路是先把每一行的D1-D31拆成「列名-列值」的行数据，过滤掉空和零后，再按ID分组取首尾符合条件的值。

WITH unpivoted_data AS (
    SELECT 
        ID,
        col_name,
        col_value
    FROM your_table  -- 替换成你的实际表名
    UNPIVOT (
        col_value FOR col_name IN (
            D1 AS 'D1', D2 AS 'D2', D3 AS 'D3',
            D4 AS 'D4', D5 AS 'D5', D6 AS 'D6',
            D7 AS 'D7', D8 AS 'D8', D9 AS 'D9',
            D10 AS 'D10', D11 AS 'D11', D12 AS 'D12',
            D13 AS 'D13', D14 AS 'D14', D15 AS 'D15',
            D16 AS 'D16', D17 AS 'D17', D18 AS 'D18',
            D19 AS 'D19', D20 AS 'D20', D21 AS 'D21',
            D22 AS 'D22', D23 AS 'D23', D24 AS 'D24',
            D25 AS 'D25', D26 AS 'D26', D27 AS 'D27',
            D28 AS 'D28', D29 AS 'D29', D30 AS 'D30',
            D31 AS 'D31'
        )
    )
    WHERE col_value IS NOT NULL AND col_value != 0
)
SELECT 
    ID,
    -- 取最早（列名字母序最小）的非零非空值
    MAX(CASE WHEN col_name = MIN(col_name) THEN col_value END) AS first_non_zero_non_null,
    -- 取最晚（列名字母序最大）的非零非空值
    MAX(CASE WHEN col_name = MAX(col_name) THEN col_value END) AS last_non_zero_non_null
FROM unpivoted_data
GROUP BY ID;

代码说明： #

• UNPIVOT 把每行的D1-D31转换成多行，每行对应一个列名（比如'D1'）和对应的值；
• WHERE 子句过滤掉空值和零值；
• 分组后通过MIN(col_name)找到最早的列，MAX(col_name)找到最晚的列，再匹配对应的列值就是我们要的首尾值。

如果觉得子查询麻烦，也可以直接用COALESCE结合CASE语句，按顺序检查每一列：

SELECT
    ID,
    -- 从D1到D31依次检查，返回第一个非零非空值
    COALESCE(
        CASE WHEN D1 IS NOT NULL AND D1 != 0 THEN D1 END,
        CASE WHEN D2 IS NOT NULL AND D2 != 0 THEN D2 END,
        CASE WHEN D3 IS NOT NULL AND D3 != 0 THEN D3 END,
        -- 这里省略D4到D30的判断，你需要按格式补全
        CASE WHEN D31 IS NOT NULL AND D31 != 0 THEN D31 END
    ) AS first_non_zero_non_null,
    -- 从D31到D1反向检查，返回最后一个非零非空值
    COALESCE(
        CASE WHEN D31 IS NOT NULL AND D31 != 0 THEN D31 END,
        CASE WHEN D30 IS NOT NULL AND D30 != 0 THEN D30 END,
        -- 这里省略D29到D2的判断，你需要按格式补全
        CASE WHEN D1 IS NOT NULL AND D1 != 0 THEN D1 END
    ) AS last_non_zero_non_null
FROM your_table;  -- 替换成你的实际表名

代码说明： #

• COALESCE 会返回第一个非空的参数，所以找首个值时从D1到D31依次判断，找到第一个符合条件的就返回；
• 找最后一个值时反向从D31到D1判断，逻辑同理。

额外提示： #

如果某一行的D1-D31全是空或零，上述两个方案的结果字段会返回NULL，你可以用NVL()函数设置默认值，比如NVL(first_non_zero_non_null, 0)，把NULL替换成0或者你需要的默认值。

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