Oracle SQL之lag()和lead()函数使用详解数据库
插入一些数据用于测试,如下:
测试数据截图
前期工作准备完成,接下来就准备测试这些函数了!
函数简介
lag()和lead()这两个函数可以查询我们得到的结果集上下偏移相应行数的相应的结果。
形象的说明如下:
lag()函数:
查询当前行向上偏移n行对应的结果
该函数有三个参数:第一个为待查询的参数列名,第二个为向上偏移的位数,第三个参数为超出最上面边界的默认值。
看如下代码:
–查询向上偏移 1 位的年龄
SELECT user_id, user_age, lag(user_age, 1, 0) over(ORDER BY user_id) RESULT FROM user_info;
结果图示:
lag函数运行图示
lead()函数:
查询当前行向下偏移n行对应的结果
该函数有三个参数:第一个为待查询的参数列名,第二个为向下偏移的位数,第三个参数为超出最下面边界的默认值。
如下代码:
–查询向下偏移 2 位的年龄
SELECT user_id, user_age, lead(user_age, 2, 0) over(ORDER BY user_id) FROM user_info;
结果图示:
LEAD()函数图示
在掌握了上面相关的知识之后,我们,可以来试着解决一个 Oracle问题:
例题描述
英文原题: Show the department number, name, number of employees, and
average salary of all departments, together with the names, salaries,
and jobs of the employees working in each department. 题意理解:
查找出员工信息,以及其所在部门的基本信息和该部门的平均工资。
结果格式示意图:
求解思路:
查找出员工信息,以及其所在部门的基本信息和该部门的平均工资。这一步相对来说会比较基础!
但是如何去除相同的部门名称等呢?
我们可以按照部门名称将结果进行分组
然后使用lag()函数取出每个分组的上面一位,如果到了上边界就设置其值为NULL。
然后使用decode()函数使得相应属性对应为NULL的显示原来的值,不为NULL的显示为NULL。
代码如下:
SELECT decode(lag(depttable.department_name, 1, NULL) over(PARTITION BY depttable.department_name ORDER BY emp.department_id ASC), NULL, depttable.department_id, NULL) temp_dep_id, decode(lag(depttable.department_name, 1, NULL) over(PARTITION BY depttable.department_name ORDER BY emp.department_id ASC), NULL, depttable.department_name, NULL) temp_dep_name, decode(lag(depttable.department_name, 1, NULL) over(PARTITION BY depttable.department_name ORDER BY emp.department_id ASC), NULL, depttable.employees_num, NULL) temp_emp_num, decode(lag(depttable.department_name, 1, NULL) over(PARTITION BY depttable.department_name ORDER BY emp.department_id ASC), NULL, depttable.avg_salary, NULL) temp_emp_sa, emp.last_name, emp.salary, emp.job_id FROM (SELECT dep1.department_id, dep1.department_name, COUNT(emp1.employee_id) employees_num, round(nvl(AVG(salary), 0), 2) avg_salary FROM employees emp1, departments dep1 WHERE emp1.department_id(+) = dep1.department_id GROUP BY dep1.department_id, dep1.department_name ORDER BY dep1.department_id) depttable, employees emp WHERE depttable.department_id = emp.department_id ORDER BY emp.department_id ASC, temp_dep_name ASC;
4428.html
oracle相关文章
- 深入浅出:Oracle游标嵌套游标的使用(oracle游标嵌套游标)
- Oracle数据库安全性加固实施方案(oracle安全加固)
- Oracle授权协议:获得最优结果的秘诀(授权oracle)
- Oracle清理表空间:优化数据库性能(oracle清理表空间)
- 使用Oracle数据库接口进行数据库操作(oracle数据库接口)
- Oracle触发器类型:深入挖掘多种类型实现(oracle触发器类型)
- 如何利用Oracle事件跟踪技术优化数据库性能?(oracle事件跟踪)
- Oracle数据库快速创建指南(oracle创建库)
- Oracle数据库服务的收费方式研究(oracle 收费方式)
- Oracle数据库培训班:学习技能,挑战未来!(oracle数据库培训班)
- 详解Oracle查询字符集:如何正确显示数据库中的特殊字符(oracle查询字符集)
- 使用Oracle实现多条数据合并(oracle 多条合并)
- Oracle 全字段重复智能化解决方案(oracle全字段重复)
- 新手必读开启Oracle数据库之旅(oracle入门博客园)
- Oracle数据库先删除后重新增补(oracle先删除后新增)
- 基于Oracle OGI技术的数据库存储优化(ogi技术 oracle)
- 数据库开发深入CX Oracle事务处理(cx oracle 事物)
- Oracle主机优化实现性能突破(oracle主机调优)
- Oracle数据库中给予时间的精彩之处(oracle中赋予时间值)
- 探索Oracle的五种语言之旅(oracle五种语言)
- Oracle数据库修改主键的实践案例(oracle修改主键6)
- 深入理解Oracle中的AS关键字用法(oracle中as用法)
- Oracle数据库表间关联分析(oracle两个表关键字)
- 高可用性Oracle三节点配置技术(oracle三节点)
- Oracle一年报价最优数据库体验之旅(oracle一年报价)
- Oracle SQL中使用别名的优势(oracle sql别名)
- Oracle PDB新建提升数据库性能的神奇力量(oracle pdb新建)