GaussDB(DWS)中Oracle不兼容语法修改集

1、递归语法

单表递归

Oracle：

create table tb_recursive(id int,parent_id int);

insert into tb_recursive values(0,'');

insert into tb_recursive values(1,0);

insert into tb_recursive values(11,1);

insert into tb_recursive values(12,1);

insert into tb_recursive values(111,11);

insert into tb_recursive values(2,0);

insert into tb_recursive values(21,2);

insert into tb_recursive values(22,2);

insert into tb_recursive values(222,22);

insert into tb_recursive values(211,21);

insert into tb_recursive values(212,21);

select id,parent_id,level lvl from tb_recursive connect by prior id=parent_id start with parent_id is null order by id;

Gauss：

create table tb_recursive(id int,parent_id int);

insert into tb_recursive values(0,'');

insert into tb_recursive values(1,0);

insert into tb_recursive values(11,1);

insert into tb_recursive values(12,1);

insert into tb_recursive values(111,11);

insert into tb_recursive values(2,0);

insert into tb_recursive values(21,2);

insert into tb_recursive values(22,2);

insert into tb_recursive values(222,22);

insert into tb_recursive values(211,21);

insert into tb_recursive values(212,21);

with recursive r1 as (

select id,parent_id,1 as lvl

from tb_recursive

where parent_id is null

union all

select t.id,t.parent_id,t1.lvl+1 as lvl

from tb_recursive t,r1 t1

where t.parent_id=t1.id)

select id,parent_id,lvl from r1 order by id;

Oracle:

select id,parent_id,level lvl from tb_recursive connect by id=prior parent_id start with parent_id is null order by id;

Gauss:

with recursive r1 as (

select id,parent_id,1 as lvl

from tb_recursive

where parent_id is null

union all

select t.id,t.parent_id,t1.lvl+1 as lvl

from tb_recursive t,r1 t1

where t.id=t1.parent_id)

select id,parent_id,lvl from r1 order by id;

sys_connect_by_path

Oracle:

select id,parent_id,sys_connect_by_path(id,' > ') path,level lvl from tb_recursive connect by prior id= parent_id start with parent_id is null order by id;

Gauss:

with recursive r1 as (

select id,parent_id,' > '||id path,1 as lvl

from tb_recursive

where parent_id is null

union all

select t.id,t.parent_id,t1.path||' > '||t.id path,t1.lvl+1 as lvl

from tb_recursive t,r1 t1

where t.parent_id=t1.id)

select id,parent_id,path,lvl from r1 order by id;

order siblings by

Oracle:

select id,parent_id,level lvl from tb_recursive connect by prior id=parent_id start with parent_id is null order siblings by id;

Gauss:

with recursive r1 as (

select id,parent_id,1 as lvl,lpad(id,4,0)||lpad(ctid||xc_node_id::text,20,0) sortno

from tb_recursive

where parent_id is null

union all

select t.id,t.parent_id,t1.lvl+1 as lvl,t1.sortno||lpad(t.id,4,0)||lpad(ctid||xc_node_id::text,20,0) sortno

from tb_recursive t,r1 t1

where t.parent_id=t1.id)

select id,parent_id,lvl from r1 order by sortno;

connect_by_root

Oracle:

select id,parent_id,connect_by_root(parent_id) as rid,level lvl from tb_recursive connect by prior id=parent_id start with parent_id =2 order by id;

Gauss:

with recursive r1 as (

select id,parent_id,parent_id rid,1 as lvl

from tb_recursive

where parent_id =2

union all

select t.id,t.parent_id,t1.rid,t1.lvl+1 as lvl

from tb_recursive t,r1 t1

where t.parent_id=t1.id)

select id,parent_id,rid,lvl from r1 order by id;

connect by前有where

Oracle:

select id,parent_id,level lvl from tb_recursive where parent_id = 1 connect by prior id=parent_id start with parent_id is null order by id;

Gauss:

with recursive r1 as (

select id,parent_id,1 as lvl

from tb_recursive

where parent_id is null

union all

select t.id,t.parent_id,t1.lvl+1 as lvl

from tb_recursive t,r1 t1

where t.parent_id=t1.id)

select id,parent_id,lvl from r1 where parent_id =1 order by id;

2、Rownum

select list中

Oracle:

select id,parent_id,rownum from tb_recursive order by 3;

Gauss:

select id,parent_id,row_number() over() from tb_recursive order by 3;

where条件中

Oracle:

select id,parent_id from tb_recursive where rownum =1 order by id;

Gauss:

select id,parent_id from tb_recursive order by id limit 1;

sql语句中有order by

Oracle:

select id from (select id,parent_id from tb_recursive order by id desc) where rownum =1;

Gauss:

select id from tb_recursive order by id desc limit 1;

sql语句中有count

Oracle:

select count(1) from (select id,parent_id from tb_recursive where id = 1) where rownum =1;

Gauss:

select count(1) from (select id,parent_id from tb_recursive where id = 1 limit 1);

sql语句中有max/min

Oracle:

select max(id) from tb_recursive where parent_id=21 and rownum =1;

select min(id) from tb_recursive where parent_id=21 and rownum =1;

Gauss:

select id from tb_recursive where parent_id=21 order by id limit 1;

3、Max/Min...keep语法

单表max,dense_rank last

Oracle:

select max(id) keep (dense_rank last order by parent_id) from tb_recursive;

Gauss:

select id from tb_recursive order by parent_id desc,id desc limit 1;

单表max,dense_rank first

Oracle:

select max(id) keep (dense_rank first order by parent_id) from tb_recursive;

Gauss:

select id from tb_recursive order by parent_id asc,id desc limit 1;

单表min,dense_rank last

Oracle:

select min(id) keep (dense_rank last order by parent_id) from tb_recursive;

Gauss:

select id from tb_recursive order by parent_id desc,id asc limit 1;

单表min,dense_rank first

Oracle:

select min(id) keep (dense_rank first order by parent_id) from tb_recursive;

Gauss:

select id from tb_recursive order by parent_id asc,id asc limit 1;

4、pivot语法

测试表及数据：

drop table if exists tb_test;

create table tb_test(a int,b int);

insert into tb_test values(1,1),(1,1),(1,2),(1,3),(1,4),(2,1),(2,2),(2,3),(2,2),(2,4),(2,5),(2,6);

Oracle:

select * from

(select a,b,count(*) cnt from tb_test group by a,b) t

pivot (sum(cnt) for a in (1 c,2 d,3 e,4 f));

Gauss:

select b,sum(case a when 1 then cnt end) C,

sum(case a when 2 then cnt end) D,

sum(case a when 3 then cnt end) E,

sum(case a when 4 then cnt end) F

from (select a,b,count(*) cnt from tb_test group by a,b)

group by b;

（完）