partition by关键字是分析性函数的一部分,它和聚合函数不同的地方在于它能返回一个分组中的多条记录,而聚合函数一般只有一条反映统计值的记录,partition by用于给结果集分组,如果没有指定那么它把整个结果集作为一个分组 create database StudentDB
go use StudentDB
go create table Student --学生成绩表
(
id int, --主键
Grade int, --班级
Score int --分数
)
go insert Student
select 1,1,88
union all select 2,1,66
union all select 3,1,75
union all select 4,2,30
union all select 5,2,70
union all select 6,2,80
union all select 7,2,60
union all select 8,3,90
union all select 9,3,70
union all select 10,3,80 go --所有学生信息
select * from Student id Grade Score
----------- ----------- -----------
1 1 88
2 1 66
3 1 75
4 2 30
5 2 70
6 2 80
7 2 60
8 3 90
9 3 70
10 3 80 (10 行受影响) --不分班按学生成绩排名
select *,ROW_NUMBER() over(order by Score desc) as Sequence from Student id Grade Score Sequence
----------- ----------- ----------- --------------------
8 3 90 1
1 1 88 2
6 2 80 3
10 3 80 4
3 1 75 5
9 3 70 6
5 2 70 7
2 1 66 8
7 2 60 9
4 2 30 10 (10 行受影响) --分班后按学生成绩排名
select *,row_number() over(partition by Grade order by Score desc) as Sequence from Student id Grade Score Sequence
----------- ----------- ----------- --------------------
1 1 88 1
3 1 75 2
2 1 66 3
6 2 80 1
5 2 70 2
7 2 60 3
4 2 30 4
8 3 90 1
10 3 80 2
9 3 70 3 (10 行受影响)
补充: msdn-OVER 子句B.将 OVER 子句与聚合函数结合使用下面的示例对于查询返回的所有行将 OVER 子句与聚合函数一起使用。 在这个示例中,使用 OVER 子句与使用子查询相比,可以更高效地派生聚合值。
USE AdventureWorks2012;GOSELECT SalesOrderID, ProductID, OrderQty ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg" ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count" ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min" ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664);GO
下面是结果集: SalesOrderID ProductID OrderQty Total Avg Count Min Max------------ ----------- -------- ----------- ----------- ----------- ------ ------43659 776 1 26 2 12 1 643659 777 3 26 2 12 1 643659 778 1 26 2 12 1 643659 771 1 26 2 12 1 643659 772 1 26 2 12 1 643659 773 2 26 2 12 1 643659 774 1 26 2 12 1 643659 714 3 26 2 12 1 643659 716 1 26 2 12 1 643659 709 6 26 2 12 1 643659 712 2 26 2 12 1 643659 711 4 26 2 12 1 643664 772 1 14 1 8 1 443664 775 4 14 1 8 1 443664 714 1 14 1 8 1 443664 716 1 14 1 8 1 443664 777 2 14 1 8 1 443664 771 3 14 1 8 1 443664 773 1 14 1 8 1 443664 778 1 14 1 8 1 4
以下示例显示在计算所得值中将 OVER 子句与聚合函数结合使用。 USE AdventureWorks2012;GOSELECT SalesOrderID, ProductID, OrderQty ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total ,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID) *100 AS DECIMAL(5,2))AS "Percent by ProductID"FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664);GO
下面是结果集:注意,聚合由 SalesOrderID 计算,并会为每个 SalesOrderID 的每一行计算 Percent by ProductID(ProductID 的百分比)。 SalesOrderID ProductID OrderQty Total Percent by ProductID------------ ----------- -------- ----------- ---------------------------------------43659 776 1 26 3.8543659 777 3 26 11.5443659 778 1 26 3.8543659 771 1 26 3.8543659 772 1 26 3.8543659 773 2 26 7.6943659 774 1 26 3.8543659 714 3 26 11.5443659 716 1 26 3.8543659 709 6 26 23.0843659 712 2 26 7.6943659 711 4 26 15.3843664 772 1 14 7.1443664 775 4 14 28.5743664 714 1 14 7.1443664 716 1 14 7.1443664 777 2 14 14.2943664 771 3 14 21.443664 773 1 14 7.1443664 778 1 14 7.14
|