【学习笔记】数据库ORACLE实练（1）排序

题目：用SQL语句查询工资表中薪水第N高/低的数额。（本文暂不考虑NULL的情况）

1.  N=1的情况

即查出最高薪水数额为几。简单地对employee表中的salary属性进行max函数求出最大值，然后将此值命名为HighestSalary即可。(*^▽^*)

select max(salary) as HighestSalary
from emloyee

2. N=2的情况

即查出次高薪水数额为几。此时有两种思路：（1）最直观的想法：对所有薪水进行排序，通过函数求出其中排序为第二位的薪水数额；（2）结合第一问的结果，通过递归思想，以“去掉最高薪水后的最高薪水即为次高薪水”重新理解这里的N=2。

代码实现分别为：

select max(salary) as SecondHighestSalary
from ( select dense_rank() over (order by salary desc) as arank , salary 
from Employee order by salary desc )
where arank = 2;

select max(salary) as SecondHighestSalary
from employee
where salary != (select max(salary) from employee)

3.推广到第N位薪水啦

其实和N=2中的第一种思路是一样的，改变rank=n即可。因为本题只需要得到第n位薪水的数额（即第n梯队的薪水数额），所以注意用dense_rank（）函数即可求得（用distinct（）函数更改原数据也可实现）。

 

￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥

 

知识点：SQL的常用排序函数

1.row_number（）将原始数据直接按照指定的方式排序，且无论数据是否重复，每行数据均强制赋一个顺序号；后续可由序号n调出该排序规则中第n条对应的原始数据。

select row_number() over (order by salary desc) as rank1 , *
from salary

 最终序号中没有1,2,2,3这种情况出现，必然都是1,2,3,4，…

 

2.rank（）将原始数据按照指定属性进行排序，相同值有同样的序号，但均占层。

select rank() over (order by salary desc) as rank2 , *
from salary

相同数据会并列处理，例如薪水表中这四个数据：3000,2000,2000,1000；rank（）函数得到的序号分别为15,16,16,18.  也就是说，如果用rank（）函数，第二个2000元占了第17层房间，虽然序号也是16，但其实占了17层的位置。这确保了排在第18位的数据前一定只有17个值，即严格按“一个一层”来排序，重复值均同等占多“层”顺序位。

 

3.dense_rank（）将原始数据按照指定属性进行排序，相同值被放在同一层，下一个值才开始继续记入序号。特殊情况需记得使用。

select dense_rank() over (order by salary desc) as rank3 , *
from salary

和rank（）函数不同之处在于，上例中dense_rank（）函数得到的序号是15,16,16,17. 也就是说两个2000元被并列放在了第16层的房间内，于是1000便顺位占了第17层。这确保了能完整取到前16层的数据，比如若想用rank（）函数取前16位薪水对应的职员名，便会漏掉第二个2000元的职员。此时应选择dense_rank（）函数。确保取够“位”。

 

4.ntile（）不同于每条数据都有一个编号的以上三个函数，ntile（n）将原始数据按照指定顺序与规则划分为n个“块”，每个“块”才有顺序号，具体到“块”里的数据，其编号与“块”的顺序号相同。

select ntile(3) over (order by salary desc) as rank4 , *
from salary

分块规则：每块数据条数 = （未分组数据条数 / 现余块数）+1 。若除尽无余数，则不+1 。

例如，10条排好序的数据用ntile(3)来划分。（10 / 3）+1 = 4 ，第一块中就有4条数据； （6 / 2）=3 ，第二块中就有3条数据；以此类推。最终编号结果为：1,1,1,1,2,2,2,3,3,3,4,4,4.