Oracle Advanced Query

1. Random return records

 

Select * from (select ename, job from EMP order by dbms_random.value () where rownum <= 5 returns five records at random. The system function dbms_random.value () is used as a random number.

 

2. Sort null values

Select * from EMP order by comm DESC nulls last; null values rank first

Select * from EMP order by comm DESC nulls first );

3. Continuous summation

Select ename, Sal, sum (SAL) over (),Sum (SAL) over (order by ename)From EMP;

Add over () as a continuous sum. Over ([partition clause] [sorting clause [Sliding Window clause]). If no parameter is added to over, the effect is the same as that of the previous aggregate function. If there are multiple over entries, the sorting order is based on the last one.

Select deptno, Sal,Sum (SAL) over (partition by deptno order by ename)As S from EMP; Continuous Division sum

Follow a field to perform continuous summation. If the value of this field changes, the continuous summation starts again.

 

4. Obtain the previous or next row of the current data.

Lead (field) to get the next row of the current data

Lag (field) obtains the previous row of the current data

These two functions are also used together with the over function.

Select ename, Sal, lead (SAL) over (order by SAL) Aaa, lag (SAL) over (order by SAL) BBB from EMP;