这是我网上看到的题,感觉方法较好,想用,但是好像不行,mysql能用吗??create table TempTable(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)
这不是那天我写的,大概是这样的:
create procedure spupdate()
begin
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=@ed;
select * from TempTable;
end
我写好报错,我问sql群别人说好像是set 不支持变量使用,但
是我纳闷存储过程怎么把我数据给改了???
另外,这道题有什么好的方法么????
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)
这不是那天我写的,大概是这样的:
create procedure spupdate()
begin
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=@ed;
select * from TempTable;
end
我写好报错,我问sql群别人说好像是set 不支持变量使用,但
是我纳闷存储过程怎么把我数据给改了???
另外,这道题有什么好的方法么????
+------+----------+-----------+-----------+-------+--------+---------+
| ID | VendorID | YearMonth | BeginData | Debit | Credit | EndData |
+------+----------+-----------+-----------+-------+--------+---------+
| 1 | 001 | 200801 | 100 | 50 | 30 | 120 |
| 2 | 001 | 200802 | 0 | 200 | 300 | 0 |
| 3 | 001 | 200803 | 0 | 30 | 10 | 0 |
| 4 | 002 | 200801 | 200 | 300 | 100 | 400 |
| 5 | 002 | 200802 | 0 | 200 | 300 | 0 |
| 6 | 002 | 200803 | 0 | 150 | 200 | 0 |
| 7 | 003 | 200801 | 50 | 30 | 40 | 40 |
| 8 | 003 | 200802 | 0 | 10 | 60 | 0 |
| 9 | 003 | 200803 | 0 | 10 | 40 | 0 |
+------+----------+-----------+-----------+-------+--------+---------+
9 rows in set (0.05 sec)mysql>你期望的结果是什么?
期末余额(EndData)=期初(BeginData)+加项(Debit)-减
+----+----------+-----------+-----------+-------+--------+---------+
| 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 |
+----+----------+-----------+-----------+-------+--------+---------+
用别的方法很繁琐。
select col into @var
但对仅对这个问题来说,用select 查询就行了。