Oracle SQL CASE WHEN语句问题：按除法结果返回商或prod_low明细

原代码问题分析 #

原SQL的核心问题是聚合函数和非聚合列在CASE中混用：COUNT(prod_low)/COUNT(prod_total)是全局聚合值，而prod_low是行级列值，两者数据类型、层级不匹配，Oracle会直接报错。另外子查询里的DISTINCT用法冗余，没必要重复去重。

场景1：按比例阈值返回结果 #

需求：当COUNT(prod_low)/COUNT(prod_total) < 0.005时显示该比例值；否则返回所有prod_low的明细行。

实现思路：先计算全局的比例值，再通过关联判断当前阈值，分支返回对应结果。

WITH stats AS (
    -- 计算全局的prod_total总数和prod_low数量
    SELECT 
        COUNT(DISTINCT p1.prods) AS total_count,
        COUNT(DISTINCT p2.prods) AS low_count
    FROM products p1
    LEFT JOIN products p2 
        ON p1.id = p2.id AND p2.price < 10
)
-- 根据阈值分支返回结果
SELECT 
    CASE 
        WHEN (low_count / total_count) < 0.005 
        THEN TO_CHAR((low_count / total_count)*100, 'FM999.99') || '%'
        ELSE prod_low
    END AS result
FROM (
    -- 获取prod_low明细（去重）
    SELECT DISTINCT p2.prods AS prod_low
    FROM products p2
    WHERE p2.price < 10
)
CROSS JOIN stats
-- 比例小于阈值时只返回一行比例值；否则返回所有明细
WHERE (low_count / total_count) >= 0.005 
   OR (low_count / total_count) < 0.005 AND ROWNUM = 1;

场景2：每行prod_low附带比例百分比 #

需求：无论阈值如何，都显示所有prod_low明细，每行附带全局比例的百分比值。

WITH stats AS (
    SELECT 
        COUNT(DISTINCT p1.prods) AS total_count,
        COUNT(DISTINCT p2.prods) AS low_count
    FROM products p1
    LEFT JOIN products p2 
        ON p1.id = p2.id AND p2.price < 10
)
SELECT 
    prod_low,
    TO_CHAR((low_count / total_count)*100, 'FM999.99') || '%' AS low_product_ratio
FROM (
    SELECT DISTINCT p2.prods AS prod_low
    FROM products p2
    WHERE p2.price < 10
)
CROSS JOIN stats;

场景3：每行prod_low附带alert提示 #

需求：显示所有prod_low明细，每行附带alert: >=0.5%或no alert: <0.5%的提示。

WITH stats AS (
    SELECT 
        COUNT(DISTINCT p1.prods) AS total_count,
        COUNT(DISTINCT p2.prods) AS low_count
    FROM products p1
    LEFT JOIN products p2 
        ON p1.id = p2.id AND p2.price < 10
)
SELECT 
    prod_low,
    CASE 
        WHEN (low_count / total_count) >= 0.005 
        THEN 'alert: >=0.5%'
        ELSE 'no alert: <0.5%'
    END AS status提示
FROM (
    SELECT DISTINCT p2.prods AS prod_low
    FROM products p2
    WHERE p2.price < 10
)
CROSS JOIN stats;

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