Oracle 按金額區間分組查詢：BETWEEN 條件與 CASE WHEN 動態分桶兩種實現

場景

表 t(id, money)，需按金額區間統計：100-200、200-400、400-1000。

方案一：前端傳參 + BETWEEN (固定區間)

SELECT * FROM t WHERE money BETWEEN :min AND :max;
-- 前端按鈕傳遞 min=100,max=200 等

方案二：CASE WHEN 動態分桶 (單查詢全部分布)

SELECT
    CASE
        WHEN money >= 100 AND money < 200 THEN '100-200'
        WHEN money >= 200 AND money < 400 THEN '200-400'
        WHEN money >= 400 AND money <= 1000 THEN '400-1000'
        ELSE '其他'
    END AS money_range,
    COUNT(*) AS cnt,
    SUM(money) AS total
FROM t
GROUP BY
    CASE
        WHEN money >= 100 AND money < 200 THEN '100-200'
        WHEN money >= 200 AND money < 400 THEN '200-400'
        WHEN money >= 400 AND money <= 1000 THEN '400-1000'
        ELSE '其他'
    END
ORDER BY money_range;

進階：寬表交叉分析

若需同時顯示多維度，可使用 WIDTH_BUCKET(money, 100, 1000, 9) (Oracle 10g+) 自動生成等寬桶編號，再關聯標簽表。