select a.id,
case when b.money-a.money>=0 then b.money-a.money else 0 end as 增加,
case when b.money-a.money<0 then a.money-b.money else 0 end as 减少
into #temp1from
(select id,money from table1 where month=1) a,
(select id,money from table1 where month=2) b
where a.id=b.idselect sum(增加) 增加数,sum(减少) 减少数 from #temp1
drop table #temp1
case when b.money-a.money>=0 then b.money-a.money else 0 end as 增加,
case when b.money-a.money<0 then a.money-b.money else 0 end as 减少
into #temp1from
(select id,money from table1 where month=1) a,
(select id,money from table1 where month=2) b
where a.id=b.idselect sum(增加) 增加数,sum(减少) 减少数 from #temp1
drop table #temp1
select
sum(case when b.money-a.money>=0 then b.money-a.money else 0 end) as 增加数,
sum(case when b.money-a.money<0 then a.money-b.money else 0 end) as 减少数
from
(select id,money from table1 where month=1) a,
(select id,money from table1 where month=2) b
where a.id=b.id
sum(case when b.money-a.money<0 then a.money-b.money else 0 end) as 减少数
from
(select * from table1 where [month]=1) a,
(select * from table1 where [month]=2) b
where a.id=b.id
其实人员变动的情况必须想考虑,我是这样的,一块是人没变而金额变(就是上面几位老兄),一块是人变动的情况。
//本月新增的
select count(id),sum(money) from yljff
where month= 2 and id not in(select id from yljff where month = 1)
//本月减少
select count(id),sum(money) from yljff
where month= 1 and id not in(select id from yljff where month = 2)
然后再把这些合起来,得到实际情况的。