我在写程序的时候,遇到一个求总余额的问题,无法解决,请各位高手赐教。表是这样的
表名Fee
表字段:
FeeID UserID FeeAction Amount Balance(余额)
1 1 首交 100 100
2 1 扣除 50 50
3 1 返还 10 60
4 2 首交 100 100
5 3 首交 200 200
6 2 扣除 10 90
每个UserID 的最后一条记录是最新的。现在要求统计所有的userid的余额值,请问SQL该怎么写呢?
表名Fee
表字段:
FeeID UserID FeeAction Amount Balance(余额)
1 1 首交 100 100
2 1 扣除 50 50
3 1 返还 10 60
4 2 首交 100 100
5 3 首交 200 200
6 2 扣除 10 90
每个UserID 的最后一条记录是最新的。现在要求统计所有的userid的余额值,请问SQL该怎么写呢?
(select UserId,max(FreeID) as FreeID from Fee group by UserId) b
where a.UserId=b.UserId and a.FreeID=b.FreeID
where not exists (
select 1 from fee
where UserID=a.UserID
and feeid>a.feeid
)加总
select sum(Balance) as Balance from fee a
where not exists (
select 1 from fee
where UserID=a.UserID
and feeid>a.feeid
)
create table Fee(FeeID int,UserID int, FeeAction varchar(20), Amount int, Balance int)
insert into Fee
select 1, 1, '首交', 100, 100
union all
select 2, 1, '扣除', 50, 50
union all
select 3, 1, '返还', 10, 60
union all
select 4, 2, '首交', 100, 100
union all
select 5, 3, '首交', 200, 200
union all
select 6, 2, '扣除', 10, 90
*/
select sum(Balance) as total from Fee a,
(select UserId,max(FeeID) as FeeID from Fee group by UserId) b
where a.UserId=b.UserId and a.FeeID=b.FeeID/*
total
----------------
350
*/
insert into fee
select 1,1,'首交',100,100 union all
select 2,1,'扣除',50, 50 union all
select 3,1,'返还',10, 60 union all
select 4,2,'首交',100,100 union all
select 5,3,'首交',200,200 union all
select 6,2,'扣除',10,90 select sum(balance) as 总余额 from
(select * from fee a where not exists(select 1 from fee where feeid>a.feeidand userid=a.userid))bdrop table fee