with tdyj_zg as( /*zhuguan tdyj*/
select shopname,yjsj,ssld,sum(gryj) as tdyj
from employees
where zw ='dianyuan'
group by shopname,yjsj,ssld
),tdyj_jl as( /*jingli tdyj*/
select shopname,yjsj,ssld,sum(tdyj) as tdyj
from(
select a.shopname,a.yjsj,a.xm,a.ssld,b.tdyj+a.gryj as tdyj
from employees a
left join tdyj_zg b on b.shopname =a.shopname and b.yjsj =a.yjsj and b.ssld =a.xm
where a.zw ='zhuguan') s
group by shopname,yjsj,ssld
),tdyj_ddz as( /*daidianzhang tdyj*/
select shopname,yjsj,ssld,sum(tdyj) as tdyj
from(
select a.shopname,a.yjsj,a.xm,a.ssld,b.tdyj+a.gryj as tdyj
from employees a
left join tdyj_jl b on b.shopname =a.shopname and b.yjsj =a.yjsj and b.ssld =a.xm
where a.zw ='jingli') s
group by shopname,yjsj,ssld
),tdyj_dz as( /*dianzhang tdyj*/
select shopname,yjsj,ssld,sum(tdyj) as tdyj
from(
select a.shopname,a.yjsj,a.xm,a.ssld,b.tdyj+a.gryj as tdyj
from employees a
left join tdyj_ddz b on b.shopname =a.shopname and b.yjsj =a.yjsj and b.ssld =a.xm
where a.zw ='daidianzhang') s
group by shopname,yjsj,ssld)
select a.*,
case a.zw
when 'zhuguan' then b.tdyj
when 'jingli' then c.tdyj
when 'daidianzhang' then d.tdyj
when 'dianzhang' then e.tdyj
else 0 end as tdyj
from employees a
left join tdyj_zg b on b.shopname =a.shopname and b.yjsj =a.yjsj and b.ssld =a.xm
left join tdyj_jl c on c.shopname =a.shopname and c.yjsj =a.yjsj and c.ssld =a.xm
left join tdyj_ddz d on d.shopname =a.shopname and d.yjsj =a.yjsj and d.ssld =a.xm
left join tdyj_dz e on e.shopname =a.shopname and e.yjsj =a.yjsj and e.ssld =a.xm
nide870716 回复 朱兴胜(提问者)下面这个脚本结合递归改成了存储过程，应该符合你的要求了。
--create stored procedure
create procedure dbo.calc_tdyj as
begin
--declare table @res for calculated result
declare @res table(
shopname varchar(50),
yjsj varchar(50),
xm varchar(50),
zw varchar(50),
bm varchar(50),
ssld varchar(50),
gryj varchar(50),
tdyj varchar(50));
--declare cursor
declare @per varchar(50);
declare csr cursor scroll
for select distinct ssld from employees where ssld is not null;
open csr;
fetch next from csr into @per
while(@@FETCH_STATUS =0)
begin
begin
with cte as(
select xm,zw,ssld,gryj
from employees
where ssld =@per
union all
select b.xm,b.zw,b.ssld,b.gryj
from cte a
join employees b on a.xm =b.ssld)
--insert the result of calculated tdyj
insert into @res(shopname,yjsj,xm,zw,bm,ssld,gryj,tdyj)
select shopname,yjsj,xm,zw,bm,ssld,gryj,(select sum(gryj) from cte) as tdyj
from employees
where xm =@per;
end
fetch next from csr into @per
end
close csr;
deallocate csr;
--show the result
select * from @res;
end;
--calling stored procedure
exec calc_tdyj;
