Oracle Advanced Find

1.rownum:rownum is a pseudo-column that needs to be rownum when the data is taken out, so a nested query is required for paging lookups.

SelectSal,ename from (SelectRowNum asRn,sal,ename from       (SelectSal,ename fromEmpwhereSal is  not NULL Order  bysal) xwhereRowNum<Ten)whereRn>6

The use of analytic functions can also be implemented once nested

Select  from       (Selectover,orderby aswhere is  not NULL ) xwherebetween6andten

However, due to the impact of the analysis function, some indexes may be invalid, we recommend that you use the first one.

Return data in interlaced order to find the pseudo-column, MOD (rn,x)

2.Merge: Efficient table update processing

 merge inot test ausing ( select  rowID as  rid,nbr100  +  rownum () over  (partition Span style= "color: #0000ff;" >by  NBR order  by  rowid) as  nnbr  test) b  on  (A.rowid=  b.rid)  when  matched update  set  a.nbr =  B.nnbr; 

Let's guess how many times the test watch scanned?????

3. Combine some of the columns in the table to

Step1: Row career

Select *  from  for inch (T1,T2,T3)

Unpivot a very good way to transpose the ranks

Step2: Sorting by value and merging

 with  x1 as   ( select   *  from  test Unpivot (B2 for  b3 in   select  ID, Listagg (B2, " ,   ) Within group  (order  by  B2)  b  from   X1  group  by  ID; 

After the Listagg is grouped, values are sorted and values are spliced by commas

Step3: Removing duplicates

 withX1 as   (Select *  fromTest Unpivot (B2 forB3inch(T1,T2,T3)), X2 as( SelectId,listagg (B2,',') withinGroup(Order  byB2) asb fromX1Group  byID)SelectId,b,row_number () Over(Partition byBOrder  byId assn fromX2

Oracle Advanced Find