技术问询：如何从Purchase表选取行至累计quantity不超过Stock表可用库存

从采购表（Purchase Table）中选取行，要求累计采购数量（quantity）不超过库存表（Stock Table）中对应itemId和storeId的可用库存（available quantity）。若单条记录导致累计超出，则截取该记录的quantity使累计值等于可用库存；选取时需按date倒序排列。

采购表（Purchase Table） #

库存表（Stock Table） #

期望结果表（Result I Want） #

解决方案（SQL） #

使用窗口函数计算分组内的累计采购量，结合库存数据筛选并调整超出部分的数量：

WITH ranked_purchases AS (
    SELECT 
        p.itemId,
        p.storeId,
        p.quantity,
        p.date,
        SUM(p.quantity) OVER (
            PARTITION BY p.itemId, p.storeId 
            ORDER BY p.date DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_qty,
        s.available_quantity
    FROM Purchase_Table p
    JOIN Stock_Table s 
        ON p.itemId = s.itemId 
        AND p.storeId = s.storeId
),
adjusted_purchases AS (
    SELECT 
        itemId,
        storeId,
        CASE 
            WHEN cumulative_qty <= available_quantity THEN quantity
            ELSE available_quantity - (cumulative_qty - quantity)
        END AS quantity,
        date
    FROM ranked_purchases
    WHERE cumulative_qty - quantity < available_quantity
)
SELECT * 
FROM adjusted_purchases 
ORDER BY itemId, storeId, date DESC;

逻辑说明 #

1. ranked_purchases 公共表表达式：关联采购表与库存表，按itemId+storeId分组，以date倒序计算每组内的累计采购量。
2. adjusted_purchases 公共表表达式：判断每条记录的累计量是否超出可用库存，若超出则截取数量使累计值刚好等于库存；过滤掉累计量已超过库存的后续行。
3. 最终按itemId、storeId和date倒序输出结果，与期望结果一致。

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