Oracle 技能强化 Part 10 高级查找

1.给结果集分页。

网页查询常常是以分页的形式进行显示的，为了进行分页，需要先生成了一个序号，要先排序然后在外层才能生成正确的序号。

SELECT rn as sequence_number, ename as names, sal as salarys
/*根据前面生成的序号过滤掉6行以前的数据*/
  FROM (SELECT rownum as rn, sal, ename
        /*取得排序后的序号，并过滤掉10行以后的数据*/
          from (
               /*首先要按sal排序*/
          select sal, ename from emp where sal is not NULL order by sal) x
         WHERE rownum <= 10)
 WHERE rn >= 6;

 也可以使用row_number ()分析函数生成序号，再过滤。这样就只需要嵌套一次。

SELECT rn as sequence_number, ename as names, sal as salarys
  FROM (SELECT row_number() over(order by sal) as rn, sal, ename
          FROM emp
         WHERE sal IS NOT NULL) x
 WHERE rn BETWEEN 6 AND 10;

2.重新生成房间号

CREATE TABLE hotel(floor_nbr,room_nbr) as 
SELECT 1,100 FROM dual UNION ALL
SELECT 1,100 FROM dual UNION ALL
SELECT 2,100 FROM dual UNION ALL
SELECT 2,100 FROM dual UNION ALL
SELECT 3,100 FROM dual;

/*窗函数在此不能使用*/
UPDATE hotel
  SET room_nbr = 
      (floor_nbr * 100) + row_number() over(PARTITION BY floor_nbr);

/*可以使用merge语句*/

MERGE INTO hotel a
USING (SELECT ROWID AS rid,
              (floor_nbr * 100) + row_number() over(PARTITION BY floor_nbr ORDER BY ROWID) AS room_nbr
         from hotel) b
on (a.rowid = b.rowid)
when matched then
  update set a.room_nbr = b.room_nbr;
                
select * from hotel;

 3.跳过表中n行

有时候为了取样而不是查看所有数据，要求对数据进行抽样，我们前面讲过选取随机行。用取余函数mod 即可。

/*对于返回的数据增加过滤条件即可*/

SELECT ename,MOD(rn,2) as m
  FROM (SELECT row_number() over(ORDER BY ename) rn,ename FROM emp)x
 WHERE MOD(rn,2) = 1;

4.排列组合去重。

一个数组组合去重的问题。数据环境模拟如下：

DROP TABLE TEST PURGE;
CREATE TABLE TEST (id,t1,t2,t3) AS
SELECT 1, '1', '3', '2' FROM dual UNION ALL
SELECT 2, '1', '3', '2' FROM dual UNION ALL
SELECT 3, '3', '2', '1' FROM dual UNION ALL
SELECT 4, '4', '2', '1' FROM dual;
 
/*对重新合并后的数据排序并生成序号*/
SELECT id, b, row_number() over(PARTITION BY b ORDER BY id) AS sn
FROM (
/*排序并合并*/
SELECT id, listagg(b2, ',') within GROUP(ORDER BY b2) AS b
FROM (SELECT *
FROM test
/*行转列*/ unpivot(b2 FOR b3 IN(t1, t2, t3)))
GROUP BY id);

 5.找到包含最大值和最小值的记录。

找出员工表中工资最大与最小的记录，在有分析函数以前，我们一直用子查询如下：

select ename, sal
  from emp
 where sal in (select min(sal) from emp union all select max(sal) from emp);

需要对员工表扫描三次，而用如下分析函数，只需要对员工表扫描一次即可。

SELECT ename
  from (select ename, sal, min(sal) over() min_sal, max(sal) over() max_sal
          from emp) x
 WHERE sal in (min_sal, max_sal);