SQL两表关联后如何为每条主表记录选取单个随机值

核心场景 #

• 主表为临时表#TempTable，存储10个不重复的GlobalPnID值
• 从表为[dbo].[GlobalPartNumberPortions]，每个GlobalPnID对应多条不同的PortionKey记录
• 目标效果：每个主表GlobalPnID从匹配的从表记录中随机选1条PortionKey值，逐行完成匹配，避免一对多关联导致的结果集行数膨胀。

现有代码缺陷 #

目前使用的直接左连接逻辑，会因为从表的一对多属性，导致单个主表ID对应多行返回，无法实现单值匹配要求：

SELECT  TOP 10 gpnp.PortionKey, tt.GlobalPnID 
from #TempTable tt
LEFT JOIN [dbo].[GlobalPartNumberPortions] gpnp  ON gpnp.GlobalPnId = tt.GlobalPnID

-- tt 为临时主表
-- gpnp 为存储多值的从表

实际可用的实现方案 #

方案1：APPLY关联（SQL Server环境写法更简洁） #

利用OUTER APPLY逐行遍历主表的每个ID，在从表的匹配结果中取1条随机记录，从根源上避免行数膨胀：

SELECT 
  tt.GlobalPnID,
  matched.PortionKey
FROM #TempTable tt
OUTER APPLY (
  SELECT TOP 1 gpnp.PortionKey
  FROM [dbo].[GlobalPartNumberPortions] gpnp
  WHERE gpnp.GlobalPnId = tt.GlobalPnID
  ORDER BY NEWID() -- 实现匹配记录的随机选取
) matched

• 该写法完全兼容原有左连接逻辑：如果某个GlobalPnID在从表中无匹配，依然会保留主表行，PortionKey返回NULL
• 最终结果集行数和主表完全一致，不会出现膨胀

方案2：窗口函数预排序（兼容性更强） #

先通过窗口函数为从表中每个GlobalPnID下的记录打随机行号标记，关联时仅取行号为1的记录：

WITH gpnp_ranked AS (
  SELECT 
    GlobalPnId,
    PortionKey,
    ROW_NUMBER() OVER (
      PARTITION BY GlobalPnId 
      ORDER BY NEWID() -- 同分组内记录随机排序
    ) AS rn
  FROM [dbo].[GlobalPartNumberPortions]
)
SELECT 
  tt.GlobalPnID,
  gr.PortionKey
FROM #TempTable tt
LEFT JOIN gpnp_ranked gr
  ON tt.GlobalPnID = gr.GlobalPnId
  AND gr.rn = 1 -- 仅匹配每个分组下随机排第一的记录

实操注意点 #

• 两种写法都不需要额外加TOP 10限制：因为主表本身仅10条不重复的GlobalPnID，关联后结果固定为10行
• 如果后续需要固定匹配规则而非随机值，只需把ORDER BY NEWID()替换为对应业务排序字段即可
• 如果从表数据量较大，建议在GlobalPnId字段上建立非聚集索引，减少关联查询时的全表扫描开销

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