DBMaster有没有关于像Oracle语法“SELECT id FROM groups START WITH id=2 CONNECT BY PRIOR parent_id = id”这样的解决方法?如果没有,有没有别的办法得到相同的结果?(DBMR1954)
DBMaster不支持回归SQL,但是通过多个个别SQL,也能得到相同的结果。
例如,用户通过临时表运行以下的步骤:
drop table t1;
drop table t2;
create table t1 (c1 int, c2 int);
create table t2 (c1 int, c2 int);
insert into t1 values (1, 11);
insert into t1 values (1, 12);
insert into t1 values (2, 21);
insert into t1 values (11, 111);
insert into t1 values (11, 112);
insert into t1 values (12, 121);
insert into t1 values (12, 122);
insert into t1 values (12, 123);
insert into t1 values (13, 131);
insert into t1 values (14, 141);
insert into t1 values (121, 1211);
insert into t1 values (123, 1231);
insert into t1 values (131, 1311);
insert into t1 values (1231, 12311);
insert into t1 values (12311, 123111);
/*
SELECT c1 FROM t1
START WITH c1=1 CONNECT BY
PRIOR c1 = c2;
RecursiveQuery(sqlcmd, startwith, prior)
RecursiveQuery("SELECT c1 FROM t1", "c1=1", "c1=c2") */
drop table tt1;
drop table tt2;
create table tt1 (c1 int, c2 int);
create table tt2 (c1 int, c2 int);
delete from tt1;
select c1, c2 from t1 where c1=1 into tt1; select * from tt1 into t2;
// loop begin
delete from tt2;
select t1.c1, t1.c2 from t1, tt1 where tt1.c2=t1.c1 into tt2; select * from tt2 into t2;
delete from tt1;
select t1.c1, t1.c2 from t1, tt2 where tt2.c2=t1.c1 into tt1; select * from tt1 into t2;
delete from tt2;
select t1.c1, t1.c2 from t1, tt1 where tt1.c2=t1.c1 into tt2; select * from tt2 into t2;
delete from tt1;
select t1.c1, t1.c2 from t1, tt2 where tt2.c2=t1.c1 into tt1; select * from tt1 into t2;
delete from tt2;
select t1.c1, t1.c2 from t1, tt1 where tt1.c2=t1.c1 into tt2; select * from tt2 into t2;
// loop end until select into no rows
drop table tt1;
drop table tt2;
select distinct c1 from t2;
同样,DBMaster SP能够提供帮助:
首先,创建sp connby.ec:
/*
SELECT id FROM groups
START WITH id=2 CONNECT BY
PRIOR parent_id = id;
*/
exec sql create procedure connectby_groups (integer hid) returns int outid;
{
exec sql begin declare section;
int i, cnt;
char sqlstr[256];
exec sql end declare section;
exec sql begin code section;
$ whenever sqlerror continue;
exec sql drop table temp_connectby;
$ whenever sqlerror goto dmSP_EXIT_LABEL;
exec sql create temp table temp_t1 (id int, parent_id int);
exec sql create temp table temp_t2 (id int, parent_id int);
exec sql create temp table temp_connectby (id int, parent_id int);
exec sql select id, parent_id from groups where id=:hid into temp_t1;
exec sql select id, parent_id from temp_t1 into temp_connectby;
do {
exec sql delete from temp_t2;
exec sql select a.id, a.parent_id from groups a, temp_t1 b where a.id=b.parent_id into temp_t2;
exec sql select count(*) from temp_t2 into :cnt;
if (cnt > 0)
exec sql select id, parent_id from temp_t2 into temp_connectby;
else
break;
exec sql delete from temp_t1;
exec sql select a.id, a.parent_id from groups a, temp_t2 b where a.id=b.parent_id into temp_t1;
exec sql select count(*) from temp_t1 into :cnt;
if (cnt > 0)
exec sql select id, parent_id from temp_t1 into temp_connectby;
else
break;
} while (1);
exec sql RETURNS select distinct id from temp_connectby into :outid;
exec sql drop table temp_t1;
exec sql drop table temp_t2;
exec sql end code section;
}
然后运行示例SQL:
create db sample5;
create table groups (id int, parent_id int);
insert into groups values (1, null);
insert into groups values (11, 1);
insert into groups values (12, 1);
insert into groups values (13, 1);
insert into groups values (111, 11);
insert into groups values (112, 11);
insert into groups values (131, 13);
insert into groups values (1121, 112);
insert into groups values (11211, 1121);
insert into groups values (112111, 11211);
terminate db;
start db sample5 sysadm;
create proc from 'connby.ec';
call connectby_groups(112111);
call connectby_groups(1);
call connectby_groups(131);
call connectby_groups(168);
call connectby_groups(11211);
terminate db;