oracle_sql高级查询.docVIP

一、rank /dense_rank over partition by ...order by ... 现在客户有这样一个需求，查询每个部门工资最高的雇员的信息，相信有一定oracle应用知识的同学都能写出下面的SQL语句： [sql] view plaincopy select e.ename, e.job, e.sal, e.deptno from scott.emp e, select e.deptno, max e.sal sal from scott.emp e group by e.deptno me where e.deptno me.deptno and e.sal me.sal; 在满足客户需求的同时，大家应该习惯性的思考一下是否还有别的方法。这个是肯定的，就是使用本小节标题中rank over partition by... 或dense_rank over partition by... 语法，SQL分别如下： [sql] view plaincopy select e.ename, e.job, e.sal, e.deptno from select e.ename, e.job, e.sal, e.deptno, rank over partition by e.deptno order by e.sal desc rank from scott.emp e e where e.rank 1; [sql] view plaincopy select e.ename, e.job, e.sal, e.deptno from select e.ename, e.job, e.sal, e.deptno, dense_rank over partition by e.deptno order by e.sal desc rank from scott.emp e e where e.rank 1; 为什么会得出跟上面的语句一样的结果呢？这里补充讲解一下rank /dense_rank over partition by e.deptno order by e.sal desc 语法。over: 在什么条件之上。partition by e.deptno: 按部门编号划分（分区）。order by e.sal desc: 按工资从高到低排序（使用rank /dense_rank 时，必须要带order by否则非法）rank /dense_rank : 分级整个语句的意思就是：在按部门划分的基础上，按工资从高到低对雇员进行分级，“级别”由从小到大的数字表示（最小值一定为1）。 那么rank 和dense_rank 有什么区别呢？rank : 跳跃排序，如果有两个第一级时，接下来就是第三级。dense_rank : 连续排序，如果有两个第一级时，接下来仍然是第二级。 小作业：查询部门最低工资的雇员信息。 二、min /max over partition by ... 现在我们已经查询得到了部门最高/最低工资，客户需求又来了，查询雇员信息的同时算出雇员工资与部门最高/最低工资的差额。这个还是比较简单，在第一节的groupby语句的基础上进行修改如下： [sql] view plaincopy select e.ename, e.job, e.sal, e.deptno, e.sal - me.min_sal diff_min_sal, me.max_sal - e.sal diff_max_sal from scott.emp e, select e.deptno, min e.sal min_sal, max e.sal max_sal from scott.emp e group by e.deptno me where e.deptno me.deptno order by e.deptno, e.sal; 上面我们用到了min 和max ，前者求最小值，后者求最大值。如果这两个方法配合over partition by ... 使用会是什么效果呢？大家看看下面的SQL语句： [sql] view plaincopy select e.ename, e.job, e.sal, e.deptno, nvl e.sal - min e.sal over partition by e.deptno , 0 diff_min_sal, nvl max e.sal over partition by e.deptno - e.sal, 0 diff_max_s