日期:2014-05-19  浏览次数:20498 次

怎么样聚合后取出特定的列?
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