Oracle 窗口函数over()与窗口子语句

2017-01-05 11:09:42来源:oschina作者:pro_jerry人点击

drop table t purge;
createtable t(id number,deptno number,name varchar2(20),sal number);
insert into t values(1,1,'1aa',120);
insert into t values(2,1,'2aa',300);
insert into t values(3,1,'3aa','100');
insert into t values(4,1,'4aa',99,);
insert into t values(5,1,'5aa',90);
insert into t values(6,2,'6aa',87);
insert into t values(7,2,'7aa',500);
insert into t values(8,2,'8aa',200);
insert into t values(9,2,'9aa',20);
insert into t values(10,2,'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;

查询出的值为:



最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台