oracle中的connect,by,在sql,server中实现:sqlserver使用教程
时间:2018-08-15 16:33:16 来源:写作资料库 本文已影响 人
1。Oracle中:
select org_id
from organizations
start with org_id= :org_id
connect by prior org_id=supervision_org_id
order by supervision_org_id
2。Sql Functiong:
FunctionNo SubFunctionNo SubFunctionType
01. systemadmin Privilege 0
01. systemadmin subsysadmin 1(表示樹枝)
subsysadmin subsys1 0(表示leaf)
subsysadmin subsys2 0
subsysadmin User 1
User UserOne 0
請教實現此層次結構輸出的函數(在oracle中可以使用connect by實現此功能)
先謝了.
3。sql语句实现:
举例:
declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,"中国")
insert @a values(2,0,"美国")
insert @a values(3,0,"加拿大")
insert @a values(4,1,"北京")
insert @a values(5,1,"上海")
insert @a values(6,1,"江苏")
insert @a values(7,6,"苏州")
insert @a values(8,7,"常熟")
insert @a values(9,6,"南京")
insert @a values(10,6,"无锡")
insert @a values(11,2,"纽约")
insert @a values(12,2,"旧金山")
declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200),lev int)
insert @tmp1 select *,1 from @a where tc_ID=1
while exists(select 1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1))
insert @tmp1 select a.*,1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
select * from @tmp1
注:更多精彩文章请关注三联编程教程栏目。