if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[Fee] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [dbo].[Fee]
GO
CREATE TABLE [dbo].[Fee] (
[FeeID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NULL ,
[FeeActionID] [int] NULL ,
[Amount] [money] NULL ,
[Balance] [money] NULL ,
[Remarks] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[AddTime] [datetime] NULL ,
[PostUser] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
delete from Fee
insert into Fee values(222,2,30,30, ' ',getdate(), 'test ')
insert into Fee values(222,2,20,50, ' ',getdate(), 'test ')
insert into Fee values(221,2,30,30, ' ',getdate(), 'test ')
insert into Fee values(221,2,10,20, ' ',getdate(), 'test ')
insert into Fee values(220,2,50,50, ' ',getdate(), 'test ')
insert into Fee values(220,2,20,30, ' ',getdate(), 'test ')
insert into Fee values(220,2,30,0, ' ',getdate(), 'test ')
我想取出所有最近的余额不为0的用户的ID，就是不知道怎么写SQL语句，请大家帮帮我哈.其中字段[Balance]为余额数
------解决方案--------------------
CREATE TABLE [dbo].[Fee] (
[FeeID] [int] IDENTITY (1, 1) NOT NULL ,
[UserID] [int] NULL ,
[FeeActionID] [int] NULL ,
[Amount] [money] NULL ,
[Balance] [money] NULL ,
[Remarks] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[AddTime] [datetime] NULL ,
[PostUser] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
delete from Fee
insert into Fee values(222,2,30,30, ' ',dateadd(Day,-2,getdate()), 'test ')
insert into Fee values(222,2,20,50, ' ',dateadd(Day,-1,getdate()), 'test ')
insert into Fee values(221,2,30,30, ' ',dateadd(Day,-2,getdate()), 'test ')
insert into Fee values(221,2,10,20, ' ',dateadd(Day,-1,getdate()), 'test ')
insert into Fee values(220,2,50,50, ' ',dateadd(Day,-3,getdate()), 'test ')
insert into Fee values(220,2,20,30, ' ',dateadd(Day,-2,getdate()), 'test ')
insert into Fee values(220,2,30,0, ' ',dateadd(Day,-1,getdate()), 'test ')
select * from fee as a,(
select userid,max([AddTime]) as b_AddTime from Fee group by userid) as b
where a.AddTime=b.b_AddTime and balance > 0 and a.userid = b.userid
----------------------------------
FeeID UserID FeeActionID Amount Balance Remarks AddTime PostUser userid b_AddTime
----------- ----------- ----------- --------------------- --------------------- -------------------------------------------------------------------------------------------- ----------------------- -------------------- ----------- -----------------------
9 222 2 20.00 50.00 2007-06-07 12:33:11.453 test 222 2007-06-07 12:33:11.453