本篇文章示例介绍的查询操作不同于其他查询操作,它们体现了不同的查询思路,需要以每次一页的方式显示结果集。
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
今天的文章高级查询_高级资格证书在哪里查询分享到此就结束了,感谢您的阅读,如果确实帮到您,您可以动动手指转发给其他人。
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
如需转载请保留出处:https://bianchenghao.cn/45462.html