Oracle查询改写：用CASE语句调整SELECT按规则获取最早行

原查询返回数据 #

原查询语句 #

SELECT t1.Nbr
,t1.Type
,MAX(FUNCTION(t1.TelNbr)) TelNbr
,t2.PNbr
,MIN(t1.Date)
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id 
GROUP BY t1.Nbr, t1.Type, t2.PNbr

需求说明 #

扩展原查询，实现以下逻辑：

• 针对每个TelNbr实例，当Type为AA时，获取该行对应的最早日期（MIN(t1.Date)）；
• 若Nbr或PNbr为NULL，则不区分Type，直接获取每个TelNbr实例的最早日期。

错误分析与修正方案 #

ORA-00936错误通常是因为SQL语句中缺少必要表达式，比如CASE语法错误、聚合函数使用不当，或GROUP BY与SELECT列表不匹配。以下是两种可行的修正方案：

方案一：使用窗口函数（推荐） #

窗口函数能更直观地实现动态分组逻辑，避免GROUP BY的复杂嵌套：

WITH ranked_data AS (
    SELECT 
        t1.Nbr,
        t1.Type,
        FUNCTION(t1.TelNbr) AS TelNbr,  -- 替换为实际函数名（如TO_CHAR、自定义函数）
        t2.PNbr,
        t1.Date,
        -- 动态生成分组键：Nbr/PNbr为空时按TelNbr分组，否则按TelNbr+Type分组
        CASE 
            WHEN t1.Nbr IS NULL OR t2.PNbr IS NULL THEN t1.TelNbr
            ELSE t1.TelNbr || '|' || t1.Type
        END AS group_key,
        -- 按分组键分区，取最早日期的行
        ROW_NUMBER() OVER (
            PARTITION BY 
                CASE 
                    WHEN t1.Nbr IS NULL OR t2.PNbr IS NULL THEN t1.TelNbr
                    ELSE t1.TelNbr || '|' || t1.Type
                END
            ORDER BY t1.Date ASC
        ) AS rn
    FROM table1 t1
    LEFT JOIN table2 t2 ON t1.id = t2.id
    -- 按需过滤：仅保留Type=AA的行，或Nbr/PNbr为空的行
    WHERE (t1.Type = 'AA') OR (t1.Nbr IS NULL OR t2.PNbr IS NULL)
)
SELECT 
    Nbr,
    Type,
    TelNbr,
    PNbr,
    Date AS earliest_date
FROM ranked_data
WHERE rn = 1;

方案二：GROUP BY结合CASE语句 #

若坚持使用GROUP BY，需确保分组键与SELECT中的聚合逻辑严格匹配：

SELECT 
    CASE 
        WHEN t1.Nbr IS NULL OR t2.PNbr IS NULL THEN MAX(t1.Nbr)
        ELSE t1.Nbr
    END AS Nbr,
    CASE 
        WHEN t1.Nbr IS NULL OR t2.PNbr IS NULL THEN MAX(t1.Type)
        ELSE t1.Type
    END AS Type,
    MAX(FUNCTION(t1.TelNbr)) AS TelNbr,  -- 替换为实际函数名
    CASE 
        WHEN t1.Nbr IS NULL OR t2.PNbr IS NULL THEN MAX(t2.PNbr)
        ELSE t2.PNbr
    END AS PNbr,
    MIN(t1.Date) AS earliest_date
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE (t1.Type = 'AA') OR (t1.Nbr IS NULL OR t2.PNbr IS NULL)
GROUP BY 
    CASE 
        WHEN t1.Nbr IS NULL OR t2.PNbr IS NULL THEN t1.TelNbr
        ELSE t1.TelNbr || '|' || t1.Nbr || '|' || t1.Type || '|' || t2.PNbr
    END;

关键注意事项 #

1. 替换FUNCTION(t1.TelNbr)为实际函数名，否则会引发语法错误；
2. 若需求无需过滤非AA类型的行，可移除WHERE子句中的(t1.Type = 'AA')条件。

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