【】SQL高级数据处理，想尽办法，两天都没搞好，请高手留步！【】

Question

请直接看测试数据就好了，@TempTable为初始数据，@TempReslut为处理后的样式。算法也看sql脚本里面的注释请各位高手留步，给个方案，谢谢--建立测试数据
Declare @TempTable Table (ID int,VendorID varchar(20),YearMonth int,BeginData int,Debit int,Credit int,EndData int)
Insert Into @TempTable(ID,VendorID,YearMonth,BeginData,Debit,Credit,EndData)
Select 1,'001',200801,100,50,30,120
Union All Select 2,'001',200802,0,200,300,0
Union All Select 3,'001',200803,0,30,10,0
Union All Select 4,'002',200801,200,300,100,400
Union All Select 5,'002',200802,0,200,300,0
Union All Select 6,'002',200803,0,150,200,0
Union All Select 7,'003',200801,50,30,40,40
Union All Select 8,'003',200802,0,10,60,0
Union All Select 9,'003',200803,0,10,40,0
select * from @TempTable
/*算法：期初余额(BeginData)=同一个VendorID上条记录.期末余额(EndData)   
        期末余额(EndData)=期初(BeginData)+加项(Debit)-减项(Credit)
*/--处理后最终结果
Declare @TempReslut Table (ID int,VendorID varchar(20),YearMonth int,BeginData int,Debit int,Credit int,EndData int)
Insert Into @TempReslut(ID,VendorID,YearMonth,BeginData,Debit,Credit,EndData)
Select 1,'001',200801,100,50,30,120
Union All Select 2,'001',200802,120,200,300,20
Union All Select 3,'001',200803,20,30,10,40
Union All Select 4,'002',200801,200,300,100,400
Union All Select 5,'002',200802,400,200,300,300
Union All Select 6,'002',200803,300,150,200,250
Union All Select 7,'003',200801,50,30,40,40
Union All Select 8,'003',200802,40,10,60,-10
Union All Select 9,'003',200803,-10,10,40,-40
select * from @TempReslut----------------处理语句(Begin)----------------------------------------处理语句(End)------------------------

Answer 1

--建立测试数据
Declare @TempTable Table (ID int,VendorID varchar(20),YearMonth int,BeginData int,Debit int,Credit int,EndData int)
Insert Into @TempTable(ID,VendorID,YearMonth,BeginData,Debit,Credit,EndData)
Select 1,'001',200801,100,50,30,120
Union All Select 2,'001',200802,0,200,300,0
Union All Select 3,'001',200803,0,30,10,0
Union All Select 4,'002',200801,200,300,100,400
Union All Select 5,'002',200802,0,200,300,0
Union All Select 6,'002',200803,0,150,200,0
Union All Select 7,'003',200801,50,30,40,40
Union All Select 8,'003',200802,0,10,60,0
Union All Select 9,'003',200803,0,10,40,0 
select *,EndData=BeginData+Debit-Credit from (
select ID,VendorID,YearMonth,BeginData=
isnull((select sum(begindata)+sum(debit)-sum(credit)
from @TempTable where vendorid=t.vendorid and yearmonth<t.yearmonth),begindata),
Debit,Credit
 from @TempTable t)a  
/*
ID          VendorID             YearMonth   BeginData   Debit       Credit      EndData
----------- -------------------- ----------- ----------- ----------- ----------- -----------
1           001                  200801      100         50          30          120
2           001                  200802      120         200         300         20
3           001                  200803      20          30          10          40
4           002                  200801      200         300         100         400
5           002                  200802      400         200         300         300
6           002                  200803      300         150         200         250
7           003                  200801      50          30          40          40
8           003                  200802      40          10          60          -10
9           003                  200803      -10         10          40          -40
*/

Answer 2

--建立测试数据
Declare @TempTable Table (ID int,VendorID varchar(20),YearMonth int,BeginData int,Debit int,Credit int,EndData int)
Insert Into @TempTable(ID,VendorID,YearMonth,BeginData,Debit,Credit,EndData)
          Select 1,'001',200801,100,50,30,120
