if object_id('tempdb..#tb') is not null
   drop table #tb
go
create table #tb
(
 datev datetime,--统计日期
 gameid int,--游戏ID
 coin int,--获得金币
 userid int--会员ID
)
go

insert into #tb
select '2011-12-20',1,100,1 union all
select '2011-12-20',2,100,1 union all
select '2011-12-20',1,-100,2 union all
select '2011-12-20',1,100,3 union all
select '2011-12-20',2,-200,3 union all
select '2011-12-21',2,100,1 union all
select '2011-12-21',1,500,2 union all
select '2011-12-21',2,-200,3

go

--目的是统计出某时段内所有游戏获得金币总和的排行，条件是当日所有游戏获得金币的总和小于0则不参与排行
--这是我目前用的，但很慢，数据只有60W条左右
declare @date1 datetime,@date2 datetime
set @date1='2011-12-1'
set @date2='2011-12-30' 
select top 100 userid,sum(ct) as ctA from (
select userid,sum(coin) as ct,datev from 
#tb with(nolock) where 
userid not in(5,6,7,10,21...这里偶尔会手动添加些不参与排行的会员ID) and datev>=@date1 and datev<=@date2 
group by userid,datev HAVING sum(coin)>0
)  as uct
group by userid order by ctA desc

select datev,userid,sum(coin)ct into #
from #tb 
where userid not in(5,6,7,10,21) and datev>=@date1 and datev<=@date2 
group by datev,userid 
having sum(coin)>0
select top 100 userid,sum(ct) as ctA from # group by userid order by 2 desc

------解决方案--------------------
探讨

SQL code


if object_id('tempdb..#tb') is not null
   drop table #tb
go
create table #tb
(
 datev datetime,--统计日期
 gameid int,--游戏ID
 coin int,--获得金币
 userid int--会员ID
)
go

insert into……