Oracle SQL如何显示无记录的1001及以上订单数量区间为0计数

要让所有指定区间都显示，哪怕该区间没有订单（计数为0），核心思路是先构造完整的区间列表，再和实际订单统计结果做左连接，这样就能补全无数据的区间。

修改后的SQL代码 #

WITH AllBuckets AS (
    SELECT '1. 0-100' AS "Bucket" FROM DUAL
    UNION ALL
    SELECT '2. 101-400' AS "Bucket" FROM DUAL
    UNION ALL
    SELECT '3. 401-800' AS "Bucket" FROM DUAL
    UNION ALL
    SELECT '4. 801-1000' AS "Bucket" FROM DUAL
    UNION ALL
    SELECT '5. 1001 and Above' AS "Bucket" FROM DUAL
)
SELECT 
    ab."Bucket",
    NVL(ot."Order Count", 0) AS "Order Count"
FROM AllBuckets ab
LEFT JOIN (
    SELECT 
        CASE
            WHEN O.QuantityShares <= 100 THEN '1. 0-100'
            WHEN O.QuantityShares <= 400 THEN '2. 101-400'
            WHEN O.QuantityShares <= 800 THEN '3. 401-800'
            WHEN O.QuantityShares <= 1000 THEN '4. 801-1000'
            ELSE '5. 1001 and Above'
        END AS "Bucket",
        COUNT(*) AS "Order Count"
    FROM OrderTransactions O
    GROUP BY 
        CASE
            WHEN O.QuantityShares <= 100 THEN '1. 0-100'
            WHEN O.QuantityShares <= 400 THEN '2. 101-400'
            WHEN O.QuantityShares <= 800 THEN '3. 401-800'
            WHEN O.QuantityShares <= 1000 THEN '4. 801-1000'
            ELSE '5. 1001 and Above'
        END
) ot ON ab."Bucket" = ot."Bucket"
ORDER BY ab."Bucket" ASC;

代码说明 #

• AllBuckets子句：用UNION ALL生成所有需要统计的区间，确保每个区间都被包含。Oracle中用FROM DUAL来生成单条记录。
• 左连接（LEFT JOIN）：以完整的区间列表为基础，左连接实际订单统计结果。这样即使某个区间没有订单，也会保留该区间的行，对应的计数为NULL。
• NVL函数：把NULL的计数转换成0，符合需求中显示0的要求。
• 排序：直接按预先定义的区间顺序排序，保证结果顺序正确。

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