create table lgx.xs
(
userid int not null primary key,
username varchar2(255) not null,
pusername varchar2(255),
num int
);insert into xs values(1,'1000',null,null);
insert into xs values(2,'1100','1000',null);
insert into xs values(3,'1200','1000',null);
insert into xs values(4,'1101','1100',100);
insert into xs values(5,'1102','1100',200);
insert into xs values(6,'1104','1100',250);
insert into xs values(7,'1201','1200',150);
insert into xs values(8,'1205','1200',200);
insert into xs values(9,'1207','1200',150);
commit;select b.username,b.pusername,
(select distinct sum(nvl(a.num,0)) from lgx.xs a
start with a.username=b.username
connect by a.pusername=prior a.username)
from lgx.xs b
start with trim(both ' ' from username)='1000'
connect by pusername=prior username;
(
userid int not null primary key,
username varchar2(255) not null,
pusername varchar2(255),
num int
);insert into xs values(1,'1000',null,null);
insert into xs values(2,'1100','1000',null);
insert into xs values(3,'1200','1000',null);
insert into xs values(4,'1101','1100',100);
insert into xs values(5,'1102','1100',200);
insert into xs values(6,'1104','1100',250);
insert into xs values(7,'1201','1200',150);
insert into xs values(8,'1205','1200',200);
insert into xs values(9,'1207','1200',150);
commit;select b.username,b.pusername,
(select distinct sum(nvl(a.num,0)) from lgx.xs a
start with a.username=b.username
connect by a.pusername=prior a.username)
from lgx.xs b
start with trim(both ' ' from username)='1000'
connect by pusername=prior username;
---用于取的该id以及,该id所管下属的销售总数.
return number is
Result number;
begin
SELECT SUM(sale_num) INTO result
FROM tname
START WITH id=v_id CONNECT BY PRIOR id=parent_id;
return(Result);
end get_sale_sum;
select id,get_sale_sum(id) from sales;