累计求和开窗函数sum()over()

使用over结合parttion by 对每个员工每月的销售额进行累计

partition by 分组，order by 排序

前后相减，累计求和，多行合并，一行拆多行

--数据
IF OBJECT_ID('TB','u') IS NOT NULL DROP TABLE TB
CREATE TABLE tb(
IDX INT IDENTITY(1,1),
amount int
)
INSERT INTO tb(amount)
VALUEs
(30),(30),(30),(9),(1),(1),(15),(33),(5),(8),(14),(3)

1、前后相减：

SELECT * ,(SELECT a.amount-b.amount from tb b where a.idx=B.idx+1)as cha from TB a
2、累计求和：
SELECT *,(SELECT SUM(AMOUNT) FROM TB B WHERE B.IDX<=A.IDX)AS HE FROM TB A
3、多行合并： ###### select stuff((select ','+cast(amount as varchar) from tb order by amount for xml path('')),1,1,'')
4、一行拆多行（自定义函数）： ``` IF OBJECT_ID('TB','U')IS NOT NULL DROP TABLE TB CREATE TABLE TB( HOBBY VARCHAR(30) ) INSERT INTO TB VALUES ('登山,羽毛球,游泳,瑜伽,翼装飞行') declare @x xml SET @x=(select HOBBY=cast(''+replace(HOBBY,',','')+'' AS xml ) from TB) select HOBBY=T.c.value('.','varchar(20)') from @x.nodes('v') T(c) ``` > 多行合并补充 
IF OBJECT_ID('TB','U')IS NOT NULL DROP TABLE TB
CREATE TABLE TB(
NAME VARCHAR(30),
COURSE VARCHAR(30)
)
INSERT INTO TB
VALUES
('张三','Java'),
('李四','拉丁舞'),
('王五','C语言'),
('赵六','Python'),
('张三','R语言'),
('张三','SQL'),
('王五','Tableau')
select name,(select stuff((select ','+course from tb where name=a.name for xml path('') ),1,1,''))as courses
from tb a 
GROUP BY name;

图1-TB表