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;
- /