sql - 在行之间随机排列一列

Question

如何有效地打乱大型(1m 到 5m 记录)表的内容？已知该列具有唯一值，但您可以假设为此目的删除了所有约束。我的头痛主要是因为我正在更新我选择的同一列。我的目标是使用 PL/SQL 做到这一点，以便我可以以编程方式执行其他操作，例如记录或更新其他表。

**Original table:**
+----+-----------+
| id | fname     |
+----+-----------+
|  1 | mike      |
|  2 | ricky     |
|  3 | jane      |
|  4 | august    |
|  6 | dave      |
|  9 | Jérôme    |
+----+-----------+

**Possible output:**
+----+-----------+
| id | fname     |
+----+-----------+
|  1 | dave      |
|  2 | jane      |
|  3 | mike      |
|  4 | ricky     |
|  6 | Jérôme    |
|  9 | august    |
+----+-----------+

我最近的尝试是创建一个使用 over (order by dbms_random.value) 的游标。并尝试根据 rownum 进行合并或更新。也许我可以通过创建各种临时表来绕过修改自我约束？我相当有信心 Oracle 有一些奇特的方法来做到这一点，但我的 SQL 能力仅限于基本的 CRUD 命令。

完整的解决方案在这里，基于戈登的回答:

merge into t
using (
select t.id, t2.name
from (select t.*, rownum as seqnum
      from t
     ) t join
     (select t.*, row_number() over (order by dbms_random.value) as seqnum
      from t
     ) t2
     on t.seqnum = t2.seqnum
) src
on (t.id = src.id)
when matched then update set t.name = src.name;

Answer 1

您可以使用随机行号进行自连接:

select t.id, t2.name
from (select t.*, row_number() over (order by dbms_random.value) as seqnum
      from t
     ) t join
     (select t.*, row_number() over (order by dbms_random.value) as seqnum
      from t
     ) t2
     on t.seqnum = t2.seqnum;

实际上，您不需要将两者都随机化:

select t.id, t2.name
from (select t.*, rownum as seqnum
      from t
     ) t join
     (select t.*, row_number() over (order by dbms_random.value) as seqnum
      from t
     ) t2
     on t.seqnum = t2.seqnum;