SQL:如何将Oracle表中的100,000条记录分成5个块? [英] SQL: How would you split a 100,000 records from a Oracle table into 5 chunks?

问题描述

我想弄清楚如何将具有100万个以上记录的表中的前100,000条记录拆分为5(五)个20,000条记录块放入一个文件中? 也许有些SQL将为20,000条记录的每5个块获取最小和最大rowid或主ID，因此我可以将最小和最大值放入变量中，并将其传递到SQL中，并在where子句中使用BETWEEN SQL.

I'm trying to figure out away to split the first 100,000 records from a table that has 1 million+ records into 5 (five) 20,000 records chunks to go into a file? Maybe some SQL that will get the min and max rowid or primary id for each 5 chunks of 20,000 records, so I can put the min and max value into a variable and pass it into the SQL and use a BETWEEN in the where clause to the SQL.

可以做到吗?

我正在使用Oracle 11g数据库.

I'm on an Oracle 11g database.

谢谢.

推荐答案

如果只想将值1-5分配给大小基本相等的组，则使用ntile():

If you just want to assign values 1-5 to basically equal sized groups, then use ntile():

select t.*, ntile(5) over (order by NULL) as num
from (select t.*
      from t
      where rownum <= 100000
     ) t;

如果要插入5个不同的表中，请使用insert all:

If you want to insert into 5 different tables, then use insert all:

insert all
    when num = 1 then into t1
    when num = 2 then into t2
    when num = 3 then into t3
    when num = 4 then into t4
    when num = 5 then into t5
    select t.*, ntile(5) over (order by NULL) as num
    from (select t.*
          from t
          where rownum <= 100000
         ) t;

这篇关于SQL:如何将Oracle表中的100,000条记录分成5个块?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！