T-SQL：如何实现两个表间数值组合的匹配与映射？

嘿，我来帮你搞定这个汇总表和细分表的RowID匹配问题！先理清楚核心需求：我们有来自不同系统的两张表，table1存汇总总额，table2存对应细分项，仅靠ID关联；需要找到table2中对应table1某RowID的细分项，让它们的数值总和等于table1该行的Value，最终完成RowID的映射。

先明确示例表结构（基于需求假设） #

先给出两张表的典型结构，方便后续方案落地：

-- 汇总表table1：存储各RowID对应的总额
CREATE TABLE table1 (
    RowID INT PRIMARY KEY,
    ID VARCHAR(50), -- 关联键
    Value DECIMAL(18,2) -- 汇总总额
);

-- 细分表table2：存储对应ID的细分项数值
CREATE TABLE table2 (
    DetailID INT PRIMARY KEY,
    ID VARCHAR(50), -- 关联键
    ItemValue DECIMAL(18,2) -- 细分项数值
);

第一步：校验数据一致性 #

在做匹配前，必须确保同一ID下，table1的总额总和等于table2的细分项总和，否则会出现匹配不上的情况。执行以下SQL做校验：

SELECT
    t1.ID,
    SUM(t1.Value) AS 汇总表总额合计,
    SUM(t2.ItemValue) AS 细分项总额合计
FROM table1 t1
JOIN table2 t2 ON t1.ID = t2.ID
GROUP BY t1.ID
HAVING SUM(t1.Value) != SUM(t2.ItemValue);

如果查询返回结果，说明该ID下数据不一致，需要先修正数据再进行后续匹配。

第二步：实现RowID映射（分两种场景） #

场景1：细分项按顺序累积匹配总额 #

如果table2的细分项是按顺序对应table1的RowID总额（比如table1的RowID=1对应table2前n项的和，RowID=2对应接下来m项的和），可以用累积求和窗口函数来实现：

WITH 细分项累积求和 AS (
    SELECT
        DetailID,
        ID,
        ItemValue,
        -- 按ID分组，按DetailID排序，计算累积和
        SUM(ItemValue) OVER (PARTITION BY ID ORDER BY DetailID) AS 累计总值
    FROM table2
),
汇总表累积求和 AS (
    SELECT
        RowID,
        ID,
        Value,
        -- 按ID分组，按RowID排序，计算累积总额
        SUM(Value) OVER (PARTITION BY ID ORDER BY RowID) AS 累计总额
    FROM table1
)
-- 匹配累计总值等于累计总额的记录，得到RowID映射
SELECT
    t2.DetailID,
    t2.ID,
    t2.ItemValue,
    t1.RowID
FROM 细分项累积求和 t2
JOIN 汇总表累积求和 t1
    ON t2.ID = t1.ID
    AND t2.累计总值 = t1.累计总额;

这个方案适合有明确顺序的细分项匹配，比如按时间、按明细ID排序的场景。

场景2：细分项任意组合匹配总额（子集和问题） #

如果table2的细分项可以任意组合来凑出table1的总额，这属于子集和问题，SQL处理起来会复杂一些，推荐用递归CTE来枚举可能的组合：

WITH RECURSIVE 细分项组合 AS (
    SELECT
        DetailID,
        ID,
        ItemValue,
        CAST(DetailID AS VARCHAR(1000)) AS 组合明细ID,
        ItemValue AS 组合总和,
        1 AS 层级
    FROM table2
    UNION ALL
    SELECT
        t2.DetailID,
        t2.ID,
        t2.ItemValue,
        CONCAT(c.组合明细ID, ',', t2.DetailID),
        c.组合总和 + t2.ItemValue,
        c.层级 + 1
    FROM 细分项组合 c
    JOIN table2 t2
        ON c.ID = t2.ID
        AND t2.DetailID > c.DetailID -- 避免重复组合
)
-- 匹配组合总和等于table1的Value，得到对应RowID
SELECT
    c.组合明细ID,
    c.ID,
    c.组合总和,
    t1.RowID
FROM 细分项组合 c
JOIN table1 t1
    ON c.ID = t1.ID
    AND c.组合总和 = t1.Value;

⚠️ 注意：这个方案在table2数据量大时性能会很差，因为子集和是NP难问题。如果数据量较大，建议用Python、Java等编程语言来实现，效率会更高。

补充说明 #

如果你的场景还有特殊规则（比如同一个总额对应多个可能的细分组合需要优先匹配某类项），可以再补充细节，我再帮你调整方案！

内容的提问来源于stack exchange，提问作者Mat Richardson