Oracle SQL中窗口函数(WINDOW function)与CASE语句结合使用问题

• 开窗函数语法结构错误：OVER() 子句如果要按指定规则分组求和，需要明确使用 PARTITION BY 关键字指定分组维度，你直接将CASE语句放在OVER()的括号内，不符合Oracle开窗函数的语法要求。
• CASE语句与括号匹配错误：代码中提前在ELSE 'CHECK'后面加了右括号关闭了OVER()子句，把END关键字放在了OVER()外部，导致整个语法结构断裂。
• 别名引用规则错误：Oracle中字段别名不能使用单引号包裹，单引号仅用于标识字符串常量，别名需要用双引号包裹或者直接不加引号（无空格、特殊字符的前提下）。
• 转义字符问题：代码里的<>是HTML转义后的字符，实际SQL执行时要换成标准的不等于运算符<>。

如果你需要的是每行都返回对应分类的总求和值的开窗聚合效果，修正后代码如下：

SUM(A11.NEW_MRP_QTY) OVER (
    PARTITION BY CASE 
        WHEN A11.STOCK_TYPE = 'C' THEN 'INVENTORY'
        WHEN A11.STOCK_TYPE = 'L' THEN 'INTRANSIT PO'
        WHEN A11.STOCK_TYPE = 'B' THEN 'PO AT FACTORY'
        WHEN (A11.STOCK_TYPE = 'A' AND A11.POR_ORDER_TYPE = 'CO') THEN 'PO AT FACTORY'
        WHEN (A11.STOCK_TYPE = 'A' AND A11.POR_ORDER_TYPE <> 'CO') THEN 'NOT BOUGHT'
        WHEN A11.STOCK_TYPE = ' ' THEN 'BLANK STOCK TYPE'
        ELSE 'CHECK'
    END
) AS SUPPLY_QTY

如果你需要的是按分类聚合返回每个分类对应求和值的普通分组聚合效果，参考代码如下：

SELECT 
    CASE 
        WHEN A11.STOCK_TYPE = 'C' THEN 'INVENTORY'
        WHEN A11.STOCK_TYPE = 'L' THEN 'INTRANSIT PO'
        WHEN A11.STOCK_TYPE = 'B' THEN 'PO AT FACTORY'
        WHEN (A11.STOCK_TYPE = 'A' AND A11.POR_ORDER_TYPE = 'CO') THEN 'PO AT FACTORY'
        WHEN (A11.STOCK_TYPE = 'A' AND A11.POR_ORDER_TYPE <> 'CO') THEN 'NOT BOUGHT'
        WHEN A11.STOCK_TYPE = ' ' THEN 'BLANK STOCK TYPE'
        ELSE 'CHECK'
    END AS SUPPLY_TYPE,
    SUM(A11.NEW_MRP_QTY) AS SUPPLY_QTY
FROM 你的表名 A11
GROUP BY CASE 
    WHEN A11.STOCK_TYPE = 'C' THEN 'INVENTORY'
    WHEN A11.STOCK_TYPE = 'L' THEN 'INTRANSIT PO'
    WHEN A11.STOCK_TYPE = 'B' THEN 'PO AT FACTORY'
    WHEN (A11.STOCK_TYPE = 'A' AND A11.POR_ORDER_TYPE = 'CO') THEN 'PO AT FACTORY'
    WHEN (A11.STOCK_TYPE = 'A' AND A11.POR_ORDER_TYPE <> 'CO') THEN 'NOT BOUGHT'
    WHEN A11.STOCK_TYPE = ' ' THEN 'BLANK STOCK TYPE'
    ELSE 'CHECK'
END

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