存储过程问题：按间隔将@AdsTable记录插入@SalesAndAdsTable

你遇到的前两条广告同时插入的问题，核心原因在于现有代码的计数逻辑顺序错误、整数除法精度丢失，以及未处理广告数量大于销售数量的异常场景：

1. 计数顺序颠倒：现有代码先判断广告插入条件，再递增销售计数，导致触发时机偏移，比如本该在第X条销售后插入广告，实际会延迟到第X+1条销售后触发。
2. 整数除法误差：@SalesTableCnt/@AdsTableCnt是整数除法，会丢失小数部分，导致间隔计算不准确（比如10条销售、3条广告，整数除法得到3，但实际需要每3-4条销售插入一条广告）。
3. 零间隔异常：当广告数量≥销售数量时，整数除法得到0，此时IF @OffSet = @SaleCnt会在@SaleCnt=0时反复触发，造成多条广告连续插入。

方案一：修正WHILE循环逻辑 #

调整计数顺序、修正间隔计算，并处理剩余广告：

-- 计算准确的插入间隔：向上取整确保所有广告都能均匀插入，广告数为0则跳过
SET @OffSet = CASE WHEN @AdsTableCnt = 0 THEN 0 ELSE CEILING(CAST(@SalesTableCnt AS FLOAT)/@AdsTableCnt) END;
SET @SaleCnt = 0;
SET @AdCnt = 0;

WHILE (SELECT COUNT(*) FROM @SalesTable WHERE processed = 0) > 0
BEGIN
    -- 获取下一条未处理的销售记录
    SELECT TOP 1 @SaleId = saleid FROM @SalesTable WHERE processed = 0 ORDER BY dateadded DESC;

    -- 插入并标记销售记录
    INSERT INTO @SalesAndAdsTable SELECT * FROM @SalesTable WHERE saleid = @SaleId;
    UPDATE @SalesTable SET processed = 1 WHERE saleid = @SaleId;

    -- 先递增销售计数，再判断是否插入广告
    SET @SaleCnt = @SaleCnt + 1;

    -- 满足间隔条件且还有未处理广告时插入
    IF @OffSet > 0 AND @SaleCnt = @OffSet AND (SELECT COUNT(*) FROM @AdsTable WHERE processed = 0) > 0
    BEGIN
        SELECT TOP 1 @AdId = saleid FROM @AdsTable WHERE processed = 0 ORDER BY dateadded DESC;
        
        INSERT INTO @SalesAndAdsTable SELECT * FROM @AdsTable WHERE saleid = @AdId;
        UPDATE @AdsTable SET processed = 1 WHERE saleid = @AdId;

        -- 重置销售计数，更新广告计数
        SET @SaleCnt = 0;
        SET @AdCnt = @AdCnt + 1;
    END
END

-- 处理销售完成后剩余的广告
WHILE (SELECT COUNT(*) FROM @AdsTable WHERE processed = 0) > 0
BEGIN
    SELECT TOP 1 @AdId = saleid FROM @AdsTable WHERE processed = 0 ORDER BY dateadded DESC;
    INSERT INTO @SalesAndAdsTable SELECT * FROM @AdsTable WHERE saleid = @AdId;
    UPDATE @AdsTable SET processed = 1 WHERE saleid = @AdId;
END

方案二：基于集合的方法（推荐，效率更高） #

抛弃WHILE循环，通过行号关联实现均匀插入，性能更优且逻辑更清晰：

-- 给销售记录编行号
WITH SalesWithRowNum AS (
    SELECT *, 
           ROW_NUMBER() OVER(ORDER BY dateadded DESC) AS RowNum
    FROM @SalesTable
),
-- 给广告记录编行号并计算插入位置
AdsWithRowNum AS (
    SELECT *, 
           ROW_NUMBER() OVER(ORDER BY dateadded DESC) AS RowNum,
           -- 计算每个广告应插入在第几条销售之后
           CEILING(CAST(@SalesTableCnt AS FLOAT)/@AdsTableCnt) * RowNum AS InsertAfterRow
    FROM @AdsTable
),
-- 合并销售与广告数据，按插入顺序排序
MergedData AS (
    SELECT *, RowNum AS SortKey FROM SalesWithRowNum
    UNION ALL
    SELECT *, InsertAfterRow AS SortKey FROM AdsWithRowNum
)
-- 按顺序插入目标表
INSERT INTO @SalesAndAdsTable
SELECT *
FROM MergedData
ORDER BY SortKey;

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