Union All Select 2,'001',200802,0,200,300,0
Union All Select 3,'001',200803,0,30,10,0
Union All Select 4,'002',200801,200,300,100,400
Union All Select 5,'002',200802,0,200,300,0
Union All Select 6,'002',200803,0,150,200,0
Union All Select 7,'003',200801,50,30,40,40
Union All Select 8,'003',200802,0,10,60,0
Union All Select 9,'003',200803,0,10,40,0
select 
    a.ID,a.VendorID,a.YearMonth,
    a.BeginData+isnull(sum(b.BeginData+b.Debit-b.Credit),0) as BeginData,
    a.Debit,a.Credit,
    a.BeginData+isnull(sum(b.BeginData+b.Debit-b.Credit),0)+a.Debit-a.Credit as EndData
from 
    @TempTable a
left join
    @TempTable b 
on 
    a.VendorID=b.VendorID and a.YearMonth>b.YearMonth
group by
    a.ID,a.VendorID,a.YearMonth,a.BeginData,a.Debit,a.Credit,a.EndData/*
ID          VendorID             YearMonth   BeginData   Debit       Credit      EndData     
----------- -------------------- ----------- ----------- ----------- ----------- ----------- 
1           001                  200801      100         50          30          120
2           001                  200802      120         200         300         20
3           001                  200803      20          30          10          40
4           002                  200801      200         300         100         400
5           002                  200802      400         200         300         300
6           002                  200803      300         150         200         250
7           003                  200801      50          30          40          40
8           003                  200802      40          10          60          -10
9           003                  200803      -10         10          40          -40
*/

Answer 3

或者:--建立测试数据
Declare @TempTable Table (ID int,VendorID varchar(20),YearMonth int,BeginData int,Debit int,Credit int,EndData int)
Insert Into @TempTable(ID,VendorID,YearMonth,BeginData,Debit,Credit,EndData)
          Select 1,'001',200801,100,50,30,120
Union All Select 2,'001',200802,0,200,300,0
Union All Select 3,'001',200803,0,30,10,0
Union All Select 4,'002',200801,200,300,100,400
Union All Select 5,'002',200802,0,200,300,0
Union All Select 6,'002',200803,0,150,200,0
Union All Select 7,'003',200801,50,30,40,40
Union All Select 8,'003',200802,0,10,60,0
Union All Select 9,'003',200803,0,10,40,0
select 
    t.ID,
    t.VendorID,
    t.YearMonth,
    t.BeginData+(select isnull(sum(BeginData+Debit-Credit),0) from @TempTable where VendorID=t.VendorID and YearMonth<t.YearMonth) as BeginData,
    t.Debit,
    t.Credit,
    t.BeginData+(select isnull(sum(BeginData+Debit-Credit),0) from @TempTable where VendorID=t.VendorID and YearMonth<t.YearMonth)+t.Debit-t.Credit as EndData
from 
    @TempTable t/*
ID          VendorID             YearMonth   BeginData   Debit       Credit      EndData     
----------- -------------------- ----------- ----------- ----------- ----------- ----------- 
1           001                  200801      100         50          30          120
2           001                  200802      120         200         300         20
3           001                  200803      20          30          10          40
4           002                  200801      200         300         100         400
5           002                  200802      400         200         300         300
6           002                  200803      300         150         200         250
7           003                  200801      50          30          40          40
8           003                  200802      40          10          60          -10
9           003                  200803      -10         10          40          -40
*/

Answer 4

;with a as(
select * from @TempTable where BeginData<>0
union all
select T.ID,T.VendorID,T.YearMonth,
BeginData=a.EndData,T.Debit,T.Credit,
EndData=isnull(a.EndData+T.Debit-T.Credit,T.EndData) 
from @TempTable t join a on a.vendorid=t.vendorid and a.yearmonth+1=t.yearmonth and t.ID<>a.ID
)
select * from a order by id

Answer 5

