Oracle SQL Developer中多列复用CASE WHEN并堆叠结果的实现

你可以通过UNION ALL将多个列的分组统计结果堆叠到同一张表中，核心思路是为每个目标列单独编写与原查询逻辑一致的子查询，再将这些子查询的结果合并。同时建议新增一个标识列，用来区分每条统计结果属于哪个原始列。

完整SQL代码 #

-- 统计sports_returns_l90d列
SELECT 
    'sports_returns_l90d' AS METRIC_NAME,
    BG_TURNOVER,
    MARKET,
    COUNT(*) AS RECORD_COUNT
FROM (
    SELECT 
        CASE 
            WHEN sports_returns_l90d = 0 THEN 'sports_returns_l90d_0'
            WHEN sports_returns_l90d > 0 AND sports_returns_l90d <= 500 THEN 'sports_returns_l90d_0_500'
            WHEN sports_returns_l90d > 500 AND sports_returns_l90d <= 1000 THEN 'sports_returns_l90d_500_1000'
            WHEN sports_returns_l90d > 1000 AND sports_returns_l90d <= 2500 THEN 'sports_returns_l90d_1000_2500'
            WHEN sports_returns_l90d > 2500 AND sports_returns_l90d <= 5000 THEN 'sports_returns_l90d_2500_5000'
            ELSE 'sports_returns_l90d_5000+'
        END AS BG_TURNOVER,
        MARKET
    FROM PRODUCT_TABLE
)
GROUP BY BG_TURNOVER, MARKET

UNION ALL

-- 统计sports_returns列
SELECT 
    'sports_returns' AS METRIC_NAME,
    BG_TURNOVER,
    MARKET,
    COUNT(*) AS RECORD_COUNT
FROM (
    SELECT 
        CASE 
            WHEN sports_returns = 0 THEN 'sports_returns_0'
            WHEN sports_returns > 0 AND sports_returns <= 500 THEN 'sports_returns_0_500'
            WHEN sports_returns > 500 AND sports_returns <= 1000 THEN 'sports_returns_500_1000'
            WHEN sports_returns > 1000 AND sports_returns <= 2500 THEN 'sports_returns_1000_2500'
            WHEN sports_returns > 2500 AND sports_returns <= 5000 THEN 'sports_returns_2500_5000'
            ELSE 'sports_returns_5000+'
        END AS BG_TURNOVER,
        MARKET
    FROM PRODUCT_TABLE
)
GROUP BY BG_TURNOVER, MARKET

UNION ALL

-- 统计bingo_returns列
SELECT 
    'bingo_returns' AS METRIC_NAME,
    BG_TURNOVER,
    MARKET,
    COUNT(*) AS RECORD_COUNT
FROM (
    SELECT 
        CASE 
            WHEN bingo_returns = 0 THEN 'bingo_returns_0'
            WHEN bingo_returns > 0 AND bingo_returns <= 500 THEN 'bingo_returns_0_500'
            WHEN bingo_returns > 500 AND bingo_returns <= 1000 THEN 'bingo_returns_500_1000'
            WHEN bingo_returns > 1000 AND bingo_returns <= 2500 THEN 'bingo_returns_1000_2500'
            WHEN bingo_returns > 2500 AND bingo_returns <= 5000 THEN 'bingo_returns_2500_5000'
            ELSE 'bingo_returns_5000+'
        END AS BG_TURNOVER,
        MARKET
    FROM PRODUCT_TABLE
)
GROUP BY BG_TURNOVER, MARKET;

代码说明 #

1. METRIC_NAME列：新增的标识列，明确每条统计结果对应的原始列（比如sports_returns_l90d、sports_returns），方便后续区分数据来源。
2. CASE逻辑复用：每个子查询中，仅替换CASE语句里的目标列名和分组名称前缀，保持分组区间逻辑完全一致。
3. UNION ALL合并：使用UNION ALL而非UNION，因为不需要去重，能提升查询效率；要求每个子查询的列数、列数据类型完全匹配。
4. 分组统计：每个子查询内部先通过CASE生成分组，再按分组和MARKET维度统计数量。

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