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

Original

Select *

From (select province

Commodity

Sum (price)

ROW_NUMBER () OVER (PARTITION BY province order by sum (price) desc) rn

From test141211

Group by province, commodity

-- order by province desc, sum (price) desc

)

Where rn select

Empno,sal,mgr,deptno

Sum (sal) over (partition by deptno order by sal

RANGE BETWEEN 0 PRECEDING AND 100 FOLLOWING) dd

From emp

Function: sort by DEPARTMENT_ID partition, sort by SALARY ascending order, and summarize the sum of SALARY from the current SALARY to 100 greater than the current SALARY.

Return the result:

EMPNO SAL MGR DEPTNO DD

--

7934 1300 7782 10 1300

7782 2450 7839 10 2450

7839 5000 10 5000

7369 800 7902 20 800

7566 2975 7839 20 5975 3000 is between 2975 and (2975 million 100), so find the sum of 2975 and 3000

7902 3000 7566 20 3000

7900 950 7698 30 950

7521 1250 7698 30 2500

7654 1250 7698 30 2500

7844 1500 7698 30 3100

7499 1600 7698 30 1600

7698 2850 7839 30 2850

12 rows have been selected.

Explanation: returns the record in which the leading row is equal to the current row SALARY, and the subsequent row is 100 larger than it, summing on the SALARY column.

No restrictions on the upper and lower boundaries: OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

SQL > select empno,sal,mgr,deptno

Sum (sal) over (partition by deptno order by sal

RANGE BETWEEN unbounded PRECEDING AND unbounded FOLLOWING) dd

From emp

EMPNO SAL MGR DEPTNO DD

--

7934 1300 7782 10 8750

7782 2450 7839 10 8750

7839 5000 10 8750

7369 800 7902 20 6775

7566 2975 7839 20 6775

7902 3000 7566 20 6775

7900 950 7698 30 9400

7521 1250 7698 30 9400

7654 1250 7698 30 9400

7844 1500 7698 30 9400

7499 1600 7698 30 9400

7698 2850 7839 30 9400

12 rows have been selected.

5. Over (order by salary rows between 1 preceding and 2 following)-the corresponding data window for each row is that the amplitude value of the previous row does not exceed 1, and that of the subsequent row does not exceed 2.

SQL > select empno,sal,mgr,deptno

Sum (sal) over (partition by deptno order by sal

Rows BETWEEN 1 PRECEDING AND 2 FOLLOWING) dd

From emp

Return the result

EMPNO SAL MGR DEPTNO DD

--

7934 1300 7782 10 8750

7782 2450 7839 10 8750

7839 5000 10 7450

7369 800 7902 20 6775

7566 2975 7839 20 6775

7902 3000 7566 20 5975

7900 950 7698 30 3450

7521 1250 7698 30 4950

7654 1250 7698 30 5600

7844 1500 7698 30 7200

7499 1600 7698 30 5950

7698 2850 7839 30 4450

12 rows have been selected.

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.