ID PARENTID TYPE
1 NULL 0
2 NULL 0
100 1
200 2 0
300 100
400 200TYPE 字段有可能为空
PARENTID也就是ID,上一级ID我现在想要的数据就是,parentid,同时type=0的数据比如ID=400,它的parentid=200,此时200的type=0,这个200就是我要的
有一种情况,比如300,它的parentid=100,但是type is null,那么需要继续往上找,100的parentid=1,此时1的type=0,那么这个1也是我想要的。请教下,这种语句怎么写呢?非常感谢!
1 NULL 0
2 NULL 0
100 1
200 2 0
300 100
400 200TYPE 字段有可能为空
PARENTID也就是ID,上一级ID我现在想要的数据就是,parentid,同时type=0的数据比如ID=400,它的parentid=200,此时200的type=0,这个200就是我要的
有一种情况,比如300,它的parentid=100,但是type is null,那么需要继续往上找,100的parentid=1,此时1的type=0,那么这个1也是我想要的。请教下,这种语句怎么写呢?非常感谢!
SELECT t1.id FROM[表名] t1 CASE WHEN t1.type is null THEN (SELECT type FROM [表名] t2 where t1.parentid=t2.id ) ELSE t1.type=0 END 乱写的,不知道对不对。
create table pm_test(ID number, PARENTID number ,TYPE number)
insert into pm_test values(1, NULL, 0);
insert into pm_test values(2, NULL, 0);
insert into pm_test values(100, 1, null);
insert into pm_test values(200, 2, 0);
insert into pm_test values(300, 100, null);
insert into pm_test values(400, 200, null);
commit;select * from(
select t.*,level,row_number() over(order by level) rn from pm_test t where type=0 connect by prior parentid=id start with id=400)
where rn=1-----------------
rownum ID PARENTID TYPE LEVEL RN
1 200 2 0 2 1
t1 as (select ID, PARENTID,TYPE from
(select ID, PARENTID,TYPE,row_number()over(order by level)rn from pm_test where type=0 start with ID in (400)
connect by prior PARENTID=ID)
where rn=1
),
t2 as (select ID, PARENTID,TYPE from
(select ID, PARENTID,TYPE,row_number()over(order by level)rn from pm_test where type=0 start with ID in (300)
connect by prior PARENTID=ID)
where rn=1
)
select * from t1
union all
select * from t2;