是不是这样 select * from ( select a.id , sum(b.amount) sum_amount from yourtable a, yourtable b where b.id <= a.id group by a.id ) c where c.sum_amount < 2000按id进行amount累积
Lastdrop(空杯):你写的可以达到这个效果 不过速度上。(我算了下,如果表中有1000条记录,用这个查询用12.578秒,select id,amount from table 用0.172秒)不知道您能否优化?
你可以通过程序实现的,用delphi的adodataset很容易实现 var count1,count2:integer adodataset.first; count1:=0; while not adodataset.eof do begin
count2:=adodataset.fields[1].values; count1:=count1+count2; adodataset.next; end showmessage(inttostr(count1));
再教你一招,用分析函数: select a,b from ( select a,b,sum(b) over(order by b) as flag from table) where flag<2000
sy_315(夏雨) :delphi或者c++builder中都很容易实现 但是我是在取数据集的时候用的,等取回来之后再算就晚了 developer2002(开发者2002) :厉害,刚才我在帮助里也看到了东东 如下共享: SELECT manager_id, last_name, salary, SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary RANGE UNBOUNDED PRECEDING) l_csum FROM employees;
不明白
select * from
( select a.id , sum(b.amount) sum_amount
from yourtable a, yourtable b
where b.id <= a.id
group by a.id
) c
where c.sum_amount < 2000按id进行amount累积
不过速度上。(我算了下,如果表中有1000条记录,用这个查询用12.578秒,select id,amount from table 用0.172秒)不知道您能否优化?
var count1,count2:integer
adodataset.first;
count1:=0;
while not adodataset.eof do
begin
count2:=adodataset.fields[1].values;
count1:=count1+count2;
adodataset.next;
end
showmessage(inttostr(count1));
select a,b from (
select a,b,sum(b) over(order by b) as flag from table)
where flag<2000
但是我是在取数据集的时候用的,等取回来之后再算就晚了 developer2002(开发者2002) :厉害,刚才我在帮助里也看到了东东
如下共享:
SELECT manager_id, last_name, salary,
SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees;