Oracle 高级查询

10.1 分页查询为了便于在网页上查询常常要分页显示。如对于员工表要求按工资排序一次只显示5行数据下次再显示接下来的5行。 我们以第二页数据6到10 行为例为了进行分页需要先生成一个序号我们前面讲过要先排序然后在外层才能生成正确的序号。于是语 句如下select rn as 序号, ename as 姓名, sal as 工资from (select rownum as rn, sal, enamefrom (select sal, ename from emp where sal is not null) xwhere rownum 10)where rn 6;-----------------------------------------------------------select rn as 序号, ename as 姓名, sal as 工资from (select row_number() over(order by sal) as rn, sal, enamefrom empwhere sal is not null) xwhere rn between 6 and 10;10.2 重新生成房间号现有房间号数据如下CREATE TABLE hotel (floor_nbr, room_nbr) ASSELECT 1, 100FROM dualUNION ALLSELECT 1, 100FROM dualUNION ALLSELECT 2, 100FROM dualUNION ALLSELECT 2, 100FROM dualUNION ALLSELECT 3, 100FROM dual;里面的房间号是不对的我们可以用学到的row_number重新生成房间号。或许马上会有读者想到update语句。让我们来执行一下。UPDATE hotel SET room_nbr (floor_nbr * 100) row_number() over(PARTITION BY floor_nbr);ORA-30483: window 函数在此禁用 Update不能用我们还是用merge语句吧MERGE INTO hotel hUSING (SELECT ROWID AS RID,(floor_nbr * 100) row_number() over(partition by floor_nbr order by rowid) as room_nbrfrom hotel) bon (h.rowid b.rowid)when matched thenupdate set h.room_nbr b.room_nbr;10.3 跳过表中的n 行有时为了取样而不是查看所有数据要对数据进行抽样我们前面讲过选取随机行。下面讲隔行返回。为了实现这个目标用求余函数mod 即可。为了实现隔行取值对于上图中返回的数据增加过滤条件即可。select ename, mod(rn, 2) as mfrom (select row_number() over(order by ename) rn, ename from emp) xwhere mod(rn, 2) 1;10.4 排列组合去重有网友提出一个数据组合去重的问题。数据环境模拟如下DROP TABLE TEST PURGE;CREATE TABLE TEST (id,t1,t2,t3) ASSELECT 1, 1, 3, 2 FROM dualUNION ALLSELECT 2, 1, 3, 2 FROM dualUNION ALLSELECT 3, 3, 2, 1 FROM dualUNION ALLSELECT 4, 4, 2, 1 FROM dual如上测试表中前三行列t1、t2、t3的数据组合是重复的要求用查询语句找出这些重复的数据并只保留一行。 我们可以用以下步骤达到需求一、返t1、t2、t3这三列用列转行合并为一列。二、对合并后的数据分组排序三、把分组排序后/* 对重新合并后的数据排序并生成序号 */SELECT id, b, row_number() over(PARTITION BY b ORDER BY id) AS snFROM ( /* 排序并合并 */SELECT id, listagg(b2, ,) within GROUP(ORDER BY b2) AS bFROM (SELECT *FROM test /* 行转列 */ unpivot(b2 FOR b3 IN(t1, t2, t3)))GROUP BY id);结果如上所示如果我们要去掉重复的组合数据只需要保留sn1的行即可select * from(SELECT id, b, row_number() over(PARTITION BY b ORDER BY id) AS snFROM ( /* 排序并合并 */SELECT id, listagg(b2, ,) within GROUP(ORDER BY b2) AS bFROM (SELECT *FROM test /* 行转列 */ unpivot(b2 FOR b3 IN(t1, t2, t3)))GROUP BY id))where sn 1;10.5 找到包含最大值和最小值的记录。找出员工表最大值和最小值的记录在有分析函数之前一直使用子查询如下以上方法需要对员工表emp 扫描三次性能上就有问题。而用如下的分析函数只需要对员工表emp 扫描一次即可。select ename, salfrom (select ename, sal, min(sal) over() min_sal, max(sal) over() max_salfrom emp) xwhere sal in (min_sal, max_sal);