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
裡面的房間號是不對的,正常應該是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;
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),要求用查詢語句找出這些重複的數據,並只保留一行。我們可以用以下步驟達到需求。
(1)把t1,t2,t3這三列用列轉行合併為一列
select * from test unpivot(b2 for b3 in(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
(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)
閱讀更多 小詹小詹 的文章