怎么得到所有下级id

2012-03-07

如何得到所有下级id啊PK_CORP(公司id) FATHERCORP(上级公司id) UNITNAME(名称) RS (人数)1001总部010021

如何得到所有下级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 ..

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)ASBEGIN    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    RETURNENDGO--显示结果SELECT a.*FROM tb a,f_Sort(DEFAULT,DEFAULT) bWHERE a.ID=b.IDORDER BY b.sort/*--结果ID   PID   Name       ------ --------- ---------- 001  NULL 山东省002  001   烟台市004  002   招远市003  001   青岛市005  NULL 四会市006  005   清远市007  006   小分市--*/ 
 
[解决办法]
select * from v_corp start with pk_corp = 1002 connect by prior pk_corp = fathercorp;

结果是:
10021001北京10
10031002天津10
10041003湖北10
10051002上海10

start with pk_corp = 1002 中的1002改成需要的pk_corp就行了.

热点排行

oracle

怎么得到所有下级id