PostgreSQL 有生成序列的函数,可以方便的进行造数据。这里我用ORACLE实现了PGSQL的generate_series函数功能。
成都创新互联-专业网站定制、快速模板网站建设、高性价比孝南网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式孝南网站制作公司更省心,省钱,快速模板网站建设找我们,业务覆盖孝南地区。费用合理售后完善,十多年实体公司更值得信赖。
POSTGRESQL.
t_girl=# select * from generate_series(1,10); generate_series ----------------- 1 2 3 4 5 6 7 8 9 10 (10 行记录) 时间:1.290 ms t_girl=# select * from generate_series(1,10,2); generate_series ----------------- 1 3 5 7 9 (5 行记录) 时间:0.431 ms t_girl=# select * from generate_series(1,10,3); generate_series ----------------- 1 4 7 10 (4 行记录) 时间:0.879 ms t_girl=# select * from generate_series(2,10,3); generate_series ----------------- 2 5 8 (3 行记录) 时间:0.867 ms t_girl=# select count(*) from generate_series(1,1000); count ------- 1000 (1 行记录) 时间: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 行记录) 时间: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 行。 已用时间: 00: 00: 00.02 12:10:36 SQL> select * from table(ytt_generate_series(1,10,2)); COLUMN_VALUE ------------ 1 3 5 7 9 已用时间: 00: 00: 00.00 12:10:54 SQL> select * from table(ytt_generate_series(1,10,3)); COLUMN_VALUE ------------ 1 4 7 10 已用时间: 00: 00: 00.00 12:10:56 SQL> select * from table(ytt_generate_series(2,10,3)); COLUMN_VALUE ------------ 2 5 8 已用时间: 00: 00: 00.02 12:11:04 SQL> select count(*) from table(ytt_generate_series(1,1000)); COUNT(*) ---------- 1000 已用时间: 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 行。 已用时间: 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; /