如何使用SQL为每位员工随机选取3条无重复的Tracking ID

完全可以实现，核心用分组随机排序+窗口函数即可，以下是具体实现方法：

前提假设 #

示例数据存储在名为employee_tracking的表中，包含Associate（员工姓名）和Tracking ID（跟踪ID）两个字段。

基础实现代码（满足通用需求） #

WITH ranked_tracking AS (
    SELECT
        Associate,
        [Tracking ID],
        -- 按员工分组，组内通过随机GUID实现乱序排序
        ROW_NUMBER() OVER (PARTITION BY Associate ORDER BY NEWID()) AS row_num
    FROM employee_tracking
)
SELECT Associate, [Tracking ID]
FROM ranked_tracking
-- 每个员工仅取排序前3的记录
WHERE row_num <= 3
ORDER BY Associate;

方案说明 #

• PARTITION BY Associate 按员工维度分组，保证每个员工的记录独立排序，不会互相干扰
• ORDER BY NEWID() 是SQL Server原生的随机排序方法，每次执行都会生成不同的随机排序结果
• ROW_NUMBER() 生成的序号在同组内不会重复，因此最终选出的3条跟踪ID必然是不重复的
• 如果某员工名下的跟踪ID不足3条，该逻辑会返回该员工名下所有的跟踪ID，无需额外处理边界情况

可选优化：仅保留有至少3条跟踪ID的员工 #

如果要求只审计满3条跟踪ID的员工，可以加一层过滤逻辑：

WITH valid_associate AS (
    -- 先筛选出跟踪ID数量≥3的员工
    SELECT Associate
    FROM employee_tracking
    GROUP BY Associate
    HAVING COUNT([Tracking ID]) >= 3
),
ranked_tracking AS (
    SELECT
        et.Associate,
        et.[Tracking ID],
        ROW_NUMBER() OVER (PARTITION BY et.Associate ORDER BY NEWID()) AS row_num
    FROM employee_tracking et
    INNER JOIN valid_associate va ON et.Associate = va.Associate
)
SELECT Associate, [Tracking ID]
FROM ranked_tracking
WHERE row_num <= 3
ORDER BY Associate;

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