基于关联表符合条件行数更新ALLOCATED_PRODUCTS表ReservedQuant字段的技术需求

我来帮你搞定这个基于两张表关联更新的问题！根据你的需求，我们需要用RESERVED_BOOKINGS_OVERRIDDEN表中符合时间区间和产品ID匹配的记录行数，去扣减ALLOCATED_PRODUCTS表的ReservedQuant字段。

核心逻辑 #

对于ALLOCATED_PRODUCTS中的每一行，统计RESERVED_BOOKINGS_OVERRIDDEN里满足以下条件的记录数：

• 两张表的booking_product_id完全匹配
• ALLOCATED_PRODUCTS.Date（日期维度）落在RESERVED_BOOKINGS_OVERRIDDEN.on_site_from_dt的日期到on_site_to_dt的日期之间（包含起始日期，不包含结束日期——因为结束时间是当天10点，当天的日期不算在覆盖范围内）

解决方案SQL #

这里提供两种可行的SQL写法，你可以根据自己的数据库环境选择：

方法1：关联子查询更新

这种写法直接针对每一行计算需要扣减的数量，逻辑简洁：

UPDATE ALLOCATED_PRODUCTS
SET ReservedQuant = ReservedQuant - (
    SELECT COUNT(*)
    FROM RESERVED_BOOKINGS_OVERRIDDEN rbo
    WHERE rbo.booking_product_id = ALLOCATED_PRODUCTS.booking_product_id
      AND ALLOCATED_PRODUCTS.Date >= CAST(rbo.on_site_from_dt AS DATE)
      AND ALLOCATED_PRODUCTS.Date < CAST(rbo.on_site_to_dt AS DATE)
)
WHERE EXISTS (
    SELECT 1
    FROM RESERVED_BOOKINGS_OVERRIDDEN rbo
    WHERE rbo.booking_product_id = ALLOCATED_PRODUCTS.booking_product_id
      AND ALLOCATED_PRODUCTS.Date >= CAST(rbo.on_site_from_dt AS DATE)
      AND ALLOCATED_PRODUCTS.Date < CAST(rbo.on_site_to_dt AS DATE)
);

方法2：CTE预统计再更新

这种方法先通过CTE统计每个日期+产品ID对应的覆盖行数，再关联更新，更直观也方便调试：

WITH OverrideCounts AS (
    SELECT
        ap.Date,
        ap.booking_product_id,
        COUNT(rbo.booking_product_id) AS override_count
    FROM ALLOCATED_PRODUCTS ap
    LEFT JOIN RESERVED_BOOKINGS_OVERRIDDEN rbo
        ON ap.booking_product_id = rbo.booking_product_id
        AND ap.Date >= CAST(rbo.on_site_from_dt AS DATE)
        AND ap.Date < CAST(rbo.on_site_to_dt AS DATE)
    GROUP BY ap.Date, ap.booking_product_id
)
UPDATE ap
SET ap.ReservedQuant = ap.ReservedQuant - oc.override_count
FROM ALLOCATED_PRODUCTS ap
JOIN OverrideCounts oc
    ON ap.Date = oc.Date
    AND ap.booking_product_id = oc.booking_product_id
WHERE oc.override_count > 0;

结果验证 #

执行完上述SQL后，ALLOCATED_PRODUCTS表会和你预期的结果完全一致：

• booking_product_id=4的8月5、6日：无匹配覆盖记录，ReservedQuant保持3
• booking_product_id=4的8月7、8日：匹配2条覆盖记录，3-2=1
• booking_product_id=6的8月5日：匹配1条覆盖记录，1-1=0

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