Shulou(Shulou.com)06/01 Report--

-- row_number

SELECT ename

Deptno

Rownum

Row_number () over (ORDER BY deptno) rn

Rank () over (ORDER BY deptno) rk,-- jump sign

Dense_rank () over (ORDER BY deptno) dense_rk-do not jump sign

FROM emp-- over must follow order by.

-- row_number

SELECT ename

Deptno

Rownum

Row_number () over (partition by deptno ORDER BY deptno) rn

Rank () over (partition by deptno ORDER BY deptno) rk

Dense_rank () over (partition by deptno ORDER BY deptno) dense_rk

FROM emp

-- row_number

SELECT ename

Deptno

Rownum

Row_number () over (partition by deptno ORDER BY sal) rn

Rank () over (partition by deptno ORDER BY sal) rk

Dense_rank () over (partition by deptno ORDER BY sal) dense_rk

FROM emp

-- ask for the maximum wage of each department

SELECT *

FROM (SELECT ename

Deptno

Rownum

Row_number () over (PARTITION BY deptno ORDER BY sal DESC) rn

-- rank () over (partition by deptno ORDER BY sal) rk

-- dense_rank () over (partition by deptno ORDER BY sal) dense_rk

FROM emp)

WHERE rn = 1

-- sum,avg,max,min

SELECT ename

Deptno

Sal

SUM (sal) over (PARTITION BY deptno) AS "Department Summary", SUM (sal) over () AS "all Summary"

FROM emp

-- accumulate

SELECT ename

Deptno

Sal

SUM (sal) over (order BY sal), SUM (sal) over (order BY sal,rowid)

FROM emp

SELECT ename

Deptno

Sal

Avg (sal) over (PARTITION BY deptno) AS "Department Summary", avg (sal) over () AS "all Summary"

FROM emp

SELECT empno

Ename

Sal

FROM emp a

WHERE sal = (SELECT MIN (sal) FROM emp b WHERE a.deptno = b.deptno)

-- rewrite

SELECT *

FROM (

SELECT a.*

Row_number () over (PARTITION BY a.deptno ORDER BY a.sal) rn

FROM emp a)

WHERE rn = 1

SELECT *

FROM emp

WHERE sal in (SELECT MIN (sal) over (PARTITION BY deptno ORDER BY sal) FROM emp a)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.