oracle 列转行 去重,Oracle 高级查询

10.1 分页查询

为了便于在网页上查询，常常要分页显示。如对于员工表，要求按工资排序一次只显示5行数据，下次再显示接下来的5行。 我们以第二页数据(6到10 行为例)为了进行分页，需要先生成一个序号，我们前面讲过，要先排序然后在外层才能生成正确的序号。于是语 句如下：

select rn as "序号", ename as "姓名", sal as "工资"

from (select rownum as rn, sal, ename

from (select sal, ename from emp where sal is not null) x

where rownum <= 10)

where rn >= 6;

-----------------------------------------------------------

select rn as "序号", ename as "姓名", sal as "工资"

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;

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;

里面的房间号是不对的，我们可以用学到的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 h

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 (h.rowid = b.rowid)

when matched then

update set h.room_nbr = b.room_nbr;

10.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;

10.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

如上测试表中前三行列t1、t2、t3的数据组合是重复的，要求用查询语句找出这些重复的数据，并只保留一行。 我们可以用以下步骤达到需求

一、返t1、t2、t3这三列用列转行合并为一列。

二、对合并后的数据分组排序

三、把分组排序后

/* 对重新合并后的数据排序并生成序号 */

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);

结果如上所示，如果我们要去掉重复的组合数据，只需要保留sn=1的行即可

select * from

(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)

)

where sn = 1;

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

找出员工表最大值和最小值的记录，在有分析函数之前，一直使用子查询如下：

以上方法需要对员工表emp 扫描三次，性能上就有问题。而用如下的分析函数，只需要对员工表emp 扫描一次即可。

select ename, sal

from (select ename, sal, min(sal) over() min_sal, max(sal) over() max_sal

from emp) x

where sal in (min_sal, max_sal);