https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql
Determines the partitioning and ordering of a rowset before the associated window function is applied.
That is, the OVER clause defines a window or user-specified set of rows within a query result set.
A window function then computes a value for each row in the window.
You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative累积的 aggregates, running totals, or a top N per group results.
分组
PARTITION BY
Divides the query result set into partitions.
The window function is applied to each partition separately and computation restarts for each partition.
根据什么进行分组
alue_expression
Specifies the column by which the rowset is partitioned.
value_expression can only refer to columns made available by the FROM clause.
value_expression cannot refer to expressions or aliases in the select list.
value_expression can be a column expression, scalar subquery, scalar function, or user-defined variable.
<ORDER BY clause>
Defines the logical order of the rows within each partition of the
result set.
That is, it specifies the logical order in which the window functioncalculation is performed.
order_by_expression
Specifies a column or expression on which to sort.
order_by_expression can only refer to columns made available by the FROM clause.
An integer cannot be specified to represent a column name or alias.
sql server 2012以上的版本才支持
WITH temp2 AS ( SELECT Id , [number] , ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 )) AS SNO FROM dbo.TestPartition ) SELECT temp2.Id , temp2.number , SUM(number) OVER ( PARTITION BY temp2.Id ORDER BY temp2.SNO ) AS 'number 累计值' FROM temp2
sql server 2008以及以下的版本,使用下面的
/* SQL Server 2005 以及 2008 做法 1.先分区,并编号*/ ; WITH cte AS ( SELECT Id , number , ROW_NUMBER() OVER ( PARTITION BY Id ORDER BY Id ) AS rnm FROM dbo.TestPartition ) --调试语句 --SELECT * FROM cte /* 2.再累加。用到了嵌套子查询 */ SELECT Id , number , ( SELECT SUM(number) FROM cte t1 WHERE t1.rnm <= t2.rnm AND -- t1.Id = t2.Id --保证是同一个人的数据在做累加 ) AS 'number 累计值' FROM cte t2;