目前我加工了一下，可以運行，但是有個地方怪怪的我還在想
with t1 as (
select rownum rn, t.* from (select * from [mytable] where [mycol] < 1000049753 order by [mycol]) t
),
t2 (cnt, i, j) as (
select 1 , 1 , 1
from dual
union all
select cnt + 1
, case
when mod(i, 9) = 0 then 1
else i + 1
end
, case
when mod(i, 9) = 0 then j + 1
else j
end
from t2
where cnt < (select count(1) from t1)
),
t3 as (
select j, [fn_mysequence] serno from t2 group by j order by j
)
select t3.serno
, t2.cnt, t2.i, t2.j
, t1.*
from t1, t2, t3
where t2.cnt = t1.rn and t2.j=t3.j
order by t1.rn
;

我這樣select出來 我發現我取的serno會沒辦法照順序跳號

但是這個select的結果insert到我的記錄表serno的順序就會正常了
就是看起來是order by serno, i

你沒有指定 order by 順序的話, 每次查詢的順序是不一定的,連帶
rownum 這個虛擬欄位也是會變化的,他是依照查詢的結果賦予的.
所以你查詢的結果,應該設法都查完後, 一次性的排序,看是要用 rownum,或是 row_number() over() 來取得流水號.
可以採用 喵凹咿唉思嗯 大大的範例裡的運算式,那樣會更簡潔有力.

了解謝謝 我又試了一下 最後改成這樣打算先交差

with t1 as (
select * from [mytable] where [mycol] < 1000049753 order by [mycol]
),
t2 (cnt, i, j) as (
select 1 , 1 , 1
from dual
union all
select cnt + 1
, case
when mod(i, 9) = 0 then 1
else i + 1
end
, case
when mod(i, 9) = 0 then j + 1
else j
end
from t2
where cnt < (select count(1)+1 from t1)
),
t3 as (
select j, [myfunction] serno from t2 group by j order by j
)
select
t3.serno, t2., u1.
from (select rownum rn, t.* from t1 t) u1
join t2 on u1.rn = t2.cnt
join t3 on t2.j = t3.j
--order by t2.cnt 不能加這行
;

這樣順序目前看起來是沒亂跳
感謝各路大大
神演算法

是一個好作法，但我目前需要去call那個公用取sequence function