Oracle学习日记——高级查找

1.重新生成房间号

创建如下表：

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

select * from hotel

hotel表

里面的房间号是不对的，正常应该是101，102，201，202的形式，现要求重新生成房间号，我们可以用学到的row_number重新生成房间号。

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;

merged in 后的hotel表

2.跳过表中N行

有时为了取样而不是查看所有的数据，要对数据进行抽样，前面介绍过选取随机行，这里将介绍隔行返回。

为了实现这个目标，用求余函数mod即可。

3.排列组合去重

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的数据组合是重复的（都是1，2，3），要求用查询语句找出这些重复的数据，并只保留一行。我们可以用以下步骤达到需求。

可以观察到test表中数据重复

(1)把t1,t2,t3这三列用列转行合并为一列

select * from test unpivot(b2 for b3 in(t1,t2,t3))

(1)把t1,t2,t3这三列用列转行合并为一列

(2)通过listagg函数对各组字符排序并合并

with x1 as

(select * from test unpivot(b2 for b3 in(t1,t2,t3)))

select id,listagg(b2,',') within group (order by b2) as b from x1 group by id

(2)通过listagg函数对各组字符排序并合并

(3)使用常用的去重语句即可

with x1 as

(select * from test unpivot(b2 for b3 in(t1,t2,t3))),

x2 as

(select id,listagg(b2,',') within group (order by b2) as b from x1 group by id)

select id,b,row_number() over(partition by b order by id) as sn from x2;

如果要去掉重复的组合数据，只需要保留SN = 1即可。

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

drop table test purge;

create table test as select * from dba_objects;

create index idx_test_object_id on test(object_id);

--execute dbms_stats.gather_table_stats(ownname => user,tabname => 'TEST');

要求返回最大、最小的object_id以及对应的object_name,在有分析函数以前可以用下面的查询

select object_name,object_id

from test

where object_id in(select min(object_id) from test union all select max(object_id) from test);

需要对员工表扫描三次，但是用如下的分析函数只需要对员工表扫描一次；

select object_name,object_id

from (select object_name,

object_id,

min(object_id) over() min_id,

max(object_id) over() max_id

from test) x1

where object_id in(min_id,max_id)