GaussDB(DWS)中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;

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;

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

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;

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;

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;

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;

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;

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;

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;

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;

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;

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

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

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

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

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

select id from tb_recursive order by id desc limit 1;

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

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

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;

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

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

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

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

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

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

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

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

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

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

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;