Question

oracle中start with connect by prior在sqlserver中怎么写

select
tor.*
from
tb_organrank t,
tb_organ tor
where
t.id_organ=tor.id_organ start with t.id_parentorgan=1 connect
by
prior t.id_organ=t.id_parentorgan

Answer 1

用 with .. as

实现递归。

供参考：

Oracle自己提供的是 connect by ... start with，而别的数据库只有使用With来实现

create tabletest1(id number, name varchar2(20), pid number);

insert intotest1 values(1,'电器',null);
insert intotest1 values(2,'家电',1);
insert intotest1 values(3,'冰箱',2);
insert intotest1 values(4,'洗衣机',2);
insert intotest1 values(5,'电脑',1);
insert intotest1 values(6,'笔记本',5);
insert intotest1 values(7,'平板',5);
insert intotest1 values(8,'组装机',7);
insert intotest1 values(9,'品牌机',7);

withsubqry(id,name,pid) as(
select id,name,pid fromtest1 where id= 5
union all
selecttest1.id,test1.name,test1.pid fromtest1,subqry
wheretest1.pid = subqry.id
)
select* fromsubqry;

drop tabletest1;