Oracle 窗口函数over()与窗口子语句
发布时间:2020-12-12 15:30:21 所属栏目:百科 来源:网络整理
导读:drop table t purge;create table t(id number,deptno number,name varchar2(20),sal number);insert into t values(1,1,'1aa',120);insert into t values(2,'2aa',300);insert into t values(3,'3aa','100');insert into t values(4,'4aa',99,);insert into
drop table t purge; create table t(id number,deptno number,name varchar2(20),sal number); insert into t values(1,1,'1aa',120); insert into t values(2,'2aa',300); insert into t values(3,'3aa','100'); insert into t values(4,'4aa',99,); insert into t values(5,'5aa',90); insert into t values(6,2,'6aa',87); insert into t values(7,'7aa',500); insert into t values(8,'8aa',200); insert into t values(9,'9aa',20); insert into t values(10,'10aa',30); deptno 为部门号,sal为薪水 --获取每个部门薪水的总数 select t.*,sum(sal)over(partition by deptno order by sal desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_value from t t; --last_value(),sum()为分析函数,over为开窗函数(窗口函数:分析函数分析时要处理的数据范围), --形式如:over(partition by xxx order by yyy rows between zzz) --窗口子句:UNBOUNDED PRECEDING表示数据范围的第一行 --current row表示数据范围的当前行 --UNBOUNDED FOLLOWING表示数据范围的最后一行 查询出的值为: 看另一实例: select t.*,sum(sal)over(partition by deptno order by sal asc ROWS BETWEEN UNBOUNDED PRECEDING AND current row) as last_value from t t; 查询出的值为: 行转列:pivot(max(..) ... for(分组的字段)in(分组的值)) --行转列 select * from (select sal,deptno from t) pivot(max(sal) as max_sal,sum(sal)as sum_sal,avg(sal) as avg_sal for(deptno) in(1,2)) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |