我有一个表: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元,现在我要统计三,四月份退了多少钱,该如何统计呢?请大侠们帮下忙,谢谢!
数据是这样的:
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
select count(a.tuiqian)
from (select use_id ,month,max(ServiceFee)-min(ServiceFee) as tuiqian group by use_id,month)a
where datepart(month,a.month) in (3,4)
(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 #t3select userid,max(ServiceFee)-min(ServiceFee) 退钱额
from #t3
where month(AddTime) in(3,4)
group by userid---------------
userid 退钱额
11 150
12 150
select max(AddTime),max(ServiceFee)-min(ServiceFee)
from ServiceFee
group by UserID
from ServiceFee
where month(AddTime) in(3,4)
group by UserID
insert ServiceFee
select 1,11,200,1,'等待上岗','2006-03-19' union all
select 2, 11, 100 , 1,'等待上岗','2006-03-19' union all
select 3, 11, 50 , 1,'等待上岗','2006-03-19' union all
select 4, 12, 200 , 1,'等待上岗','2006-03-21' union all
select 5, 12 , 50 , 1,'等待上岗','2006-04-15'select max(convert(char(10),AddTime,21)),max(ServiceFee)-min(ServiceFee)
from ServiceFee
where month(AddTime) in(3,4)
group by UserID