SQL Server 两表关联为table1每行匹配不同table2随机行高效方案问询

核心思路 #

通过给两个表分别生成序号做匹配，避免笛卡尔积运算，时间复杂度仅为O(M+N)（M为Table1行数，N为Table2行数），适配百万级数据量场景。

实现代码（适配SQL Server，其他数据库仅需替换随机函数即可） #

WITH t2_indexed AS (
    -- 给Table2生成从0开始的连续序号
    SELECT 
        [char],
        ROW_NUMBER() OVER(ORDER BY (SELECT 1)) - 1 AS t2_idx
    FROM table2
),
t1_matched AS (
    -- 给Table1每行生成随机匹配序号，对Table2总行数取模
    SELECT
        num,
        ABS(CHECKSUM(NEWID())) % (SELECT COUNT(*) FROM table2) AS match_idx
    FROM table1
)
-- 关联得到最终结果
SELECT t1.num, t2.[char]
FROM t1_matched t1
INNER JOIN t2_indexed t2 
    ON t1.match_idx = t2.t2_idx

方案优势 #

• 性能极高：仅对两张表各做一次全表扫描，关联操作为低开销的哈希匹配，百万级Table1和万级Table2运算可在几秒内完成，完全避免交叉连接产生的百亿级中间数据
• 满足业务要求：随机值均匀分布，天然不会出现所有Table1行都匹配同一个Table2值的情况
• 适配性强：替换随机函数即可适配其他数据库，MySQL用FLOOR(RAND() * (SELECT COUNT(*) FROM table2))，PostgreSQL用FLOOR(RANDOM() * (SELECT COUNT(*) FROM table2))即可

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