SELECT t.*,SUM(t.cnt) OVER(PARTITION BY t.itemid ORDER BY t.score) FROM t
你的描述有问题,按照你的描述根本得不到你要求的结果,根据你要求得出的结果,反向推测,应该是这样: 以itemid分组,当score 为本组最小值时输出cnt,否则输出在组内小于等于本score的所有cnt之和。 这样就简单了: create table test(itemid NUMBER(8),score NUMBER(8,2),cnt NUMBER(8)) insert into test(itemid,score,cnt) select 1, 0, 3 from dual union all select 1 ,2, 2 from dual union all select 1, 3, 1 from dual union all select 1, 4 ,2 from dual union all select 1, 5 ,8 from dual union all select 1, 6 ,10 from dual union all select 1, 10, 3 from dual union all select 1, 15, 5 from dual union all select 8, 0 ,2 from dual union all select 8, 0.5, 3 from dual union all select 8, 1 ,5 from dual union all select 10, 2, 4 from dual union all select 10, 2.5, 3 from dual with astest as(select row_number() over(partition by itemid order by rowid) NO,itemid,score,cnt from test) select a.itemid,a.score,a.cnt,(select sum(b.cnt) from astest b where b.itemid=a.itemid and b.NO<=a.NO) n from astest a order by a.rowid
SQL> WITH t AS ( 2 SELECT 1 itemid,0 score,3 cnt FROM dual UNION ALL 3 SELECT 1 itemid,2 score,2 cnt FROM dual UNION ALL 4 SELECT 1 itemid,3 score,1 cnt FROM dual UNION ALL 5 SELECT 1 itemid,4 score,2 cnt FROM dual UNION ALL 6 SELECT 1 itemid,5 score,8 cnt FROM dual UNION ALL 7 SELECT 1 itemid,6 score,10 cnt FROM dual UNION ALL 8 SELECT 1 itemid,10 score,3 cnt FROM dual UNION ALL 9 SELECT 1 itemid,15 score,5 cnt FROM dual UNION ALL 10 SELECT 8 itemid,0 score,2 cnt FROM dual UNION ALL 11 SELECT 8 itemid,0.5 score,3 cnt FROM dual UNION ALL 12 SELECT 8 itemid,1 score,5 cnt FROM dual UNION ALL 13 SELECT 10 itemid,2 score,4 cnt FROM dual UNION ALL 14 SELECT 10 itemid,2.5 score,3 cnt FROM dual 15 ) 16 SELECT t.*, 17 SUM(t.cnt) OVER(PARTITION BY t.itemid ORDER BY t.score) n 18 FROM t 19 ;
SELECT t.*,SUM(t.cnt) OVER(PARTITION BY t.itemid ORDER BY t.score) FROM t
以itemid分组,当score 为本组最小值时输出cnt,否则输出在组内小于等于本score的所有cnt之和。
这样就简单了:
create table test(itemid NUMBER(8),score NUMBER(8,2),cnt NUMBER(8))
insert into test(itemid,score,cnt)
select 1, 0, 3 from dual union all
select 1 ,2, 2 from dual union all
select 1, 3, 1 from dual union all
select 1, 4 ,2 from dual union all
select 1, 5 ,8 from dual union all
select 1, 6 ,10 from dual union all
select 1, 10, 3 from dual union all
select 1, 15, 5 from dual union all
select 8, 0 ,2 from dual union all
select 8, 0.5, 3 from dual union all
select 8, 1 ,5 from dual union all
select 10, 2, 4 from dual union all
select 10, 2.5, 3 from dual with astest as(select row_number() over(partition by itemid order by rowid) NO,itemid,score,cnt from test)
select a.itemid,a.score,a.cnt,(select sum(b.cnt) from astest b where b.itemid=a.itemid and b.NO<=a.NO) n
from astest a
order by a.rowid
SQL> WITH t AS (
2 SELECT 1 itemid,0 score,3 cnt FROM dual UNION ALL
3 SELECT 1 itemid,2 score,2 cnt FROM dual UNION ALL
4 SELECT 1 itemid,3 score,1 cnt FROM dual UNION ALL
5 SELECT 1 itemid,4 score,2 cnt FROM dual UNION ALL
6 SELECT 1 itemid,5 score,8 cnt FROM dual UNION ALL
7 SELECT 1 itemid,6 score,10 cnt FROM dual UNION ALL
8 SELECT 1 itemid,10 score,3 cnt FROM dual UNION ALL
9 SELECT 1 itemid,15 score,5 cnt FROM dual UNION ALL
10 SELECT 8 itemid,0 score,2 cnt FROM dual UNION ALL
11 SELECT 8 itemid,0.5 score,3 cnt FROM dual UNION ALL
12 SELECT 8 itemid,1 score,5 cnt FROM dual UNION ALL
13 SELECT 10 itemid,2 score,4 cnt FROM dual UNION ALL
14 SELECT 10 itemid,2.5 score,3 cnt FROM dual
15 )
16 SELECT t.*,
17 SUM(t.cnt) OVER(PARTITION BY t.itemid ORDER BY t.score) n
18 FROM t
19 ;
ITEMID SCORE CNT N
---------- ---------- ---------- ----------
1 0 3 3
1 2 2 5
1 3 1 6
1 4 2 8
1 5 8 16
1 6 10 26
1 10 3 29
1 15 5 34
8 0 2 2
8 0.5 3 5
8 1 5 10
10 2 4 4
10 2.5 3 7
13 rows selected
http://www.linuxidc.com/Linux/2011-04/34063.htm 给个over()求和的链接