-----------------读取编码规则
declare @kmcd1 int,@kmcd2 int,@kmcd3 int,@kmcd4 int,@kmcd5 int,@kmcd varchar(10),@qs int,@kjnd int,@num int
select @kmcd=KMBMGZ,@qs=isnull(yzqs+1,qyqs),@kjnd=qyn from tAccount
set @kmcd2=cast(substring(@kmcd,2,1) as int)
set @kmcd3=cast(substring(@kmcd,3,1) as int)
set @kmcd4=cast(substring(@kmcd,4,1) as int)
set @kmcd5=cast(substring(@kmcd,5,1) as int)
set @kmcd1=cast(substring(@kmcd,1,1) as int)
----更新@kmcd1
update tSubjectyesb
set jf=jf+t1.ajfMoney,df=df+t1.adfMoney,ye=(case when t2.fx='借' then ye+t1.ajfMoney-t1.adfMoney else ye-t1.ajfMoney+t1.adfMoney end),
wbjf=wbjf+t1.ajfwb,wbdf=wbdf+t1.adfwb,wbye=(case when t2.fx='借' then wbye+t1.ajfwb-t1.adfwb else wbye-t1.ajfwb+t1.adfwb end),
jfsl=jfsl+t1.ajfsl,jfdj=(case when jfsl = 0 then 0 else jf/jfsl end),
dfsl=dfsl+t1.adfsl,dfdj=(case when dfsl = 0 then 0 else df/dfsl end),
yesl=(case when t2.fx='借' then yesl+t1.ajfsl-t1.adfsl else yesl-t1.ajfsl+t1.adfsl end),
yedj=(case when yesl = 0 then 0 else ye/yesl end),
bnjfhj=bnjfhj+t1.ajfMoney,bqjfhj=bqjfhj+t1.ajfMoney,
bndfhj=bndfhj+t1.adfMoney,bqdfhj=bqdfhj+t1.adfMoney,
bnwbjfhj=bnwbjfhj+t1.ajfwb,bnwbdfhj=bnwbdfhj+t1.adfwb,
bnsljfhj=bnsljfhj+t1.ajfsl,bnsldfhj=bnsldfhj+t1.adfsl
from tSubjectyesb,
(select kmbm,sum(jfMoney) as ajfMoney,sum(dfMoney) as adfMoney,sum(jfsl) as ajfsl,sum(dfsl) as adfsl,sum(jfwb) as ajfwb,sum(dfwb) as adfwb
from tFnkjflk where kjnd=@kjnd and qishu=@qs group by kmbm) t1
left join tsubject t2 on t1.kmbm=t2.kmbm
where tSubjectyesb.kmbm=t1.kmbm and kjnd=@kjnd and qs=@qs and len(t1.kmbm)=@kmcd1 --and tSubjectyesb.kmbm like left(t1.kmbm,@kmcd1)+'%'
select @num=count(*) from tSubjectyesb left join
(select kmbm,sum(jfMoney) as ajfMoney,sum(dfMoney) as adfMoney,sum(jfsl) as ajfsl,sum(dfsl) as adfsl,sum(jfwb) as ajfwb,sum(dfwb) as adfwb
from tFnkjflk where kjnd=@kjnd and qishu=@qs and len(kmbm)=@kmcd1 group by kmbm) t1 on tSubjectyesb.kmbm=t1.kmbm
where kjnd=@kjnd and qs=@qs-1 if @num=0
begin
insert into tSubjectyesb(kmbm,kjnd,qs,fx) select t1.kmbm,@kjnd,@qs-1,t2.fx
from (select kmbm,sum(jfMoney) as ajfMoney,sum(dfMoney) as adfMoney,sum(jfsl) as ajfsl,sum(dfsl) as adfsl,sum(jfwb) as ajfwb,sum(dfwb) as adfwb
from tFnkjflk where kjnd=@kjnd and qishu=@qs and len(kmbm)=@kmcd1 group by kmbm) t1
left join tsubject t2 on t1.kmbm=t2.kmbm
end insert into tSubjectyesb(kmbm,kjnd,qs,fx,jf,df,ye,
wbjf,wbdf,wbye,
jfsl,jfdj,
dfsl,dfdj,
yesl,
yedj,
bnjfhj,bqjfhj,bndfhj,bqdfhj,
bnwbjfhj,bnwbdfhj,
bnsljfhj,bnsldfhj)
select t1.kmbm,@kjnd,@qs,t2.fx,t1.ajfMoney,t1.adfMoney,(case when t2.fx='借' then ye+t1.ajfMoney-t1.adfMoney else ye-t1.ajfMoney+t1.adfMoney end),
t1.ajfwb,t1.adfwb,(case when t2.fx='借' then wbye+t1.ajfwb-t1.adfwb else wbye-t1.ajfwb+t1.adfwb end),
t1.ajfsl,(case when jfsl = 0 then 0 else jf/jfsl end),
t1.adfsl,(case when dfsl = 0 then 0 else df/dfsl end),
(case when t2.fx='借' then yesl+t1.ajfsl-t1.adfsl else yesl-t1.ajfsl+t1.adfsl end),
(case when yesl = 0 then 0 else ye/yesl end),
isnull(bnjfhj,0)+t1.ajfMoney,t1.ajfMoney,isnull(bndfhj,0)+t1.adfMoney,t1.adfMoney,
isnull(bnwbjfhj,0)+t1.ajfwb,isnull(bnwbdfhj,0)+t1.adfwb,
isnull(bnsljfhj,0)+t1.ajfsl,isnull(bnsldfhj,0)+t1.adfsl
from tSubjectyesb,
(select kmbm,sum(jfMoney) as ajfMoney,sum(dfMoney) as adfMoney,sum(jfsl) as ajfsl,sum(dfsl) as adfsl,sum(jfwb) as ajfwb,sum(dfwb) as adfwb
from tFnkjflk where kjnd=@kjnd and qishu=@qs and len(kmbm)=@kmcd1 group by kmbm) t1
left join tsubject t2 on t1.kmbm=t2.kmbm
where tSubjectyesb.kmbm=t1.kmbm and kjnd=@kjnd and qs=@qs-1 and t1.kmbm not in
(select kmbm from tSubjectyesb where kjnd=@kjnd and qs=@qs )----更新@kmcd1+@kmcd2
必须重复以上内容但编码长度为@kmcd1+@kmcd2的编码,还有编码长度为KMCD1的其上级编码的内容
编码规则最多到@kmcd5,但是要逐级判断内容非常大,请大侠帮忙简化一下
declare @kmcd1 int,@kmcd2 int,@kmcd3 int,@kmcd4 int,@kmcd5 int,@kmcd varchar(10),@qs int,@kjnd int,@num int
select @kmcd=KMBMGZ,@qs=isnull(yzqs+1,qyqs),@kjnd=qyn from tAccount
set @kmcd2=cast(substring(@kmcd,2,1) as int)
set @kmcd3=cast(substring(@kmcd,3,1) as int)
set @kmcd4=cast(substring(@kmcd,4,1) as int)
set @kmcd5=cast(substring(@kmcd,5,1) as int)
set @kmcd1=cast(substring(@kmcd,1,1) as int)
----更新@kmcd1
update tSubjectyesb
set jf=jf+t1.ajfMoney,df=df+t1.adfMoney,ye=(case when t2.fx='借' then ye+t1.ajfMoney-t1.adfMoney else ye-t1.ajfMoney+t1.adfMoney end),
wbjf=wbjf+t1.ajfwb,wbdf=wbdf+t1.adfwb,wbye=(case when t2.fx='借' then wbye+t1.ajfwb-t1.adfwb else wbye-t1.ajfwb+t1.adfwb end),
jfsl=jfsl+t1.ajfsl,jfdj=(case when jfsl = 0 then 0 else jf/jfsl end),
dfsl=dfsl+t1.adfsl,dfdj=(case when dfsl = 0 then 0 else df/dfsl end),
yesl=(case when t2.fx='借' then yesl+t1.ajfsl-t1.adfsl else yesl-t1.ajfsl+t1.adfsl end),
yedj=(case when yesl = 0 then 0 else ye/yesl end),
bnjfhj=bnjfhj+t1.ajfMoney,bqjfhj=bqjfhj+t1.ajfMoney,
bndfhj=bndfhj+t1.adfMoney,bqdfhj=bqdfhj+t1.adfMoney,
bnwbjfhj=bnwbjfhj+t1.ajfwb,bnwbdfhj=bnwbdfhj+t1.adfwb,
bnsljfhj=bnsljfhj+t1.ajfsl,bnsldfhj=bnsldfhj+t1.adfsl
from tSubjectyesb,
(select kmbm,sum(jfMoney) as ajfMoney,sum(dfMoney) as adfMoney,sum(jfsl) as ajfsl,sum(dfsl) as adfsl,sum(jfwb) as ajfwb,sum(dfwb) as adfwb
from tFnkjflk where kjnd=@kjnd and qishu=@qs group by kmbm) t1
left join tsubject t2 on t1.kmbm=t2.kmbm
where tSubjectyesb.kmbm=t1.kmbm and kjnd=@kjnd and qs=@qs and len(t1.kmbm)=@kmcd1 --and tSubjectyesb.kmbm like left(t1.kmbm,@kmcd1)+'%'
select @num=count(*) from tSubjectyesb left join
(select kmbm,sum(jfMoney) as ajfMoney,sum(dfMoney) as adfMoney,sum(jfsl) as ajfsl,sum(dfsl) as adfsl,sum(jfwb) as ajfwb,sum(dfwb) as adfwb
from tFnkjflk where kjnd=@kjnd and qishu=@qs and len(kmbm)=@kmcd1 group by kmbm) t1 on tSubjectyesb.kmbm=t1.kmbm
where kjnd=@kjnd and qs=@qs-1 if @num=0
begin
insert into tSubjectyesb(kmbm,kjnd,qs,fx) select t1.kmbm,@kjnd,@qs-1,t2.fx
from (select kmbm,sum(jfMoney) as ajfMoney,sum(dfMoney) as adfMoney,sum(jfsl) as ajfsl,sum(dfsl) as adfsl,sum(jfwb) as ajfwb,sum(dfwb) as adfwb
from tFnkjflk where kjnd=@kjnd and qishu=@qs and len(kmbm)=@kmcd1 group by kmbm) t1
left join tsubject t2 on t1.kmbm=t2.kmbm
end insert into tSubjectyesb(kmbm,kjnd,qs,fx,jf,df,ye,
wbjf,wbdf,wbye,
jfsl,jfdj,
dfsl,dfdj,
yesl,
yedj,
bnjfhj,bqjfhj,bndfhj,bqdfhj,
bnwbjfhj,bnwbdfhj,
bnsljfhj,bnsldfhj)
select t1.kmbm,@kjnd,@qs,t2.fx,t1.ajfMoney,t1.adfMoney,(case when t2.fx='借' then ye+t1.ajfMoney-t1.adfMoney else ye-t1.ajfMoney+t1.adfMoney end),
t1.ajfwb,t1.adfwb,(case when t2.fx='借' then wbye+t1.ajfwb-t1.adfwb else wbye-t1.ajfwb+t1.adfwb end),
t1.ajfsl,(case when jfsl = 0 then 0 else jf/jfsl end),
t1.adfsl,(case when dfsl = 0 then 0 else df/dfsl end),
(case when t2.fx='借' then yesl+t1.ajfsl-t1.adfsl else yesl-t1.ajfsl+t1.adfsl end),
(case when yesl = 0 then 0 else ye/yesl end),
isnull(bnjfhj,0)+t1.ajfMoney,t1.ajfMoney,isnull(bndfhj,0)+t1.adfMoney,t1.adfMoney,
isnull(bnwbjfhj,0)+t1.ajfwb,isnull(bnwbdfhj,0)+t1.adfwb,
isnull(bnsljfhj,0)+t1.ajfsl,isnull(bnsldfhj,0)+t1.adfsl
from tSubjectyesb,
(select kmbm,sum(jfMoney) as ajfMoney,sum(dfMoney) as adfMoney,sum(jfsl) as ajfsl,sum(dfsl) as adfsl,sum(jfwb) as ajfwb,sum(dfwb) as adfwb
from tFnkjflk where kjnd=@kjnd and qishu=@qs and len(kmbm)=@kmcd1 group by kmbm) t1
left join tsubject t2 on t1.kmbm=t2.kmbm
where tSubjectyesb.kmbm=t1.kmbm and kjnd=@kjnd and qs=@qs-1 and t1.kmbm not in
(select kmbm from tSubjectyesb where kjnd=@kjnd and qs=@qs )----更新@kmcd1+@kmcd2
必须重复以上内容但编码长度为@kmcd1+@kmcd2的编码,还有编码长度为KMCD1的其上级编码的内容
编码规则最多到@kmcd5,但是要逐级判断内容非常大,请大侠帮忙简化一下
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281