[MSSQL]SQL SERVER 2005-OVER (PARTITION BY…)的用法

吴贺华

　　按字段进行排序
比如：
编号 InfoId, SeriesId,
11 a class_1
22 b class_1
33 c class_1
44 d class_2
55 e class_2
66 f class_2
... ... ...
-----------------------------
查询出来结果是这样
11 a class_1 1
22 b class_1 2
33 c class_1 3
44 d class_2 1
55 e class_2 2
66 f class_2 3
---------------------------------------------------------------
Select InfoId, SeriesId,
ROW_NUMBER() OVER(PARTITION BY SeriesId ORDER BY InfoId) AS ord
FROM [uc_QuotePrice] WITH (NOLOCK)
ORDER BY SeriesId,ord
---------------------------------------------------
　　OVER (PARTITION BY…)的用法
OVER (PARTITION BY…) 按照某列分割/区分
　　SELECT EMP.*,SUM(SAL) OVER(PARTITION BY DEPTNO) 本部门工资总和 FROM EMP
　　deptno ename sal 本部门工资总和
　　1 10 CLARK 2450.00 8750
　　2 10 KING 5000.00 8750
　　3 10 MILLER 1300.00 8750
　　4 20 SMITH 800.00 10875
　　5 20 ADAMS 1100.00 10875
　　6 20 FORD 3000.00 10875
　　7 20 SCOTT 3000.00 10875
　　8 20 JONES 2975.00 10875
　　9 30 ALLEN 1600.00 9400
　　10 30 BLAKE 2850.00 9400
　　11 30 MARTIN 1250.00 9400
　　12 30 JAMES 950.00 9400
　　13 30 TURNER 1500.00 9400
　　14 30 WARD 1250.00 9400
　　将DEPTNO列列值相同的对应的sal加和
　　
　　如果不需要已某个列的值分割,那就要用null,因为没有分割列,所以“工资总和”的值为所有sal列的值的和
　　SELECT EMP.*,SUM(SAL) OVER(PARTITION BY DEPTNO) 工资总和 FROM EMP
　　1 10 CLARK 2450.00 29025
　　2 10 KING 5000.00 29025
　　3 10 MILLER 1300.00 29025
　　4 20 SMITH 800.00 29025
　　5 20 ADAMS 1100.00 29025
　　6 20 FORD 3000.00 29025
　　7 20 SCOTT 3000.00 29025
　　8 20 JONES 2975.00 29025
　　9 30 ALLEN 1600.00 29025
　　10 30 BLAKE 2850.00 29025
　　11 30 MARTIN 1250.00 29025
　　12 30 JAMES 950.00 29025
　　13 30 TURNER 1500.00 29025
　　14 30 WARD 1250.00 29025
　　
　　使用 sum(sal) over (order by ename)... 查询员工的薪水"连续"求和
　　SELECT EMP.*,SUM(SAL) OVER(ORDER BY ENAME) 按姓名排序后连续求和,100*ROUND(SAL/SUM(SAL) OVER (),4) "份额(%)" FROM EMP
　　1 20 ADAMS 1100.00 1100 3.79
　　2 30 ALLEN 1600.00 2700 5.51
　　3 30 BLAKE 2850.00 5550 9.82
　　4 10 CLARK 2450.00 8000 8.44
　　5 20 FORD 3000.00 11000 10.34
　　6 30 JAMES 950.00 11950 3.27
　　7 20 JONES 2975.00 14925 10.25
　　8 10 KING 5000.00 19925 17.23
　　9 30 MARTIN 1250.00 21175 4.31
　　10 10 MILLER 1300.00 22475 4.48
　　11 20 SCOTT 3000.00 25475 10.34
　　12 20 SMITH 800.00 26275 2.76
　　13 30 TURNER 1500.00 27775 5.17
　　14 30 WARD 1250.00 29025 4.31
　　
　　SELECT EMP.*,SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY ENAME) 按照部门连续求总和 FROM EMP
　　1 10 CLARK 2450.00 2450
　　2 10 KING 5000.00 7450
　　3 10 MILLER 1300.00 8750
　　4 20 ADAMS 1100.00 1100
　　5 20 FORD 3000.00 4100
　　6 20 JONES 2975.00 7075
　　7 20 SCOTT 3000.00 10075
　　8 20 SMITH 800.00 10875
　　9 30 ALLEN 1600.00 1600
　　10 30 BLAKE 2850.00 4450
　　11 30 JAMES 950.00 5400
　　12 30 MARTIN 1250.00 6650
　　13 30 TURNER 1500.00 8150
　　14 30 WARD 1250.00 9400
　　
SELECT EMP.*,SUM(SAL) OVER (ORDER BY DEPTNO,ENAME) 所有部门连续求总和 FROM EMP
　　1 10 CLARK 2450.00 2450
　　2 10 KING 5000.00 7450
　　3 10 MILLER 1300.00 8750
　　4 20 ADAMS 1100.00 9850
　　5 20 FORD 3000.00 12850
　　6 20 JONES 2975.00 15825
　　7 20 SCOTT 3000.00 18825
　　8 20 SMITH 800.00 19625
　　9 30 ALLEN 1600.00 21225
　　10 30 BLAKE 2850.00 24075
　　11 30 JAMES 950.00 25025
　　12 30 MARTIN 1250.00 26275
　　13 30 TURNER 1500.00 27775
　　14 30 WARD 1250.00 29025
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。
　　
　　部门从大到小排列，部门里各员工的薪水从高到低排列
　　SELECT DEPTNO,ENAME,SAL,SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY DEPTNO DESC, SAL DESC) DEPT_SUM,
　　SUM(SAL) OVER(ORDER BY DEPTNO DESC, SAL DESC) SUM FROM EMP;
　　1 30 BLAKE 2850.00 2850 2850
　　2 30 ALLEN 1600.00 4450 4450
　　3 30 TURNER 1500.00 5950 5950
　　4 30 MARTIN 1250.00 8450 8450
　　5 30 WARD 1250.00 8450 8450
　　6 30 JAMES 950.00 9400 9400
　　7 20 FORD 3000.00 6000 15400
　　8 20 SCOTT 3000.00 6000 15400
　　9 20 JONES 2975.00 8975 18375
　　10 20 ADAMS 1100.00 10075 19475
　　11 20 SMITH 800.00 10875 20275
　　12 10 KING 5000.00 5000 25275
　　13 10 CLARK 2450.00 7450 27725
　　14 10 MILLER 1300.00 8750 29025
铁观音-厂家直销