一个计算的sql语句
测试数据如下：mom day zy jfje dfje jeye（金额余额）01 01 期初余额 0 0 10000000001 02 预收账款 1000 0 001 02 预收账款 1000 0 001 02 预收账款 1000 0 001 02 预收账款 1000 0 001 02 预收账款 1000 0 001 02 预收账款 1000 0 001 02 预收账款 0 12330 0jeye的值要等于 期初余额的jeye 的值 +jfje-dfje结果mom day zy jfje dfje jeye（金额余额）01 01 期初余额 0 0 10000000001 02 预收账款 1000 0 99999000............本人用一下写法能实现 不过数据一多 效率就很差 求大牛指点 select mom,day, zy,jfje,dfje,jeye=(select jeye=sum(jeye)+sum(jfje)-sum(dfje) from #result where rid<=a.rid) from (select * ,row_numbe() over(order by getdate()) rid from #result) a求更高效率的语句 [img=http://forum.csdn.net/PointForum/ui/scripts/csdn/Plugin/003/monkey/15.gif][/img]
declare @t table([mom] varchar(2),[day] varchar(2),[zy] varchar(8),[jfje] int,[dfje] int,[jeye] int)insert @tselect '01','01','期初余额',0,0,100000000 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',0,12330,0;with maco as (select row_number() over(order by getdate()) rid,* from @t)select mom,[day],zy,jfje,dfje,jeye=(select sum(jeye-jfje+dfje) from maco where rid<=t.rid) from maco t/*mom day zy jfje dfje jeye---- ---- -------- ----------- ----------- -----------01 01 期初余额 0 0 10000000001 02 预收账款 1000 0 9999900001 02 预收账款 1000 0 9999800001 02 预收账款 1000 0 9999700001 02 预收账款 1000 0 9999600001 02 预收账款 1000 0 9999500001 02 预收账款 1000 0 9999400001 02 预收账款 0 12330 100006330*/--你这个表没有主键吗？
[解决办法]
看下这个能否满足要求with cte1 as(select row=row_number() over(order by mom,day),* from tb), cte2 as(select * from cte1 where row=1 union all select cte1.row,cte1.mom,cte1.day,cte1.zy,cte1.jfje,cte1.dfje,jeye=cte2.jeye+cte1.jfje-cte1.dfje from cte2 join cte1 on cte1.row=cte2.row+1)select * from cte21 1 1 期初余额 0 0 1000000002 1 2 预收账款 1000 0 1000010003 1 2 预收账款 1000 0 1000020004 1 2 预收账款 1000 0 1000030005 1 2 预收账款 1000 0 1000040006 1 2 预收账款 1000 0 1000050007 1 2 预收账款 1000 0 1000060008 1 2 预收账款 0 12330 99993670
[解决办法]
create table #result([mom] varchar(4),[day] varchar(4),[zy] varchar(8),[jfje] int,[dfje] int,[jeye] int)insert #resultselect '01','01','期初余额',0,0,100000000 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',0,12330,0;with t as(select mom,[day],zy,jfje,dfje,jeye, row_number() over(order by getdate()) rn from #result)select mom,[day],zy,jfje,dfje,c.yefrom t across apply(select sum(jeye)-sum(jfje)+sum(dfje) 'ye' from t b where b.rn<=a.rn) c /*mom day zy jfje dfje ye---- ---- -------- ----------- ----------- -----------01 01 期初余额 0 0 10000000001 02 预收账款 1000 0 9999900001 02 预收账款 1000 0 9999800001 02 预收账款 1000 0 9999700001 02 预收账款 1000 0 9999600001 02 预收账款 1000 0 9999500001 02 预收账款 1000 0 9999400001 02 预收账款 0 12330 100006330(8 row(s) affected)*/
[解决办法]
Select T1.mom, T1.day, T1.zy, T1.jfje, T1.dfje, Sum(T2.jeye+T2.jfje-T2.dfje)From (select row_number() over(order by getdate()) rid,* from @T) T1 Left Join (select row_number() over(order by getdate()) rid,* from @T) T2 On (T1.RId >=T2.RId)Group By T1.mom, T1.day, T1.zy, T1.jfje, T1.dfje,T1.RIdOrder By T1.RId
[解决办法]
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([month] varchar(2),[day] varchar(2),[zy] varchar(8),[jfje] int,[dfje] int,[jeye] int)insert [test]select '01','01','期初余额',0,0,100000000 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',1000,0,0 union allselect '01','02','预收账款',0,12330,0with tas(select px=ROW_NUMBER()over(order by [month],[day]), * from test),mas(select *,([jeye]+jfje-dfje) as new from t where px=1union allselect a.px,a.[month],a.[day],a.zy,a.jfje,a.dfje,a.jeye,(b.new-a.jfje+a.dfje)from t ajoin m b on a.px=b.px+1)select * from m OPTION(MAXRECURSION 0)--加这一句就好了