求每个相关ID的第一行数据
假如有一个表:
ID UserID sMoney fillDate
1 AAA 100 2007-1-25 17:00:00
2 BBB 100 2007-1-25 17:01:00
3 AAA 50 2007-1-25 18:00:00
4 CCC 50 2007-1-25 18:22:00
5 BBB 200 2007-1-25 20:00:00
ID是主键自动编号
UserID是用户编号
sMoney是用户充的钱
fillDate是充钱金额
想求出所有用户第一次充钱金额的总合
[解决办法]
create table T(ID int, UserID varchar(10), sMoney int, fillDate datetime)
insert T select 1, 'AAA ', 100, '2007-1-25 17:00:00 '
union all select 2, 'BBB ', 100, '2007-1-25 17:01:00 '
union all select 3, 'AAA ', 50, '2007-1-25 18:00:00 '
union all select 4, 'CCC ', 50, '2007-1-25 18:22:00 '
union all select 5, 'BBB ', 200, '2007-1-25 20:00:00 '
select sMoney=sum(sMoney) from T as tmp
where not exists(select 1 from T where UserID=tmp.UserID and ID <tmp.ID)
--result
sMoney
-----------
250
(1 row(s) affected)
