求教这个视图该如何建立
表a
ID 父ID 数量
1 0 1
2 1 2
3 2 3
4 0 4
5 4 5
6 4 6
结果视图
ID 数量
1 6
2 5
3 3
4 10
5 5
6 6
这个试图该怎么建立啊求教！
[解决办法]
要不就写上计算规则，不写计算规则你起码把数填对了。
[解决办法]
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 test_view as
select t.id, (select sum(ext) from test_table connect by prior id = parentid start with id = t.id) res from test_table t;
