表结构及数据如下:
test_id group_name quantity
1 a 10
2 b 20
3 c 30
4 d 40
5 e 50需求:当前行的quantity与它的前两行的quantity累计结构表数据如下:
test_id group_name quantity sum_quantity
1 a 10 10
2 b 20 30 (10+20)
3 c 30 60 (10+20+30)
4 d 40 90 (20+30+40)
5 e 50 120 (30+40+50)请高人指点用SQL如何实现?
test_id group_name quantity
1 a 10
2 b 20
3 c 30
4 d 40
5 e 50需求:当前行的quantity与它的前两行的quantity累计结构表数据如下:
test_id group_name quantity sum_quantity
1 a 10 10
2 b 20 30 (10+20)
3 c 30 60 (10+20+30)
4 d 40 90 (20+30+40)
5 e 50 120 (30+40+50)请高人指点用SQL如何实现?
--用分析函数实现:
select test_id, group_name, quantity,sum(quantity)over(order by test_id) sum_quantity
from 表;
--没测试,你自己运行下看看!
select test_id,
group_name,
quantity,
quantity+lag(quantity,1,0)over(order by test_id) sum_quantity
from 你的表名
--我想到三种方法:--第一种:自连接
select b.test_id,b.group_name,sum(a.quantity) from table_name a,table_name b
where a.test_id<=b.test_id
group by b.test_id,b.group_name
order by b.test_id,b.group_name;--第二种:子查询
select a.test_id,a.group_name,(select sum(b.quantity) from table_name b where b.test_id<=a.test_id) from table_name a;--第三种:利用Oracle8.1.6开始提供的分析函数实现(超简单!)。
select test_id, group_name, quantity,sum(quantity)over(order by test_id) sum_quantity
from table_name;
第一、第二,你改一下就OK了啊!
select a.test_id, a.group_name, a.quantity,b.sum_quantity
from table_name a,
(select b.test_id,b.group_name,sum(a.quantity) from table_name a,table_name b
where a.test_id<=b.test_id
group by b.test_id,b.group_name
order by b.test_id,b.group_name
)b
where a.test_id=b.test_id;
呵呵
--没测试,你试下
select test_id, group_name, quantity,
sum(quantity)over(order by test_id rows between 2 preceding and current row) sum_quantity
from table_name;
group_name,
quantity,
quantity+lag(quantity,1,0)over(order by test_id)+lag(quantity,2,0)over(order by test_id) sum_quantity
from test经过测试此语句能够达到目的,谢谢gelyon以及回帖的人。
with tmp as
(select t.test_id, lag(test_id) over(order by test_id) l1
from TEST t
union all
select t.test_id, lag(test_id, 2) over(order by test_id) l1 from TEST t)
select tt.*,
(select sum(t.quantity)
from test t
where t.test_id in
(select l1 from tmp where tmp.test_id = tt.test_id)
or t.test_id = tt.test_id) sq
from test tt