--建立测试数据
Declare @TempTable Table (ID int,VendorID varchar(20),YearMonth int,BeginData int,Debit int,Credit int,EndData int)
Insert Into @TempTable(ID,VendorID,YearMonth,BeginData,Debit,Credit,EndData)
Select 1,'001',200801,100,50,30,120
Union All Select 2,'001',200802,0,200,300,0
Union All Select 3,'001',200803,0,30,10,0
Union All Select 4,'002',200801,200,300,100,400
Union All Select 5,'002',200802,0,200,300,0
Union All Select 6,'002',200803,0,150,200,0
Union All Select 7,'003',200801,50,30,40,40
Union All Select 8,'003',200802,0,10,60,0
Union All Select 9,'003',200803,0,10,40,0
;with a as(
select * from @TempTable where BeginData<>0
union all
select T.ID,T.VendorID,T.YearMonth,
BeginData=a.EndData,T.Debit,T.Credit,
EndData=isnull(a.EndData+T.Debit-T.Credit,T.EndData) 
from @TempTable t join a on a.vendorid=t.vendorid and a.yearmonth+1=t.yearmonth and t.ID<>a.ID
)
select * from a order by id
/*
ID          VendorID             YearMonth   BeginData   Debit       Credit      EndData
----------- -------------------- ----------- ----------- ----------- ----------- -----------
1           001                  200801      100         50          30          120
2           001                  200802      120         200         300         20
3           001                  200803      20          30          10          40
4           002                  200801      200         300         100         400
5           002                  200802      400         200         300         300
6           002                  200803      300         150         200         250
7           003                  200801      50          30          40          40
8           003                  200802      40          10          60          -10
9           003                  200803      -10         10          40          -40
*/

Answer 6

谢谢各位有没有用update的方法啊

Answer 7

实际的数据量是很大的感觉能用 Updata 速度会快很多谢谢各位

Answer 8

--建立测试数据
Declare @TempTable Table (ID int,VendorID varchar(20),YearMonth int,BeginData int,Debit int,Credit int,EndData int)
Insert Into @TempTable(ID,VendorID,YearMonth,BeginData,Debit,Credit,EndData)
Select 1,'001',200801,100,50,30,120
Union All Select 2,'001',200802,0,200,300,0
Union All Select 3,'001',200803,0,30,10,0
Union All Select 4,'002',200801,200,300,100,400
Union All Select 5,'002',200802,0,200,300,0
Union All Select 6,'002',200803,0,150,200,0
Union All Select 7,'003',200801,50,30,40,40
Union All Select 8,'003',200802,0,10,60,0
Union All Select 9,'003',200803,0,10,40,0
--select * from @TempTable
select ID,VendorID,YearMonth
,BeginData=isnull((select sum(BeginData)+sum(Debit)-sum(Credit) from @TempTable where VendorID=t.VendorID and id<t.id),BeginData)
,Debit,Credit
,EndData=(select sum(BeginData)+sum(Debit)-sum(Credit) from @TempTable where VendorID=t.VendorID and id<=t.id)
from @TempTable t
/*
ID          VendorID             YearMonth   BeginData   Debit       Credit      EndData
----------- -------------------- ----------- ----------- ----------- ----------- -----------
1           001                  200801      100         50          30          120
2           001                  200802      120         200         300         20
3           001                  200803      20          30          10          40
4           002                  200801      200         300         100         400
5           002                  200802      400         200         300         300
6           002                  200803      300         150         200         250
7           003                  200801      50          30          40          40
8           003                  200802      40          10          60          -10
9           003                  200803      -10         10          40          -40(9 行受影响)
*/

Answer 9

update @tempResult set BeginData=b.BeginData,EndData=b.EndData
from @tempResult a,(
select *,EndData=BeginData+Debit-Credit from (
select ID,VendorID,YearMonth,BeginData=
isnull((select sum(begindata)+sum(debit)-sum(credit)
from @TempTable where vendorid=t.vendorid and yearmonth<t.yearmonth),begindata),
Debit,Credit
 from @TempTable t)a)b
on a.vendorid=b.vendorid and a.yearmonth=b.yearmonth

Answer 10

Declare @TempTable Table (ID int,VendorID varchar(20),YearMonth int,BeginData int,Debit int,Credit int,EndData int)
Insert Into @TempTable(ID,VendorID,YearMonth,BeginData,Debit,Credit,EndData)
Select 1,'001',200801,100,50,30,120
Union All Select 2,'001',200802,0,200,300,0
Union All Select 3,'001',200803,0,30,10,0
Union All Select 4,'002',200801,200,300,100,400
Union All Select 5,'002',200802,0,200,300,0
Union All Select 6,'002',200803,0,150,200,0
Union All Select 7,'003',200801,50,30,40,40
Union All Select 8,'003',200802,0,10,60,0
Union All Select 9,'003',200803,0,10,40,0
 
update @TempTable set BeginData=b.BeginData,EndData=b.EndData
from @TempTable a,(
select *,EndData=BeginData+Debit-Credit from (
select ID,VendorID,YearMonth,BeginData=
isnull((select sum(begindata)+sum(debit)-sum(credit)
from @TempTable where vendorid=t.vendorid and yearmonth <t.yearmonth),begindata),
Debit,Credit
from @TempTable t)a)b
where a.vendorid=b.vendorid and a.yearmonth=b.yearmonth select * from @TempTable

Answer 11

--建立测试数据
Declare @TempTable Table (ID int,VendorID varchar(20),YearMonth int,BeginData int,Debit int,Credit int,EndData int)
Insert Into @TempTable(ID,VendorID,YearMonth,BeginData,Debit,Credit,EndData)
Select 1,'001',200801,100,50,30,120
Union All Select 2,'001',200802,0,200,300,0
Union All Select 3,'001',200803,0,30,10,0
Union All Select 4,'002',200801,200,300,100,400
Union All Select 5,'002',200802,0,200,300,0
Union All Select 6,'002',200803,0,150,200,0
Union All Select 7,'003',200801,50,30,40,40
Union All Select 8,'003',200802,0,10,60,0
Union All Select 9,'003',200803,0,10,40,0
--select * from @TempTabledeclare @VendorID varchar(20),@bd int,@ed int
update @TempTable
set @bd=case when VendorID=@VendorID then @ed else BeginData end
,@ed=case when VendorID=@VendorID then @ed+Debit-Credit else EndData end
,@VendorID=VendorID
,BeginData=@bd
,EndData=@edselect * from @TempTable
/*
ID          VendorID             YearMonth   BeginData   Debit       Credit      EndData
----------- -------------------- ----------- ----------- ----------- ----------- -----------
1           001                  200801      100         50          30          120
2           001                  200802      120         200         300         20
3           001                  200803      20          30          10          40
4           002                  200801      200         300         100         400
5           002                  200802      400         200         300         300
6           002                  200803      300         150         200         250
7           003                  200801      50          30          40          40
8           003                  200802      40          10          60          -10
9           003                  200803      -10         10          40          -40(9 行受影响)
*/

Answer 12

--建立测试数据
Declare @TempTable Table (ID int,VendorID varchar(20),YearMonth int,BeginData int,Debit int,Credit int,EndData int)
Insert Into @TempTable(ID,VendorID,YearMonth,BeginData,Debit,Credit,EndData)
Select 1,'001',200801,100,50,30,120
Union All Select 2,'001',200802,0,200,300,0
Union All Select 3,'001',200803,0,30,10,0
Union All Select 4,'002',200801,200,300,100,400
Union All Select 5,'002',200802,0,200,300,0
Union All Select 6,'002',200803,0,150,200,0
Union All Select 7,'003',200801,50,30,40,40
Union All Select 8,'003',200802,0,10,60,0
Union All Select 9,'003',200803,0,10,40,0;with a as(
select * from @TempTable where BeginData<>0
union all
select T.ID,T.VendorID,T.YearMonth,
BeginData=a.EndData,T.Debit,T.Credit,
EndData=isnull(a.EndData+T.Debit-T.Credit,T.EndData) 
from @TempTable t join a on a.vendorid=t.vendorid and a.yearmonth+1=t.yearmonth and t.ID<>a.ID
)
update @TempTable set BeginData=a.BeginData,EndData=a.EndData from @TempTable b join a on a.id=b.idselect * from @TempTable order by id
/*
ID          VendorID             YearMonth   BeginData   Debit       Credit      EndData
----------- -------------------- ----------- ----------- ----------- ----------- -----------
1           001                  200801      100         50          30          120
2           001                  200802      120         200         300         20
3           001                  200803      20          30          10          40
4           002                  200801      200         300         100         400
5           002                  200802      400         200         300         300
6           002                  200803      300         150         200         250
7           003                  200801      50          30          40          40
8           003                  200802      40          10          60          -10
9           003                  200803      -10         10          40          -40
*/