Oracle如何创建带金额递减逻辑的匹配表tbMatching？

1. 创建tbMatching表 #

先创建目标匹配表，根据Oracle版本选择自增主键方式：

适用于Oracle 12c及以上版本（使用IDENTITY） #

CREATE TABLE tbMatching (
    IDM NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    IDA NUMBER NOT NULL,
    IDB NUMBER NOT NULL,
    AmountBefore NUMBER(18,2) NOT NULL,
    Amountdecrease NUMBER(18,2) NOT NULL,
    AmountAfter NUMBER(18,2) NOT NULL,
    CONSTRAINT fk_matching_ida FOREIGN KEY (IDA) REFERENCES tbA(ID),
    CONSTRAINT fk_matching_idb FOREIGN KEY (IDB) REFERENCES tbB(ID)
);

适用于Oracle 12c以下版本（使用序列） #

-- 先创建自增序列
CREATE SEQUENCE seq_tbMatching START WITH 1 INCREMENT BY 1;

-- 创建匹配表
CREATE TABLE tbMatching (
    IDM NUMBER PRIMARY KEY,
    IDA NUMBER NOT NULL,
    IDB NUMBER NOT NULL,
    AmountBefore NUMBER(18,2) NOT NULL,
    Amountdecrease NUMBER(18,2) NOT NULL,
    AmountAfter NUMBER(18,2) NOT NULL,
    CONSTRAINT fk_matching_ida FOREIGN KEY (IDA) REFERENCES tbA(ID),
    CONSTRAINT fk_matching_idb FOREIGN KEY (IDB) REFERENCES tbB(ID)
);

2. 生成匹配数据的SQL逻辑 #

通过窗口函数计算累计金额，实现tbA金额按tbB记录顺序逐步递减匹配：

INSERT INTO tbMatching (IDA, IDB, AmountBefore, Amountdecrease, AmountAfter)
WITH tbB_cumulative AS (
    -- 计算每个KEY下tbB的累计金额及上一条累计值
    SELECT 
        b.ID AS IDB,
        b.KEY,
        b.Amount AS B_Amount,
        SUM(b.Amount) OVER (PARTITION BY b.KEY ORDER BY b.ID) AS Cumulative_B_Amount,
        LAG(SUM(b.Amount) OVER (PARTITION BY b.KEY ORDER BY b.ID), 1, 0) 
            OVER (PARTITION BY b.KEY ORDER BY b.ID) AS Previous_Cumulative
    FROM tbB b
),
tbA_B_join AS (
    -- 关联tbA与累计后的tbB数据
    SELECT 
        a.ID AS IDA,
        a.Amount AS A_Amount,
        bc.IDB,
        bc.B_Amount,
        bc.Cumulative_B_Amount,
        bc.Previous_Cumulative
    FROM tbA a
    JOIN tbB_cumulative bc ON a.KEY = bc.KEY
)
-- 计算每一条匹配记录的金额字段
SELECT 
    IDA,
    IDB,
    -- 匹配前的tbA剩余金额
    GREATEST(A_Amount - Previous_Cumulative, 0) AS AmountBefore,
    -- 本次实际匹配的金额（取tbB当前金额与剩余金额的较小值）
    LEAST(B_Amount, A_Amount - Previous_Cumulative) AS Amountdecrease,
    -- 匹配后的tbA剩余金额
    GREATEST(A_Amount - Previous_Cumulative - LEAST(B_Amount, A_Amount - Previous_Cumulative), 0) AS AmountAfter
FROM tbA_B_join
WHERE Previous_Cumulative < A_Amount; -- 过滤掉已完成匹配的无效记录

3. 逻辑说明 #

• tbB_cumulative：通过窗口函数SUM() OVER()计算每个KEY下tbB记录的累计金额，同时用LAG()获取上一条记录的累计值，用于确定当前匹配的起始剩余金额。
• tbA_B_join：将tbA与处理后的tbB数据按KEY关联，实现一对多的基础配对。
• 金额计算逻辑：
  • AmountBefore：当前匹配前tbA的剩余金额，即总金额减去之前已匹配的累计金额。
  • Amountdecrease：本次实际抵扣的金额，不会超过tbB当前记录的金额，也不会超过tbA的剩余金额。
  • AmountAfter：匹配完成后tbA的剩余金额，为匹配前金额减去本次抵扣金额。
  • 过滤条件确保只生成还有剩余金额需要匹配的记录，避免tbB累计金额超过tbA金额后的无效条目。

4. 测试示例 #

假设：

• tbA记录：ID=1, KEY='K1', Amount=100
• tbB记录：
  • ID=1, KEY='K1', Amount=30
  • ID=2, KEY='K1', Amount=50
  • ID=3, KEY='K1', Amount=40

执行插入后，tbMatching会生成3条记录：

内容的提问来源于stack exchange，提问作者Dai Cung cong