Oracle 12c基于日历表的多表每日车辆记录统计实现求助

嗨，这个多表每日统计的问题我帮你搞定！

首先，你的思路完全正确——必须基于日历表来确保每个日期都能出现在结果里，哪怕某张表当天没有数据也要显示0。下面我分步骤给你解决方案：

1. 核心思路拆解 #

• 先对每张源表按日期聚合，计算每天的车辆总数
• 用日历表左连接所有聚合后的表，保证日历表的每个日期都能保留
• 用NVL()函数把空值替换成0，确保无数据的日期显示0
• 如果是40张表，我们可以用动态SQL来避免重复写冗余代码

2. 静态示例（针对CARS1和CARS2） #

先给你一个针对当前两张表的完整查询，完全匹配你要的结果格式：

WITH calendar AS (
    SELECT TO_DATE('2020-08-01', 'YYYY-MM-DD') + (rownum - 1) AS sale_date
    FROM dual
    CONNECT BY rownum <= TRUNC(SYSDATE) - TO_DATE('2020-08-01', 'YYYY-MM-DD') + 1
),
cars1_daily AS (
    SELECT sale_date, SUM(count_cars) AS total_cars1
    FROM cars1
    GROUP BY sale_date
),
cars2_daily AS (
    SELECT sale_date, SUM(count_cars) AS total_cars2
    FROM cars2
    GROUP BY sale_date
)
SELECT 
    c.sale_date,
    NVL(c1.total_cars1, 0) AS cars1,
    NVL(c2.total_cars2, 0) AS cars2
FROM calendar c
LEFT JOIN cars1_daily c1 ON c.sale_date = c1.sale_date
LEFT JOIN cars2_daily c2 ON c.sale_date = c2.sale_date
ORDER BY c.sale_date;

代码说明：

• calendar：生成从2020-08-01到当前日期的所有日期（用TRUNC(SYSDATE)确保只取日期部分，避免时间干扰）
• cars1_daily/cars2_daily：分别统计两张表每天的车辆总数
• 最后左连接后用NVL()把空值转成0，保证每个日期都有数值展示

3. 扩展到40张表的动态SQL方案 #

如果有40张类似结构的表，手动写40个CTE太繁琐，我们可以用动态SQL自动生成查询语句：

DECLARE
    v_sql VARCHAR2(32767);
    v_join_clause VARCHAR2(32767);
    v_select_cols VARCHAR2(32767);
BEGIN
    -- 获取所有需要统计的表名（这里假设表名以CARS开头，可根据实际调整匹配规则）
    SELECT 
        LISTAGG(
            'LEFT JOIN (SELECT sale_date, SUM(count_cars) AS total_' || table_name || ' FROM ' || table_name || ' GROUP BY sale_date) t' || table_name || ' ON c.sale_date = t' || table_name || '.sale_date',
            ' '
        ) INTO v_join_clause,
        LISTAGG(
            'NVL(t' || table_name || '.total_' || table_name || ', 0) AS ' || table_name,
            ', '
        ) INTO v_select_cols
    FROM user_tables
    WHERE table_name LIKE 'CARS%';

    -- 拼接完整SQL
    v_sql := '
        WITH calendar AS (
            SELECT TO_DATE(''2020-08-01'', ''YYYY-MM-DD'') + (rownum - 1) AS sale_date
            FROM dual
            CONNECT BY rownum <= TRUNC(SYSDATE) - TO_DATE(''2020-08-01'', ''YYYY-MM-DD'') + 1
        )
        SELECT 
            c.sale_date, ' || v_select_cols || '
        FROM calendar c ' || v_join_clause || '
        ORDER BY c.sale_date';

    -- 先输出生成的SQL确认正确性，没问题后再执行
    DBMS_OUTPUT.PUT_LINE(v_sql);
    -- EXECUTE IMMEDIATE v_sql; -- 确认后打开这行直接执行
END;
/

说明：

• 这段PL/SQL会自动查询所有符合条件的表（比如以CARS开头），自动拼接左连接语句和字段列表
• 你可以根据实际表名规则调整WHERE table_name LIKE 'CARS%'的匹配条件
• 执行前建议先通过DBMS_OUTPUT查看生成的SQL是否符合预期，避免出错

验证结果 #

用你提供的测试数据，静态查询会得到你期望的结果：

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