create table t_temp as
select ip,year,month,sum(num) snum from tbname
group by ip,year,month;delete from tbname t where exists(
select 1 from t_temp tt
where t.ip=tt.ip and t.year=tt.year and t.month=tt.month);insert into tbname select * from t_temp; commit;drop table t_temp;
select ip,year,month,sum(num) snum from tbname
group by ip,year,month;delete from tbname t where exists(
select 1 from t_temp tt
where t.ip=tt.ip and t.year=tt.year and t.month=tt.month);insert into tbname select * from t_temp; commit;drop table t_temp;
上述的问题解决了,多谢!不好意思,再问一个小问题。表如下所示:
ip year month num
10.1.1.1 2004 9 100
10.1.1.2 2004 9 50
10.1.1.1 2004 10 26
10.1.2.3 2004 10 18
如何能取出表中相同年和月的前十个num数,比如说要取2004年10月份的num前十位数据插入到新表当中,如何实现,谢谢!
select ip,year,month,num from (
select ip,year,month,num,rank() over(order by num) rk
from tbname where year='2004' and month='10'
) t
where t.rk<=10;