ORACLE 聚合查询小例子

来源：互联网发布：node.js爬虫编辑：程序博客网时间：2024/05/16 07:20

select * from emp;

select deptno,         ename,         sal,         sum(sal) over() 公司总工资,         100 * round(sal / （sum(sal) over() ）, 4) 工资百分比,         sum(sal) over(order by ename) 公司内工资递加,         sum(sal) over(partition by deptno order by ename) 部门内工资递加    from emp   order by deptno;

select deptno,         ename,         sal,         rank() over(partition by deptno order by sal desc) rank    from emp;

select deptno,         ename,         sal,         dense_rank() over(partition by deptno order by sal desc) rank    from emp;

select deptno,         ename,         sal,         row_number() over(partition by deptno order by sal desc) rank    from emp;

select deptno,         ename,         sal,         max(sal) over() 公司最高,         min(sal) over(partition by deptno) 部门最低,         max(sal) over(partition by deptno) 部门最高,         nvl(sal - min(sal) over(partition by deptno), 0) 比最低多    from emp;

阅读全文

0 0