如何匹配销售与收据表的日期并关联至产品ID表，生成ID-日期组合行？

嘿，我来帮你搞定这个关联问题！核心思路就是以「产品ID + 日期」作为唯一关联键，先构建出所有需要的ID+日期组合，再分别关联销售和收据表，这样就能保证每个组合对应单独一行输出了。

我先假设你的三张表结构大概是这样（如果实际字段不同，替换成你的字段就行）：

• products：产品主表，包含product_id（主键）和各类属性字段
• sales：销售表，包含product_id、sale_date（销售日期）、sale_amount（销售额）等
• receipts：收据表，包含product_id、receipt_date（收据日期）、receipt_amount（收据金额）等

步骤1：生成所有需要的「产品ID+日期」组合 #

首先得确保我们覆盖所有可能的日期——不管某个产品当天有没有销售或收据，都要生成对应的行。我们可以从销售和收据表中提取所有已出现的日期，再和产品表做交叉关联：

WITH all_dates AS (
    -- 收集销售和收据表中所有唯一日期
    SELECT sale_date AS record_date FROM sales
    UNION
    SELECT receipt_date AS record_date FROM receipts
),
product_date_pairs AS (
    -- 生成每个产品与每个日期的组合
    SELECT p.product_id, ad.record_date
    FROM products p
    CROSS JOIN all_dates ad
)

步骤2：关联销售与收据数据 #

接下来用左连接把销售和收据数据关联到这个组合表上，这样即使某天某个产品没有销售或收据，也会保留该行，对应字段显示NULL（如果需要转成0，可以用COALESCE函数）：

-- 接上上面的CTE，完整SQL如下
WITH all_dates AS (
    SELECT sale_date AS record_date FROM sales
    UNION
    SELECT receipt_date AS record_date FROM receipts
),
product_date_pairs AS (
    SELECT p.product_id, ad.record_date
    FROM products p
    CROSS JOIN all_dates ad
)
SELECT
    pdp.product_id,
    pdp.record_date AS date,
    p.attribute1, -- 替换成你的产品属性字段
    p.attribute2,
    s.sale_amount,
    r.receipt_amount
FROM product_date_pairs pdp
-- 关联产品属性
JOIN products p ON pdp.product_id = p.product_id
-- 左连接销售表，匹配产品ID和日期
LEFT JOIN sales s ON pdp.product_id = s.product_id AND pdp.record_date = s.sale_date
-- 左连接收据表，同样匹配产品ID和日期
LEFT JOIN receipts r ON pdp.product_id = r.product_id AND pdp.record_date = r.receipt_date
ORDER BY pdp.product_id, pdp.record_date;

进阶：处理同一天多笔销售/收据的情况 #

如果同一个产品在同一天有多条销售或收据记录，你可能需要聚合数据（比如求和），这时候可以在查询里加上GROUP BY：

WITH all_dates AS (
    SELECT sale_date AS record_date FROM sales
    UNION
    SELECT receipt_date AS record_date FROM receipts
),
product_date_pairs AS (
    SELECT p.product_id, ad.record_date
    FROM products p
    CROSS JOIN all_dates ad
)
SELECT
    pdp.product_id,
    pdp.record_date AS date,
    p.attribute1,
    p.attribute2,
    -- 用COALESCE把NULL转成0，避免显示空值
    COALESCE(SUM(s.sale_amount), 0) AS total_daily_sales,
    COALESCE(SUM(r.receipt_amount), 0) AS total_daily_receipts
FROM product_date_pairs pdp
JOIN products p ON pdp.product_id = p.product_id
LEFT JOIN sales s ON pdp.product_id = s.product_id AND pdp.record_date = s.sale_date
LEFT JOIN receipts r ON pdp.product_id = r.product_id AND pdp.record_date = r.receipt_date
GROUP BY pdp.product_id, pdp.record_date, p.attribute1, p.attribute2
ORDER BY pdp.product_id, pdp.record_date;

额外提醒 #

如果你的业务逻辑里，收据日期和销售日期不是严格同一天（比如收据是对应销售后的若干天），那需要调整关联条件——比如用s.sale_date = r.receipt_date - INTERVAL '1 day'这种方式，但根据你描述的「每个ID和日期组合对应单独一行」，上面的按天匹配逻辑应该是最贴合的。

内容的提问来源于stack exchange，提问作者James C.