Oracle查询实现无销售记录时段返回0的解决方案求助

需求：现有Oracle查询用于按小时统计销售信息，但仅返回有销售记录的时段。需要让无销售的小时对应的# TICKET、$ TICKET、# ITEMS字段返回0，且包含指定的12-16点所有时段。

原查询代码 #

SELECT COUNT(a.DOC_NO) AS "# TICKET",
       SUM(a.TRANSACTION_TOTAL_AMT) AS "$ TICKET",
       SUM(a.sold_qty) AS "# ITEMS",  
       a.store_code AS "TIENDA",
       to_char(a.CREATED_DATETIME,'yyyy-mm-dd') AS "FECHA",
       to_char(a.CREATED_DATETIME,'HH24') || ':00' AS "HORA"  
FROM document a  
WHERE a.receipt_type in (0,1) 
AND a.status=4 
AND a.store_name!='CEDIS' 
AND to_char(a.CREATED_DATETIME,'yyyy-mm-dd') = '2024-06-01' 
AND a.STORE_CODE = 'ESF' 
GROUP BY a.store_code, to_char(a.CREATED_DATETIME,'yyyy-mm-dd'), to_char(a.CREATED_DATETIME,'HH24') 
ORDER BY a.store_code, to_char(a.CREATED_DATETIME,'yyyy-mm-dd'), to_char(a.CREATED_DATETIME,'HH24')

当前问题：仅返回有销售记录的时段（如12:00、14:00），缺少无销售的时段（如13:00）。

错误尝试的代码（报错：missing right parenthesis） #

SELECT CASE WHEN EXISTS(
        SELECT  COUNT(a.DOC_NO) AS "# TICKET",
    SUM(a.TRANSACTION_TOTAL_AMT) AS "$ TICKET",
        SUM(a.sold_qty) AS "# ITEMS", a.store_code AS "TIENDA",
        to_char(a.CREATED_DATETIME,'yyyy-mm-dd') AS "FECHA",
        to_char(a.CREATED_DATETIME,'HH24') || ':00' AS "HORA"
    FROM document a
    WHERE a.receipt_type in (0,1) and a.status=4 
        AND a.store_name!='CEDIS' 
        AND to_char(a.CREATED_DATETIME,'yyyy-mm-dd') = '2024-06-01' 
        AND a.STORE_CODE = 'ESF'
GROUP BY a.store_code,to_char(a.CREATED_DATETIME,'yyyy-mm-dd'), to_char(a.CREATED_DATETIME,'HH24')
ORDER BY a.store_code, to_char(a.CREATED_DATETIME,'yyyy-mm-dd'), to_char(a.CREATED_DATETIME,'HH24')
)
THEN to_char(a.CREATED_DATETIME,'HH24')
ELSE NULL END AS hours
FROM (VALUES('12'),('13'),('14'),('15'),('16')) CON(hours)

期望结果 #

正确实现方法 #

核心思路是先生成完整的小时列表（12-16点），再将其与销售统计数据做左连接，最后用NVL函数将空值替换为0。

解法代码 #

WITH hours_list AS (
    -- 生成指定的12-16点小时数据
    SELECT '12' AS hour_num FROM DUAL UNION ALL
    SELECT '13' FROM DUAL UNION ALL
    SELECT '14' FROM DUAL UNION ALL
    SELECT '15' FROM DUAL UNION ALL
    SELECT '16' FROM DUAL
),
sales_stats AS (
    -- 原查询的统计逻辑，保留小时数字段用于关联
    SELECT COUNT(a.DOC_NO) AS ticket_count,
           SUM(a.TRANSACTION_TOTAL_AMT) AS amount_total,
           SUM(a.sold_qty) AS item_count,
           a.store_code AS store,
           to_char(a.CREATED_DATETIME,'yyyy-mm-dd') AS sale_date,
           to_char(a.CREATED_DATETIME,'HH24') AS hour_num  -- 仅保留小时数字，用于关联
    FROM document a  
    WHERE a.receipt_type IN (0,1) 
      AND a.status = 4 
      AND a.store_name != 'CEDIS' 
      AND to_char(a.CREATED_DATETIME,'yyyy-mm-dd') = '2024-06-01' 
      AND a.STORE_CODE = 'ESF' 
    GROUP BY a.store_code, to_char(a.CREATED_DATETIME,'yyyy-mm-dd'), to_char(a.CREATED_DATETIME,'HH24')
)
-- 左连接小时列表和统计数据，补全0值
SELECT NVL(s.ticket_count, 0) AS "# TICKET",
       NVL(s.amount_total, 0) AS "$ TICKET",
       NVL(s.item_count, 0) AS "# ITEMS",
       'ESF' AS "TIENDA",
       '2024-06-01' AS "FECHA",
       h.hour_num || ':00' AS "HORA"
FROM hours_list h
LEFT JOIN sales_stats s 
    ON h.hour_num = s.hour_num
    AND s.store = 'ESF'
    AND s.sale_date = '2024-06-01'
ORDER BY h.hour_num;

关键说明 #

1. 生成小时列表：Oracle低版本不支持VALUES子句直接生成多行数据，所以用UNION ALL结合DUAL表生成12-16点的小时数字。
2. 左连接关联：以小时列表为主表，左连接销售统计数据，确保所有小时都出现在结果中。
3. 空值替换：用NVL函数将无销售时段的统计字段替换为0，符合需求。
4. 固定字段值：因为查询条件中已经指定了门店和日期，直接在结果中写死对应的值，避免关联时的空值问题。

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