求一条sql语句，谢谢各位

[dongdonghe1]

有下列的数据
col1 col2 col3
13.3 1 'NEW'
24.8 1 'ADD'
20 2 'NEW'
164 2 'ADD'
128 3 'NEW'
413.6 3 'ADD'
1061.6 4 'ADD'
把上面的数据变成以下的形式
col1 col2 col3
38.1 1 'NEW'
184 2 'NEW'
541.6 3 'NEW'
1061.6 4 'ADD'
请问如何实现，谢谢各位了

[doer_ljy]

如楼上所说，使用分析函数。

[tianyacao007]

^_^，强！学习

[tgm78]

sys@TASCII>select col1, col2, col3 from
2 (select sum(col1)over(partition by col2) as col1, col2, col3, row_number()o
ver(partition by col2 order by rownum )rn from (
3 select 13.3 as col1, 1 as col2, 'NEW' as col3 from dual union
4 select 24.8 as col1, 1 as col2, 'ADD' as col3 from dual union
5 select 20 as col1, 2 as col2, 'NEW' as col3 from dual union
6 select 164 as col1, 2 as col2, 'ADD' as col3 from dual union
7 select 128 as col1, 3 as col2, 'NEW' as col3 from dual union
8 select 413.6 as col1, 3 as col2, 'ADD' as col3 from dual union
9 select 1061.6 as col1, 4 as col2, 'ADD' as col3 from dual ))
10 where rn = 1
11 /

COL1 COL2 COL
---------- ---------- ---
38.1 1 NEW
184 2 NEW
541.6 3 NEW
1061.6 4 ADD


强！

[沝林]

col3就两种值么？

select sum(col1) col1, col2, max(col3) col3 from table group by col2

[xiaoxiao1984]

sys@TASCII>select col1, col2, col3 from
2 (select sum(col1)over(partition by col2) as col1, col2, col3, row_number()o
ver(partition by col2 order by rownum )rn from (
3 select 13.3 as col1, 1 as col2, 'NEW' as col3 from dual union
4 select 24.8 as col1, 1 as col2, 'ADD' as col3 from dual union
5 select 20 as col1, 2 as col2, 'NEW' as col3 from dual union
6 select 164 as col1, 2 as col2, 'ADD' as col3 from dual union
7 select 128 as col1, 3 as col2, 'NEW' as col3 from dual union
8 select 413.6 as col1, 3 as col2, 'ADD' as col3 from dual union
9 select 1061.6 as col1, 4 as col2, 'ADD' as col3 from dual ))
10 where rn = 1
11 /

COL1 COL2 COL
---------- ---------- ---
38.1 1 NEW
184 2 NEW
541.6 3 NEW
1061.6 4 ADD