日期:2014-05-17 浏览次数:20850 次
create or replace view v_test
as
select id,
(select count(1) from test where 父ID=t.id) 数量
from test t
with sd as(
select 1 as id, 0 as parentid, 1 as ext from dual union all
select 2 as id, 1 as parentid, 2 as ext from dual union all
select 3 as id, 2 as parentid, 3 as ext from dual union all
select 4 as id, 0 as parentid, 4 as ext from dual union all
select 5 as id, 4 as parentid, 5 as ext from dual union all
select 6 as id, 4 as parentid, 6 as ext from dual)
select t.id, (select sum(ext) from sd connect by prior id = parentid start with id = t.id) res from sd t;
create table test_table as(
select 1 as id, 0 as parentid, 1 as ext from dual union all
select 2 as id, 1 as parentid, 2 as ext from dual union all
select 3 as id, 2 as parentid, 3 as ext from dual union all
select 4 as id, 0 as parentid, 4 as ext from dual union all
select 5 as id, 4 as parentid, 5 as ext from dual union all
select 6 as id, 4 as parentid, 6 as ext from dual);
create or replace view t