如何通过UNION获取未来预算日期？发票与预算表聚合关联问题

现有两张数据表：实际发票表（Invoices）和预算表（Budget），两者均包含日期列，其中预算表的日期覆盖至年末。当前的聚合关联查询仅能获取截至最新发票日期的销售额（Sales）与预算（Budget）数据，如何使用UNION操作使查询结果包含未来的预算日期？

原查询代码如下：

select
t1.invoice_date
,t1.sales_company_id
,t1.Sales
,t2.Budget
from 
(
  select 
             invoice_date
            ,sales_company_id
            ,sum(Sales) as Sales
  From Invoices
  group by
            invoice_date
            ,sales_org_id -- 注：此处原代码存在字段不一致的笔误，应为sales_company_id
) t1
            
left join  
(
   select 
            budget_date
            ,sales_company_id
            ,sum(Budget) as Budget
   from Budget
   group by 
            budget_date
            ,sales_company_id
) t2
  
on t1.invoice_date = t2.budget_date
and t1.sales_company_id = t2.sales_company_id;

解决方案 #

要覆盖未来预算日期，需通过UNION合并两类数据：一类是已有发票记录的日期关联预算，另一类是仅存在预算的未来日期（无对应发票）。

修改后查询代码 #

-- 第一部分：有发票记录的日期，关联对应预算
select
    t1.invoice_date as record_date
    ,t1.sales_company_id
    ,t1.Sales
    ,t2.Budget
from 
(
  select 
             invoice_date
            ,sales_company_id
            ,sum(Sales) as Sales
  From Invoices
  group by
            invoice_date
            ,sales_company_id
) t1
left join  
(
   select 
            budget_date
            ,sales_company_id
            ,sum(Budget) as Budget
   from Budget
   group by 
            budget_date
            ,sales_company_id
) t2
on t1.invoice_date = t2.budget_date
and t1.sales_company_id = t2.sales_company_id

UNION ALL -- 若需去重可替换为UNION，UNION ALL性能更优

-- 第二部分：仅预算存在的未来日期（发票表未覆盖的日期）
select
    t2.budget_date as record_date
    ,t2.sales_company_id
    ,NULL as Sales -- 无发票数据，销售额填NULL（可按需改为0）
    ,t2.Budget
from 
(
   select 
            budget_date
            ,sales_company_id
            ,sum(Budget) as Budget
   from Budget
   group by 
            budget_date
            ,sales_company_id
) t2
left join  
(
  select 
             invoice_date
            ,sales_company_id
  From Invoices
  group by
            invoice_date
            ,sales_company_id
) t1
on t2.budget_date = t1.invoice_date
and t2.sales_company_id = t1.sales_company_id
where t1.invoice_date is NULL; -- 筛选出发票表没有的日期

关键说明 #

• 列名统一：两部分查询的列名必须完全一致，这里将日期列统一命名为record_date，避免列名混乱。
• UNION vs UNION ALL：如果确定未来日期不会出现在发票表中，用UNION ALL更高效（无需去重）；若有重复数据风险，改用UNION自动去重。
• 无发票数据处理：未来日期没有对应销售额，用NULL填充，也可根据业务需求改为0。
• 笔误修正：原查询中t1的group by字段sales_org_id与select中的sales_company_id不一致，已修正，避免语法错误。

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