如何随机重排Oracle表ORDER列值且不重复，保持NAME列位置不变

原表数据 #

期望结果（示例） #

现有方法的问题 #

• 方法1：Update TEST_TABLE Set ORDER = dbms_random.value(1,4);
  每次更新行时独立生成随机数，无法保证数值不重复，会出现多个行的ORDER值相同的情况。
• 方法2：Update TEST_TABLE Set ORDER = (Select dbms_random.value(1,4) From dual);
  子查询仅执行一次，生成单个随机数，所有行的ORDER值会被设置为同一个数。

正确解决方案 #

可以通过MERGE语句结合行号关联的方式，实现ORDER值的无重复随机重排，同时保持NAME列的记录顺序不变：

MERGE INTO TEST_TABLE t
USING (
    -- 给原表按原有顺序分配固定行号，确保NAME位置对应关系不变
    SELECT 
        NAME,
        ROW_NUMBER() OVER (ORDER BY "ORDER") AS original_row_num
    FROM TEST_TABLE
) orig
ON (t.NAME = orig.NAME)
USING (
    -- 将原ORDER值随机排序后分配行号，生成无重复的随机序列
    SELECT 
        val AS random_order,
        ROW_NUMBER() OVER (ORDER BY dbms_random.value()) AS random_row_num
    FROM (
        SELECT DISTINCT "ORDER" AS val FROM TEST_TABLE
    )
) rand
ON (orig.original_row_num = rand.random_row_num)
WHEN MATCHED THEN UPDATE SET t."ORDER" = rand.random_order;

逻辑说明 #

1. 子查询orig：给原表的每一行按原ORDER列的顺序分配行号，保证每个NAME对应固定的位置标识。
2. 子查询rand：提取原表中所有唯一的ORDER值，通过dbms_random.value()随机排序后分配行号，生成一组无重复的随机序列。
3. MERGE操作：通过行号将原表行与随机序列关联，把随机ORDER值更新到对应的行中，既保证了ORDER值不重复，又维持了NAME的原有顺序。

也可以使用UPDATE语句实现相同效果：

UPDATE TEST_TABLE t
SET "ORDER" = (
    SELECT random_order
    FROM (
        SELECT 
            val AS random_order,
            ROW_NUMBER() OVER (ORDER BY dbms_random.value()) AS rn
        FROM (SELECT DISTINCT "ORDER" AS val FROM TEST_TABLE)
    ) rand
    WHERE rn = (
        SELECT ROW_NUMBER() OVER (ORDER BY "ORDER") FROM TEST_TABLE t2 WHERE t2.NAME = t.NAME
    )
);

注意：ORDER是Oracle的保留关键字，建议用双引号"ORDER"包裹，避免语法冲突。

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