有什么办法可以使得某个字段的任一个记录的值是该记录前所有记录的合计值？(50分)

C

czlucky

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-08

• #1

有什么办法可以使得任一个记录的值是该记录前所有记录的合计值，如：

记录号 当月值 此前合计
1 50 50
2 100 150
3 20 170
4....
用DBGrid之类的控件显示，可以在DBGrid的事件中处理，或者是在数据集里利用SQL、计算字段？

 

W

weekboy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-08

• #2

var temtotalvalue:integer;
temmax:integer;
sql.add('select max(id) ,sum(crrentvalue) from youtable');
open;
temtotalvalue:=query1.fields[1].value;
temmax:=query1.fields[0].value+1;
close;
sql.clear;
sql.add('insert into youtable(id,currentvalue,totalvalue)');
sql.add(valuestemid,:temcrvalue,:temtlvalue);
parambyname('temid'):=temmax;
parambyname('temcrvalue').asinteger:=youcurrentvalue;
parambyname('totalvalue').asinteger:=temtotalvalue+youcurrentvalue;
execsql;

 

W

wonsfon

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-08

• #3

update 表名 set 此前合计=(select sum(当月值) from 表名 where 记录号<= a.记录号 ) from 表名 a

 

K

kkyy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-08

• #4

用触发器for update,

 

K

Kisber

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #5

select t1.记录号, t1.当月值, 此前合计 = (select sum(当月值) from table t2 where
t2.记录号 <= t1.记录号) from table t1

 

L

leozmy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-09

• #6

select t1.id,t1.a,
sum(case when t1.id>=t2.id then t2.a end)
from table1 t1,table1 t2
group by t1.id,t1.a

 

C

czlucky

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-12

• #7

请看我的代码：
SELECT Stock.Date, Stock.BillNo, Stock.Explain, Tmp.Payout, (Select Sum(Number*Price) From StockWare Where BillNo <= Stock.BillNo)
FROM Stock INNER JOIN (Select BillNo, Sum(Number*Price) As Payout From StockWare Group By BillNo) AS Tmp ON Stock.BillNo = Tmp.BillNo
WHERE (Stock.Date Between :stDate And :enDate) And (Stock.ClientNo = :ClientNo)
ORDER BY Stock.BillNo
由于我是要要对“ClientNo”进行分类，所以上面的代码就出现错误值，它不管分类，所有的的值都相加，而“StockWare”表又没有“ClientNo”字段，怎么办？

 

K

Kisber

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-13

• #8

SELECT S.ClientNo, S.Date, S.BillNo, S.Explain, Sum(W1.Number*W1.Price) AS Payout
, Sum(W2.Number*W2.Price) AS AddupPayout
FROM Stock S INNER JOIN StockWare W1 ON (W1.BillNo = S.BillNo)
INNER JOIN StockWare W2 ON (W2.BillNo <= S.BillNo)
GROUP BY S.ClientNo, S.Date, S.BillNo, S.Explain
ORDER BY S.BillNo

 

L

LeeChange

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-01-17

• #9

select a.记录号， a.当月值，
(select sum(当月值) from TableName b where b.记录号<a.记录号) as 当前合计
from TableName a