Azure SQL Database中筛选数量总和≥指定值的SQL查询需求

假设你的表名为Inventory，我们可以通过递归CTE生成所有可能的itemId子集，计算每个子集的数量总和，再筛选出符合总和≥输入值@X的组合。同时处理X超出总数量时返回所有记录的场景。

完整SQL语句 #

DECLARE @X INT = 20; -- 替换为你的输入值

-- 计算所有记录的总数量
DECLARE @TotalQty INT;
SELECT @TotalQty = SUM(qty) FROM Inventory;

IF @X > @TotalQty
BEGIN
    -- X超出总数量，返回所有itemId
    SELECT itemId FROM Inventory;
END
ELSE
BEGIN
    -- 递归CTE生成所有可能的itemId子集及对应总和
    WITH ItemSubsets AS (
        -- 基础项：单个item的子集
        SELECT 
            CAST(itemId AS VARCHAR(MAX)) AS ItemIds,
            qty AS TotalQty,
            itemId AS LastItemId
        FROM Inventory
        WHERE qty >= @X -- 先筛选单个item就满足条件的情况
        UNION ALL
        -- 递归生成组合：在已有子集基础上添加后续item
        SELECT 
            CONCAT(IS.ItemIds, ', ', I.itemId),
            IS.TotalQty + I.qty,
            I.itemId
        FROM ItemSubsets IS
        JOIN Inventory I ON I.itemId > IS.LastItemId -- 避免重复组合（如1+2和2+1视为同一组合）
        WHERE IS.TotalQty + I.qty >= @X
    )
    -- 输出所有符合条件的itemId组合
    SELECT ItemIds AS 符合条件的itemId组合, TotalQty AS 数量总和
    FROM ItemSubsets
    ORDER BY TotalQty, ItemIds;
END

场景验证 #

• 当X=20时：会返回所有单个qty≥20的item（item2、3、4、6），以及多个item组合总和≥20的情况（如item1+item2、item1+item3等）。
• 当X=50时：会返回总和≥50的组合，比如1,2,3（总和62）、4（总和113）、2,3,6（总和68）等。
• 当X=500时：由于总数量为208<500，直接返回所有itemId。

说明 #

• 递归CTE通过LastItemId确保每个组合只生成一次（按itemId升序添加），避免重复的组合排列。
• 先筛选单个item满足条件的情况，减少递归计算量。
• 如果需要返回每个组合的itemId列表而非拼接字符串，可以调整为返回表格式的结果（比如用JSON数组或表值函数），但上述示例用字符串拼接更直观展示组合。

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