主从表场景下SQL补全10倍数空行的CTE优化方案需求

咱们直接来解决问题——原有的WHILE循环方案在大数据量下性能拉胯，换成基于集合操作的CTE方案就能完美解决，既满足每个主表ID对应的从表行数凑成10的倍数，又能保证高效性。

核心思路 #

1. 先统计每个MasterID对应的从表现有记录数，算出需要补的行数：用公式(10 - (COUNT(*) % 10)) % 10，这个公式能自动处理“刚好10行就不补”的情况，比如现有6行补4行，现有8行补2行，现有10行补0行。
2. 用递归CTE生成1-10的数字序列（最多需要补9行，所以1-10足够覆盖所有场景），用来生成补行的序号。
3. 把原从表数据和生成的空行做UNION ALL合并，最后按要求排序输出。

完整实现代码 #

-- 初始化测试数据（和你的示例完全一致）
DECLARE @MasterTable TABLE(MasterID INT);
INSERT INTO @MasterTable VALUES(1),(2);

DECLARE @DetailsTable TABLE (ID INT, MasterID INT, DetailsDescription NVARCHAR(255));
-- 插入MasterID=1的6条数据
INSERT INTO @DetailsTable VALUES 
(1,1, 'XXXXX'), (2,1, 'XXXXX'), (3,1, 'XXXXX'),
(4,1, 'XXXXX'), (5,1, 'XXXXX'), (6,1, 'XXXXX');
-- 插入MasterID=2的8条数据
INSERT INTO @DetailsTable VALUES 
(1,2, 'XXXXX'), (2,2, 'XXXXX'), (3,2, 'XXXXX'), (4,2, 'XXXXX'),
(5,2, 'XXXXX'), (6,2, 'XXXXX'), (7,2, 'XXXXX'), (8,2, 'XXXXX');

-- 用CTE实现高效补全逻辑
WITH MasterCounts AS (
    -- 统计每个MasterID的现有记录数和需补行数
    SELECT 
        MasterID,
        CurrentCount = COUNT(*),
        NeedToAdd = (10 - (COUNT(*) % 10)) % 10
    FROM @DetailsTable
    GROUP BY MasterID
),
NumberGenerator AS (
    -- 递归生成1-10的数字序列，用于生成补行的序号
    SELECT n = 1
    UNION ALL
    SELECT n + 1 FROM NumberGenerator WHERE n < 10
)
-- 合并原数据和补的空行
SELECT 
    dt.ID,
    dt.MasterID,
    dt.DetailsDescription
FROM @DetailsTable dt
UNION ALL
SELECT 
    -- 补行的ID：取对应MasterID的最大ID + 数字n，保证ID唯一有序
    (SELECT MAX(ID) FROM @DetailsTable WHERE MasterID = mc.MasterID) + ng.n,
    mc.MasterID,
    DetailsDescription = NULL
FROM MasterCounts mc
JOIN NumberGenerator ng ON ng.n <= mc.NeedToAdd
-- 按要求排序输出
ORDER BY MasterID, ID;

关键细节解释 #

• MasterCounts CTE：核心是计算需补行数的公式，(10 - (COUNT(*) % 10)) % 10能精准处理所有情况——哪怕现有记录数刚好是10的倍数，也不会多补行。
• NumberGenerator CTE：用递归生成数字序列，替代了原方案里的循环生成，是典型的集合式操作，性能远高于逐行循环。
• 补行ID生成：通过取对应MasterID的最大ID加上数字n，保证补行的ID既唯一又和原有数据的ID顺序一致，符合排序要求。

性能优势 #

这个方案是纯集合操作，SQL Server对这类操作的优化程度远高于游标、WHILE循环这类逐行处理逻辑。当你的主从表数据量较大时，性能差异会非常明显——原方案可能需要几秒甚至几分钟，这个方案大概率能在毫秒级完成。

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