Select x,sum(y) from t group by rollup(x) having grouping(x)=0 ctrl+c一下
第一种实现方法: select x,y,sum(y) over(partition by x order by rownum) from t第二种实现方法: select x,sum(y) from t group by rollup(x,rownum) HAVING GROUPING(ID) = 0
刚才写错了个地方,改正下:第一种实现方法: select x,y,sum(y) over(partition by x order by rownum) from t第二种实现方法: select x,sum(y) from t group by rollup(x,rownum) HAVING GROUPING(x) = 0
select x,y,y z from ttt where (x,y) not in ( select x,max(y) y from ttt group by x) union select a.x,max(a.y) y,sum(a.y) z from ttt a group by a.x
the second: create table ttt_temp as select * from ttt; insert into ttt select x,sum(y) y from ttt group by x; commit; select * from ttt_tmp order by x;
ctrl+c一下
select x,y,sum(y) over(partition by x order by rownum) from t第二种实现方法:
select x,sum(y) from t group by rollup(x,rownum) HAVING GROUPING(ID) = 0
select x,y,sum(y) over(partition by x order by rownum) from t第二种实现方法:
select x,sum(y) from t group by rollup(x,rownum) HAVING GROUPING(x) = 0
where (x,y) not in (
select x,max(y) y from ttt
group by x)
union
select a.x,max(a.y) y,sum(a.y) z from ttt a
group by a.x
create table ttt_temp
as select * from ttt;
insert into ttt select x,sum(y) y from ttt group by x;
commit;
select * from ttt_tmp order by x;