create table ttt(dh varchar(10),je numeric(8,2),zdh varchar(10))
insert into ttt select '0001',12.53,'0003'
insert into ttt select '0002',12.53,'0003'
insert into ttt select '0003',78.52,'0004'
insert into ttt select '0004',56.56,'0006'
insert into ttt select '0005',56.98,'0004'
insert into ttt select '0006',78.56,'0'
create function f_getRoot(@dh varchar(10))
returns varchar(10)
as
begin
if exists(select 1 from ttt where dh=@dh and zdh!='0')
select @dh=zdh from ttt where dh=@dh
return @dh
end
select
dh = dbo.f_getRoot(dh),
je = sum(je)
from
ttt
where
dbo.f_getRoot(dh) = '0004'
group by
dbo.f_getRoot(dh)
insert into ttt select '0001',12.53,'0003'
insert into ttt select '0002',12.53,'0003'
insert into ttt select '0003',78.52,'0004'
insert into ttt select '0004',56.56,'0006'
insert into ttt select '0005',56.98,'0004'
insert into ttt select '0006',78.56,'0'
create function f_getRoot(@dh varchar(10))
returns varchar(10)
as
begin
if exists(select 1 from ttt where dh=@dh and zdh!='0')
select @dh=zdh from ttt where dh=@dh
return @dh
end
select
dh = dbo.f_getRoot(dh),
je = sum(je)
from
ttt
where
dbo.f_getRoot(dh) = '0004'
group by
dbo.f_getRoot(dh)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货