SQL Server 2016：如何编写查询从同一张表生成多组唯一ID配对？

针对你的人员随机配对需求，我来分享几个可行的批量实现思路，同时优化你现有的查询代码，完全避开游标来解决性能问题。

先梳理核心需求的实现关键点 #

你的几个核心参数其实可以通过窗口函数+批量筛选来高效满足，不用逐个遍历：

• 唯一配对：靠表变量的UNIQUE NONCLUSTERED ([assessorID], [authorID])约束兜底，同时生成逻辑里避免重复
• 固定配对数量：需要同时控制「评估者发起的配对数」和「被评估者接收的配对数」
• 随机生成：用NEWID()做排序依据，保证随机性
• 禁止自配对：在筛选条件里直接排除e1.personID = e2.personID的情况

优化后的实现方案 #

我先给出调整后的完整代码，再拆解每个部分的作用：

DECLARE @assessmentID INT = [N]; -- 替换为你的评估ID
DECLARE @requiredPairs INT = 10; -- 每个ID作为评估者/被评估者的配对数

DECLARE @assessmentPairs TABLE(
 assessorID INT,
 authorID INT,
 assessorCounter INT,
 authorCounter INT
 UNIQUE NONCLUSTERED ([assessorID], [authorID]) -- 强制唯一配对
);

-- 第一步：先筛选当前评估对应的合格人员，缩小后续计算范围
WITH EligiblePeople AS (
    SELECT DISTINCT p.personID
    FROM People p
    JOIN Assessments a ON p.courseOfferingID = a.courseOfferingID
    WHERE a.assessmentID = @assessmentID
),
-- 第二步：生成所有有效配对并做双向随机排序
AllValidPairs AS (
    SELECT 
        e1.personID AS assessorID,
        e2.personID AS authorID,
        -- 按评估者分组随机排序，用于控制评估者发起的配对数
        ROW_NUMBER() OVER(PARTITION BY e1.personID ORDER BY NEWID()) AS assessorRank,
        -- 按被评估者分组随机排序，用于控制被评估者接收的配对数
        ROW_NUMBER() OVER(PARTITION BY e2.personID ORDER BY NEWID()) AS authorRank
    FROM EligiblePeople e1
    CROSS JOIN EligiblePeople e2
    WHERE e1.personID <> e2.personID -- 排除自配对
)
-- 第三步：插入满足双向数量要求的配对
INSERT INTO @assessmentPairs (assessorID, authorID, assessorCounter, authorCounter)
SELECT 
    assessorID,
    authorID,
    assessorRank AS assessorCounter,
    authorRank AS authorCounter
FROM AllValidPairs
WHERE 
    assessorRank <= @requiredPairs
    AND authorRank <= @requiredPairs;

-- 可选：如果部分ID的配对数未达标，批量补充缺口
WITH AssessorNeeds AS (
    SELECT 
        personID AS assessorID,
        @requiredPairs - COUNT(*) AS needed
    FROM @assessmentPairs
    GROUP BY personID
    HAVING COUNT(*) < @requiredPairs
),
RemainingValidPairs AS (
    SELECT 
        e1.personID AS assessorID,
        e2.personID AS authorID,
        ROW_NUMBER() OVER(PARTITION BY e1.personID ORDER BY NEWID()) AS rn
    FROM EligiblePeople e1
    JOIN AssessorNeeds an ON e1.personID = an.assessorID
    CROSS JOIN EligiblePeople e2
    WHERE 
        e1.personID <> e2.personID
        -- 排除已存在的配对
        AND NOT EXISTS (SELECT 1 FROM @assessmentPairs ap WHERE ap.assessorID = e1.personID AND ap.authorID = e2.personID)
        -- 保证被评估者不超过配对上限
        AND (SELECT COUNT(*) FROM @assessmentPairs ap WHERE ap.authorID = e2.personID) < @requiredPairs
)
INSERT INTO @assessmentPairs (assessorID, authorID, assessorCounter, authorCounter)
SELECT 
    assessorID,
    authorID,
    (SELECT COUNT(*) FROM @assessmentPairs ap WHERE ap.assessorID = rp.assessorID) + rn AS assessorCounter,
    (SELECT COUNT(*) FROM @assessmentPairs ap WHERE ap.authorID = rp.authorID) + 1 AS authorCounter
FROM RemainingValidPairs
WHERE rn <= needed;

-- 查看最终结果
SELECT * FROM @assessmentPairs ORDER BY authorID, assessorID;

关键优化点说明 #

1. 预筛选合格人员：用EligiblePeople CTE先把当前评估对应的人员提出来，避免对整个People表做CROSS JOIN，大幅提升性能。
2. 双向随机排序：通过两个ROW_NUMBER()窗口函数，分别控制评估者和被评估者的配对数量，确保两边都不会超过指定值。
3. 去掉冗余的GROUP BY：你的原代码里的GROUP BY是多余的——CROSS JOIN已经生成了唯一的(assessorID, authorID)组合（排除自配对后），除非People表有重复的personID，否则不需要分组。
4. 缺口补充逻辑：如果第一次筛选后，有些评估者的配对数没达到要求，用批量查询从剩余有效配对里随机补充，同时保证被评估者的数量不超标。

其他可行思路 #

如果你的人员规模特别大（比如上千人），可以考虑：

• 分批次生成：用TOP结合随机排序，每次生成一部分配对，避免一次性生成过大的数据集。
• 基于随机数的配对：给每个人员分配一个随机数，然后通过匹配随机数的范围来生成配对，比如让评估者匹配随机数在某个区间的被评估者，这种方式性能更高，但需要额外的逻辑平衡配对数量。

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