怎么得到所有下级id
如何得到所有下级id啊
PK_CORP(公司id) FATHERCORP(上级公司id) UNITNAME(名称) RS (人数)
1001 '' 总部 0
1002 1001 北京 10
1003 1002 天津 10
1004 1003 湖北 10
1005 1002 上海 10
1006 1001 南京 10
--如何输入pk_corp,能得到下级公司PK_CORP(包含本身,以及下级的下级)
比如:输入1001 返回所有pk_corp
输入1002 返回 1002,1003,1004,1005
CREATE VIEW v_corp
AS
SELECT '1001' pk_corp ,'' FATHERCORP ,'总部' unitname ,0 rs FROM DUAL
UNION ALL
SELECT '1002' pk_corp ,'1001' FATHERCORP ,'北京' unitname ,10 rs FROM DUAL
UNION ALL
SELECT '1003' pk_corp ,'1002' FATHERCORP ,'天津' unitname,10 rs FROM DUAL
UNION ALL
SELECT '1004' pk_corp ,'1003' FATHERCORP ,'湖北' unitname ,10 rs FROM DUAL
UNION ALL
SELECT '1005' pk_corp ,'1002' FATHERCORP ,'上海' unitname,10 rs FROM DUAL
UNION ALL
SELECT '1006' pk_corp ,'1001' FATHERCORP ,'南京' unitname,10 rs FROM DUAL
------解决方案--------------------
SQL> select * from treeview;
PARENT CHILD
---------- ----------
a b
a c
a e
b b1
b b2
c c1
e e1
e e3
c e
9 rows selected.
SQL> select * from treeview start with parent='c' connect by child=parent;
PARENT CHILD
---------- ----------
c c1
c e
SQL> select * from treeview start with parent='c' connect by prior child=parent;
PARENT CHILD
---------- ----------
c c1
c e
e e1
e e3
------解决方案--------------------
try it ..
PK_CORP(公司id) FATHERCORP(上级公司id) UNITNAME(名称) RS (人数)
1001 '' 总部 0
1002 1001 北京 10
1003 1002 天津 10
1004 1003 湖北 10
1005 1002 上海 10
1006 1001 南京 10
--如何输入pk_corp,能得到下级公司PK_CORP(包含本身,以及下级的下级)
比如:输入1001 返回所有pk_corp
输入1002 返回 1002,1003,1004,1005
CREATE VIEW v_corp
AS
SELECT '1001' pk_corp ,'' FATHERCORP ,'总部' unitname ,0 rs FROM DUAL
UNION ALL
SELECT '1002' pk_corp ,'1001' FATHERCORP ,'北京' unitname ,10 rs FROM DUAL
UNION ALL
SELECT '1003' pk_corp ,'1002' FATHERCORP ,'天津' unitname,10 rs FROM DUAL
UNION ALL
SELECT '1004' pk_corp ,'1003' FATHERCORP ,'湖北' unitname ,10 rs FROM DUAL
UNION ALL
SELECT '1005' pk_corp ,'1002' FATHERCORP ,'上海' unitname,10 rs FROM DUAL
UNION ALL
SELECT '1006' pk_corp ,'1001' FATHERCORP ,'南京' unitname,10 rs FROM DUAL
------解决方案--------------------
SQL> select * from treeview;
PARENT CHILD
---------- ----------
a b
a c
a e
b b1
b b2
c c1
e e1
e e3
c e
9 rows selected.
SQL> select * from treeview start with parent='c' connect by child=parent;
PARENT CHILD
---------- ----------
c c1
c e
SQL> select * from treeview start with parent='c' connect by prior child=parent;
PARENT CHILD
---------- ----------
c c1
c e
e e1
e e3
------解决方案--------------------
try it ..
- SQL code
SQL> select distinct 2 ltrim(first_value(sys_connect_by_path(pk_corp,',')) over(order by level desc),',') as new_pk_corp 3 from ( 4 select pk_corp, 5 row_number() over(order by pk_corp) as front_values, 6 (row_number() over(order by pk_corp))+1 as behind_values 7 from tablename tt 8 start with pk_corp = 1002 9 connect by prior pk_corp = FATHERCORP 10 )zz 11 connect by prior behind_values = front_values; NEW_PK_CORP -------------------------------------------- 1002,1003,1004,1005
------解决方案--------------------
- SQL code
-- 树形数据深度排序处理示例(递归法) --测试数据 CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10)) INSERT tb SELECT '001',NULL ,'山东省' UNION ALL SELECT '002','001','烟台市' UNION ALL SELECT '004','002','招远市' UNION ALL SELECT '003','001','青岛市' UNION ALL SELECT '005',NULL ,'四会市' UNION ALL SELECT '006','005','清远市' UNION ALL SELECT '007','006','小分市' GO --广度搜索排序函数 CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1) RETURNS @t_Level TABLE(ID char(3),sort int) AS BEGIN DECLARE tb CURSOR LOCAL FOR SELECT ID FROM tb WHERE PID=@ID OR(@ID IS NULL AND PID IS NULL) OPEN TB FETCH tb INTO @ID WHILE @@FETCH_STATUS=0 BEGIN INSERT @t_Level VALUES(@ID,@sort) SET @sort=@sort+1 IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层) BEGIN --递归查找当前节点的子节点 INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort) SET @sort=@sort+@@ROWCOUNT --排序号加上子节点个数 END FETCH tb INTO @ID END RETURN END GO --显示结果 SELECT a.* FROM tb a,f_Sort(DEFAULT,DEFAULT) b WHERE a.ID=b.ID ORDER BY b.sort /*--结果 ID PID Name ------ --------- ---------- 001 NULL 山东省 002 001 烟台市 004 002 招远市 003 001 青岛市 005 NULL 四会市 006 005 清远市 007 006 小分市 --*/