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

累计求和开窗函数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('<v>'+replace(HOBBY,',','</v><v>')+'</v>' 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表