MSSQL OVER

over函数的用法：

1.用于局部求和，比如，求某人在某个时间段内的订单额综合

select 
  orderid,orderdate,

--orderdate数据格式为2013-01，结果为根据年月份求出总额度

  sum(orderprice) over(partition by orderdate)
 from orders

2.用于生成自动增长列

select 
 row_number() over(order by orderid) as rownumber,
 orderid
from orders

3.用于局部生成自动增长列

SELECT 

--根据custid将数据分组，然后生成自动增长列

	ROW_NUMBER() over(partition by custid order by val asc) as num,
orderid, custid, val,
  SUM(val) OVER() AS totalvalue,
  SUM(val) OVER(PARTITION BY custid) AS custtotalvalue
FROM Sales.OrderValues;

执行结果：

1	10702	1	330.00	1265793.22	4273.00
2	10952	1	471.20	1265793.22	4273.00
3	10643	1	814.50	1265793.22	4273.00
4	10835	1	845.80	1265793.22	4273.00
5	10692	1	878.00	1265793.22	4273.00
6	11011	1	933.50	1265793.22	4273.00
1	10308	2	88.80	1265793.22	1402.95
2	10759	2	320.00	1265793.22	1402.95
3	10625	2	479.75	1265793.22	1402.95
4	10926	2	514.40	1265793.22	1402.95
1	10682	3	375.50	1265793.22	7023.98
2	10365	3	403.20	1265793.22	7023.98
3	10856	3	660.00	1265793.22	7023.98
4	10507	3	749.06	1265793.22	7023.98
5	10677	3	813.37	1265793.22	7023.98