如何实现SQL表列单独随机化并关联指定列组合？

嘿，我来帮你搞定这个需求！你之前用UNION的思路走偏啦——UNION是用来合并两个结果集的行，而我们需要的是把不同列的随机值组合到同一行里，同时保证first_name和gender始终绑定。

核心思路 #

我们需要把要关联的列（first_name+gender）作为一个整体随机，而last_name和age各自独立随机，最后通过行号和原表的ID对应起来，生成每行的随机组合。

以PostgreSQL为例的实现代码 #

WITH random_first_gender AS (
    -- 将first_name和gender绑定，随机排序后生成行号
    SELECT 
        first_name, 
        gender,
        ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
    FROM Person
),
random_last_name AS (
    -- 单独随机化last_name并生成行号
    SELECT 
        last_name,
        ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
    FROM Person
),
random_age AS (
    -- 单独随机化age并生成行号
    SELECT 
        age,
        ROW_NUMBER() OVER (ORDER BY RANDOM()) AS rn
    FROM Person
)
-- 将原表ID与三个随机结果集的行号关联，得到最终随机数据
SELECT 
    p.ID,
    rfg.first_name,
    rln.last_name,
    ra.age,
    rfg.gender
FROM Person p
JOIN random_first_gender rfg ON p.ID = rfg.rn
JOIN random_last_name rln ON p.ID = rln.rn
JOIN random_age ra ON p.ID = ra.rn;

代码解释 #

1. random_first_gender CTE：把first_name和gender作为一组，用RANDOM()随机排序后生成行号，确保这两列始终关联在一起随机。
2. random_last_name和random_age CTE：分别对last_name和age单独随机排序并生成行号，实现独立随机化。
3. 最终关联：通过原表的ID和三个CTE的行号rn关联，让每个ID对应一组随机组合，同时保证first_name和gender的绑定关系。

其他数据库适配（比如MySQL） #

如果用MySQL，写法类似，只是生成行号的方式可以用变量：

SET @rn := 0;
WITH random_first_gender AS (
    SELECT 
        first_name, 
        gender,
        (@rn := @rn + 1) AS rn
    FROM Person
    ORDER BY RAND()
),
random_last_name AS (
    SELECT 
        last_name,
        (@rn := @rn + 1) AS rn
    FROM Person
    ORDER BY RAND()
),
random_age AS (
    SELECT 
        age,
        (@rn := @rn + 1) AS rn
    FROM Person
    ORDER BY RAND()
)
SELECT 
    p.ID,
    rfg.first_name,
    rln.last_name,
    ra.age,
    rfg.gender
FROM Person p
JOIN random_first_gender rfg ON p.ID = rfg.rn
JOIN random_last_name rln ON p.ID = rln.rn
JOIN random_age ra ON p.ID = ra.rn;

为什么你的UNION方法不行？ #

UNION会把两个查询的结果行合并成一个结果集，比如第一个查ID, first_name, gender，第二个查ID, last_name, age，合并后会出现两种结构的行，完全不是我们需要的“一行里包含所有随机列”的效果，所以这种场景下UNION不适用。

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