高级查询

　　本篇文章示例介绍的查询操作不同于其他查询操作，它们体现了不同的查询思路，需要以每次一页的方式显示结果集。

 

1.在结果集中翻页

　　问题：返回员工表中薪水排名前五的员工，然后再返回接下来排名前五的员工。

　　解决方案：这个问题看起来很简单，只需要根据“薪水”这一列进行倒序排序，然后分页操作即可。但是如果存在重复数据时就会有问题。

　　先按照“薪水”这一列进行倒序排序：

　　

 

　　然后分页取排名前五的数据：

　　

 

 

 　　看似没问题，但是题目要求返回的是薪水排名前五的数据。因为这里有薪水相同的数据，返回的数据其实不止五条。应该返回下面框起来的数据：

　　

 

 

 　　遇到这种情况，需要使用窗函数 rank() over 给每条数据进行排名（当前行在其分区内的排名，有间隙。要无间隙可以使用 dense_rank 。不使用ROW_NUMBER() ，该函数不区分使用并列），然后使用 between and 语法分页。

select * from (
SELECT *,rank() over(order by SAL desc) as rn FROM test.emps ) a where rn between 1 and 5

　　

 

 

 

2.在表中跳过 n 行数据

　　问题：以每次跳过一人的方式返回员工表中的数据。换言之，想返回第一个员工，第三个员工 ......

　　解决方案：先对结果集排序，否则不存在“第一个”，“第二个”，“下一个”的概念。然后使用窗函数 ROW_NUMBER() 给每一行进行编号，再结合这些编号和求模函数 mod() 跳过不想返回的行。

select *,mod(rn,2) from (
select *,row_number() over(order by id) as rn from emps ) a
where mod(rn,2) = 1

　　

 

 

 

3.确定哪些行是互逆的

　　问题：有一张包含两次考试结果的表，想确定哪两组成绩是互逆的。

　　

 

 

 　　从这些数据可知，Test1 为70、Test2 为90与Test1 为90、Test2 为70 是互逆的。Test1 为20、Test2 为20与Test1 为20、Test2 为20 是互逆的。希望返回如下结果：

　　

 

 

 　 解决方案：使用自连接找出这样的行，即一行的 Test1 和Test2 与 另一行的 Test2和Test1 相等。然后去重。

SELECT a.test1,a.test2 FROM test.grades a
join test.grades b on a.test1=b.test2 and a.test2 = b.test1
where a.id < b.id

　　这里使用 a.id < b.id 去重。

 

4.平移行值

　　问题：返回每位员工的姓名，薪水，下一个更高和更低的薪水值。如果没有更高或更低的薪水值，就执行回转操作。即如果当前员工的薪水是最低的，则将它的下一个更低的薪水值设置为最高的薪水；如果当前员工的薪水是最高的，就将它的下一个更高的薪水设置为最低的薪水。

　　解决方案：使用窗函数 Lead over 和 Lag over 可以轻松取到前一行和后一行数据。然后使用 min 和 max 获取最小和最大值。

　　第一步：取下一行数据的薪水作为当前行的下一个更高的薪水，取上一行的薪水作为下一个更低薪水

select ename,sal,lead(sal) over(order by sal) 下一个更高的薪水,lag(sal) over(order by sal) 下一个更低薪水 from emps

　　

 

 

 　　第二步：处理第一行和最后一行

select ename,sal,
coalesce( lead(sal) over(order by sal),min(sal) over()) 下一个更高的薪水,
coalesce( lag(sal) over(order by sal),max(sal) over()) 下一个更低薪水 from emps

　　

 

 

 

5.消除重复行

　　问题：找出员工表中不同的部门编号，但不想看到重复的行。

　　解决方案：最简单的方法是使用 distinct 关键字或者使用 group by 。另一种解决方法是，使用窗函数 row_number over 。

select * from (
select DeptNo,row_number() over(partition by  DeptNo order by deptno) as rn  from emps ) a
where rn = 1

　　row_number() over(partition by  DeptNo order by deptno) as rn 根据部门编号分区，给分区内每一行进行编号。然后取分区内第一行。

　　

 

 

 

6.查找马值

　　问题：返回一个结果集，包含员工名字、部门编号、薪水、获聘日期以及所属部门最后聘请的员工的薪水。

　　　　所属部门最后聘请的员工的薪水就是马值，因为查找这些值的方法与国际象棋中马的走法类似。先跳到某一行，然后跳到某一列。同样，要找到所属部门最后聘请的员工的薪水，必须先定位到最后获聘日期所在的行，然后获取该行的薪水值。

　　解决方案：使用  max(HireDate) over(partition by DeptNo)  和 case when 语法找出所属部门最后聘请的员工。再次使用 Max Over 获取所属部门最后聘请的员工的薪水值。

select DeptNo,Ename,Sal,HireDate, max(Lastest_Sal) over(partition by DeptNo) from (
select DeptNo,Ename,Sal,HireDate,
case  max(HireDate) over(partition by DeptNo) 
    when HireDate then SAL else 0 
    end  as Lastest_Sal
    from emps ) a

 

7.生成简单预测

　　问题：根据当前的数据返回表示未来行动的行和列。例如，下面有三行订单数据：

　　

 

 　　每行数据包含下单日期和处理日期（下单两天后）。现在想对每行数据都返回三行（当前行和另外两行），还想返回另外两列，订单核验日期（订单处理一天后）和发货日期（订单核验一天后）。并且想返回一个呈现整个过程的结果集，下图的样子：

　　

 

 　　

　　解决方案：使用笛卡尔积为每行订单再生成两行树，然后使用 Case 表达式来创建所需的列值。

with recursive t3 (n) as
(
    select 1 as n
    union all
    select n+1 from t3 where n< 3
)


select id, OrderDate 下单日期,processDate 处理日期, 
case when n >= 2 then adddate(OrderDate,interval 1 day)
    else '' end as 核验日期,
case when n>2 then adddate(OrderDate,interval 2 day)
    else '' end as 发货日期
 from orders o
join t3