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)