SQL怎么样统计已经退款的数字?
我有一个表:ServiceFee.
数据是这样的:
ID UserID ServiceFee FindJobNum UserState AddTime
1 11 200 1 等待上岗 2006-03-19
2 11 100 1 ...... 2006-03-19
3 11 50 1 ..... 2006-03-19
4 12 200 1 ......... 2006-03-21
5 12 50 1 ...... 2006-04-15
数据里面是这样的:在3月19日这天,UserID为11的用户先是交了200元服务费,接着退了100元,剩下了100元,后来又退了50元,剩下50元。在ServiceFee字段里都是剩下的钱数。而用户12,在3.21交了200元,在四月份退了150,剩下50元,现在我要统计三,四月份退了多少钱,该如何统计呢?请大侠们帮下忙,谢谢!
------解决方案-------------------- ---计算每人月
select count(a.tuiqian)
from (select use_id ,month,max(ServiceFee)-min(ServiceFee) as tuiqian group by use_id,month)a
------解决方案--------------------create table #t3
(ID int,
UserID int,
ServiceFee int,
AddTime datetime
)
insert into #t3
select '1 ', '11 ', '200 ', '2006-03-19 ' union all select '2 ', '11 ', '100 ', '2006-03-19 ' union all select '3 ', '11 ', '50 ', '2006-03-19 ' union all select '4 ', '12 ', '200 ', '2006-03-21 ' union all select '5 ', '12 ', '50 ', '2006-04-15 '
select * from #t3
select userid,max(ServiceFee)-min(ServiceFee) 退钱额
from #t3
where month(AddTime) in(3,4)
group by userid
---------------
userid 退钱额
11 150
12 150