SQL Server计算两表匹配记录日期间差并更新至指定表的实现咨询

核心逻辑如下：

• 关联条件：表A和表B按pcode、product、market、pcenter四个字段等值关联
• 取表B中匹配记录的最小日期（也就是你提到的首次入库日期）
• 用表A的当日日期减去上述最小日期得到天数差，无匹配记录时值为0

常用SQL实现示例 #

1. 先查询确认结果（避免直接更新出错） #

SELECT 
    a.*,
    COALESCE(DATEDIFF(a.date, MIN(b.date)), 0) AS `No.of Days`
FROM 
    A a
LEFT JOIN 
    B b 
    ON a.pcode = b.pcode 
    AND a.product = b.product 
    AND a.market = b.market 
    AND a.pcenter = b.pcenter
GROUP BY 
    a.pcode, a.product, a.market, a.pcenter, a.date;

2. 直接更新表A的No.of Days字段 #

如果是MySQL环境可以用下面的写法：

UPDATE A a
LEFT JOIN (
    SELECT 
        pcode, product, market, pcenter, MIN(date) AS first_date
    FROM B
    GROUP BY pcode, product, market, pcenter
) b ON a.pcode = b.pcode 
    AND a.product = b.product 
    AND a.market = b.market 
    AND a.pcenter = b.pcenter
SET a.`No.of Days` = COALESCE(DATEDIFF(a.date, b.first_date), 0);

如果是PostgreSQL环境更新写法如下：

UPDATE A a
SET "No.of Days" = COALESCE(a.date - b.first_date, 0)
FROM (
    SELECT 
        pcode, product, market, pcenter, MIN(date) AS first_date
    FROM B
    GROUP BY pcode, product, market, pcenter
) b
WHERE 
    a.pcode = b.pcode 
    AND a.product = b.product 
    AND a.market = b.market 
    AND a.pcenter = b.pcenter;
-- 无匹配的记录单独更新为0
UPDATE A a
SET "No.of Days" = 0
WHERE "No.of Days" IS NULL;

注意事项 #

• 执行更新操作前建议先备份表A数据，或者先执行查询语句确认结果符合预期再执行更新
• 注意不同数据库的日期差值函数语法差异：MySQL用DATEDIFF(晚日期,早日期)直接得到天数差，PostgreSQL直接用两个DATE类型相减即可得到天数差
• 如果表B数据量较大，可以给pcode、product、market、pcenter、date加联合索引提升查询效率

内容的提问来源于stack exchange，提问作者Cool-Dip