27,582
社区成员




;WITH SaleReport(id,saler,saletype,salemoeny)AS(
select 1,'张三',1,100 union all
select 2,'李四',1,50 union all
select 3,'王五',1,80 union all
select 4,'张三',2,20 union ALL
select 5,'张三',3,20 --添加了类型为3,但不统计,最后结果一样
)
SELECT saler ,
SUM(CASE WHEN saletype = 1 THEN salemoeny
WHEN saletype = 2 THEN -salemoeny
ELSE 0
END) AS salemoeny
FROM SaleReport
GROUP BY saler
结果和上边那个一样,并且没有统计类型为3的。
;WITH SaleReport(id,saler,saletype,salemoeny)AS(
select 1,'张三',1,100 union all
select 2,'李四',1,50 union all
select 3,'王五',1,80 union all
select 4,'张三',2,20
)
SELECT saler ,
SUM(CASE WHEN saletype = 1 THEN salemoeny
ELSE -salemoeny
END) AS salemoeny
FROM SaleReport
GROUP BY saler