SQL Server中随机选行至列总和达目标值的实现需求

问题分析 #

你给出的原查询 select t.* from (select q.*,sum(q.price) over(order by newid())as total from Orders q)t where t.total<=45 存在核心问题：它是按随机排序后的前缀累计和筛选行，只能拿到随机顺序下前N行的和不超过目标的结果，无法主动挑选行来精准凑出目标值，也无法保证匹配到刚好等于目标的组合。

下面针对你提出的三个需求场景，给出可行的解决方案：

前置准备：先处理边界场景 #

首先定义目标值，并计算全局统计量，快速处理两种边界情况：

DECLARE @Target INT = 45; -- 替换为你的目标值
DECLARE @TotalAll INT, @MinPrice INT;
SELECT 
    @TotalAll = SUM(price), 
    @MinPrice = MIN(price) 
FROM Orders;

-- 场景3：目标值小于最小单价，返回空结果
IF @Target < @MinPrice
BEGIN
    SELECT * FROM Orders WHERE 1=0;
    RETURN;
END

-- 场景2：目标值大于所有行总价，返回全部行
IF @Target >= @TotalAll
BEGIN
    SELECT * FROM Orders;
    RETURN;
END

场景1：精准匹配目标值的随机组合 #

方法1：递归CTE枚举子集（适合行数≤30的小数据集） #

该方法通过递归枚举所有可能的行组合，筛选出总和等于目标值的组合，再随机返回其中一组：

WITH RecursiveCTE AS (
    -- 锚点：初始化第一行的选/不选状态
    SELECT 
        id,
        price,
        CAST(id AS VARCHAR(MAX)) AS SelectedIds,
        price AS CurrentSum,
        1 AS RowNum
    FROM Orders
    WHERE id = (SELECT MIN(id) FROM Orders)
    
    UNION ALL
    
    -- 递归：逐行处理，选择或跳过当前行（避免重复组合）
    SELECT 
        o.id,
        o.price,
        -- 若选当前行后总和不超目标，则记录ID；否则保持原ID列表
        CASE WHEN r.CurrentSum + o.price <= @Target 
             THEN r.SelectedIds + ',' + CAST(o.id AS VARCHAR) 
             ELSE r.SelectedIds END,
        -- 更新累计总和
        CASE WHEN r.CurrentSum + o.price <= @Target 
             THEN r.CurrentSum + o.price 
             ELSE r.CurrentSum END,
        r.RowNum + 1
    FROM RecursiveCTE r
    JOIN Orders o ON o.id > r.id -- 按ID递增处理，避免重复组合（如[1,2]和[2,1]视为同一组）
)
-- 从所有符合条件的组合中随机选一组，关联原表返回数据
SELECT o.*
FROM Orders o
JOIN (
    SELECT TOP 1 SelectedIds
    FROM RecursiveCTE
    WHERE CurrentSum = @Target
    ORDER BY NEWID() -- 随机选择组合
) s ON CHARINDEX(',' + CAST(o.id AS VARCHAR) + ',', ',' + s.SelectedIds + ',') > 0;

方法2：随机循环尝试（适合中等数据集，允许一定概率无结果） #

如果数据集行数较多（超过30行，递归会性能爆炸），可以用循环随机采样行组合，直到找到总和等于目标的组合：

DECLARE @Selected TABLE (id INT, price INT);
DECLARE @CurrentSum INT = 0;
DECLARE @MaxAttempts INT = 1000; -- 设置最大尝试次数，避免无限循环
DECLARE @Attempts INT = 0;

WHILE @CurrentSum != @Target AND @Attempts < @MaxAttempts
BEGIN
    DELETE FROM @Selected;
    -- 随机选择行（可调整NEWID()对比的概率值，0.5表示约50%的行被选中）
    INSERT INTO @Selected
    SELECT id, price
    FROM Orders
    WHERE NEWID() < CAST(0.5 AS VARBINARY(8))
    ORDER BY NEWID();
    
    SELECT @CurrentSum = SUM(price) FROM @Selected;
    SET @Attempts = @Attempts + 1;
END

-- 找到符合条件的组合则返回，否则返回空
IF @CurrentSum = @Target
BEGIN
    SELECT * FROM Orders o WHERE o.id IN (SELECT id FROM @Selected);
END
ELSE
BEGIN
    SELECT * FROM Orders WHERE 1=0;
END

注意事项 #

1. 递归CTE的性能限制：当Orders表行数超过30时，子集数量会呈2^n指数增长，导致查询超时，此时建议用随机循环法。
2. 随机循环法的不确定性：如果不存在刚好等于目标值的组合，循环结束后会返回空，你可以根据实际需求修改逻辑（比如返回最接近目标且不超过的组合）。

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