您好,登錄后才能下訂單哦!
PostgreSQL 有生成序列的函數(shù),可以方便的進(jìn)行造數(shù)據(jù)。這里我用ORACLE實(shí)現(xiàn)了PGSQL的generate_series函數(shù)功能。
POSTGRESQL.
t_girl=# select * from generate_series(1,10); generate_series ----------------- 1 2 3 4 5 6 7 8 9 10 (10 行記錄) 時(shí)間:1.290 ms t_girl=# select * from generate_series(1,10,2); generate_series ----------------- 1 3 5 7 9 (5 行記錄) 時(shí)間:0.431 ms t_girl=# select * from generate_series(1,10,3); generate_series ----------------- 1 4 7 10 (4 行記錄) 時(shí)間:0.879 ms t_girl=# select * from generate_series(2,10,3); generate_series ----------------- 2 5 8 (3 行記錄) 時(shí)間:0.867 ms t_girl=# select count(*) from generate_series(1,1000); count ------- 1000 (1 行記錄) 時(shí)間:1.142 ms t_girl=# select to_char(now() - '1 day'::interval * n,'YYYY-MM-DD') as result from generate_series(1,10) g(n); result ------------ 2015-12-08 2015-12-07 2015-12-06 2015-12-05 2015-12-04 2015-12-03 2015-12-02 2015-12-01 2015-11-30 2015-11-29 (10 行記錄) 時(shí)間:17.284 ms
ORACLE:
12:10:34 SQL> select * from table(ytt_generate_series(1,10)); COLUMN_VALUE ------------ 1 2 3 4 5 6 7 8 9 10 已選擇 10 行。 已用時(shí)間: 00: 00: 00.02 12:10:36 SQL> select * from table(ytt_generate_series(1,10,2)); COLUMN_VALUE ------------ 1 3 5 7 9 已用時(shí)間: 00: 00: 00.00 12:10:54 SQL> select * from table(ytt_generate_series(1,10,3)); COLUMN_VALUE ------------ 1 4 7 10 已用時(shí)間: 00: 00: 00.00 12:10:56 SQL> select * from table(ytt_generate_series(2,10,3)); COLUMN_VALUE ------------ 2 5 8 已用時(shí)間: 00: 00: 00.02 12:11:04 SQL> select count(*) from table(ytt_generate_series(1,1000)); COUNT(*) ---------- 1000 已用時(shí)間: 00: 00: 00.13 13:32:13 SQL> select to_char(sysdate - n,'YYYY-MM-DD') from table(ytt_generate_series(1,10)); TO_CHAR(SY ---------- 2015-12-08 2015-12-07 2015-12-06 2015-12-05 2015-12-04 2015-12-03 2015-12-02 2015-12-01 2015-11-30 2015-11-29 已選擇 10 行。 已用時(shí)間: 00: 00: 00.01
附上代碼:
-- Declare result set. create or replace type ytt_num is object ( n number); / create or replace type numbers_table is table of ytt_num; / -- Function body. -- Created by ytt. -- 2015/12/9 create or replace function ytt_generate_series ( f_start_num number := 1, -- Start number. f_end_num number, -- Finish number. f_step_num number := 1 -- Step. ) return numbers_table pipelined is list numbers_table := numbers_table(); i number := 0; j number := 1; begin i := f_start_num; j := 1; -- Increase nested table's size. list.extend(f_end_num); -- Loop begin. while i <= f_end_num loop -- Initlization. list(j) := ytt_num(null); list(j).n := i; pipe row(list(j)); i := i + f_step_num; j := j + 1; end loop; return; end; /
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。