SQL Server 2019中客户产品清单的产品捆绑实现方案问询

针对你的需求，推荐用集合计数匹配法优化实现，避免字符串拼接的性能和准确性问题，同时完美满足「账户必须包含捆绑所有产品才算匹配」的规则。以下是具体实现方案：

假设表结构 #

先明确基础表定义（如果你的表名/字段不同，替换即可）：

-- 客户产品库存表：记录每个账户拥有的产品
CREATE TABLE CustomerInventory (
    AccountID INT,
    ProductID INT
);

-- 捆绑参考表：记录每个捆绑包含的产品
CREATE TABLE Bundles (
    BundleID INT,
    ProductID INT
);

核心SQL实现 #

通过CTE分步处理，逻辑清晰且性能高效（适配1300行的数据集完全无压力）：

WITH AccountProductCounts AS (
    -- 步骤1：计算每个账户的总产品数量
    SELECT 
        AccountID,
        COUNT(DISTINCT ProductID) AS TotalProducts
    FROM CustomerInventory
    GROUP BY AccountID
),
BundleProductCounts AS (
    -- 步骤2：计算每个捆绑要求的产品数量
    SELECT 
        BundleID,
        COUNT(DISTINCT ProductID) AS BundleProductCount
    FROM Bundles
    GROUP BY BundleID
),
AccountBundleMatches AS (
    -- 步骤3：匹配账户与捆绑：账户拥有捆绑的所有产品
    SELECT 
        ci.AccountID,
        b.BundleID
    FROM CustomerInventory ci
    JOIN Bundles b ON ci.ProductID = b.ProductID
    GROUP BY ci.AccountID, b.BundleID
    -- 关键判断：账户匹配到的该捆绑产品数 = 捆绑要求的总产品数
    HAVING COUNT(DISTINCT ci.ProductID) = (SELECT BundleProductCount FROM BundleProductCounts WHERE BundleID = b.BundleID)
),
AccountBundleProducts AS (
    -- 步骤4：计算每个账户匹配到的所有捆绑产品的去重数量
    SELECT 
        abm.AccountID,
        COUNT(DISTINCT b.ProductID) AS BundledProductCount
    FROM AccountBundleMatches abm
    JOIN Bundles b ON abm.BundleID = b.BundleID
    GROUP BY abm.AccountID
)
-- 最终结果：账户ID、匹配的捆绑ID、非捆绑产品数量
SELECT 
    apc.AccountID,
    -- 若一个账户匹配多个捆绑，用逗号分隔合并显示
    STRING_AGG(abm.BundleID, ', ') AS MatchedBundleIDs,
    -- 非捆绑产品数 = 总产品数 - 捆绑产品数（无匹配时直接取总产品数）
    apc.TotalProducts - ISNULL(abp.BundledProductCount, 0) AS UnbundledProductCount
FROM AccountProductCounts apc
LEFT JOIN AccountBundleMatches abm ON apc.AccountID = abm.AccountID
LEFT JOIN AccountBundleProducts abp ON apc.AccountID = abp.AccountID
GROUP BY apc.AccountID, apc.TotalProducts, abp.BundledProductCount
ORDER BY apc.AccountID;

方案优势 #

• 准确性：用集合计数判断，避免字符串拼接可能出现的歧义（比如产品ID包含特殊字符、多位数拼接混淆）
• 灵活性：支持一个账户匹配多个捆绑的场景，用STRING_AGG合并结果；若需每个捆绑单独成行，可调整为单独输出
• 性能：针对1300行的数据集，CTE的聚合计算几乎无性能损耗，SQL Server 2019会自动优化执行计划
• 去重处理：全程用COUNT(DISTINCT)处理，避免库存重复记录或捆绑产品重叠导致的计数错误

可选调整 #

如果需要每个捆绑单独一行显示（而非合并），可修改最终查询为：

SELECT 
    apc.AccountID,
    abm.BundleID AS MatchedBundleID,
    apc.TotalProducts - abp.BundledProductCount AS UnbundledProductCount
FROM AccountProductCounts apc
JOIN AccountBundleMatches abm ON apc.AccountID = abm.AccountID
JOIN AccountBundleProducts abp ON apc.AccountID = abp.AccountID
ORDER BY apc.AccountID, abm.BundleID;

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