try: 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
userid not in(5,6,7,10,21...这里偶尔会手动添加些不参与排行的会员ID)这userid NOT IN的你写死了还是别人输入的??可否另建立一个表来保存的?
“在1201-1231多少数据量”这个没懂什么意思 总共目前是保持在60W条左右 然后每天总共会新增8W左右,然后每天凌晨SQL作业会删除8W左右userid not in 是我在服务器上写死了的
datev
有没有建立索引
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
“在1201-1231多少数据量”这个没懂什么意思 总共目前是保持在60W条左右 然后每天总共会新增8W左右,然后每天凌晨SQL作业会删除8W左右userid not in 是我在服务器上写死了的
(datev,coin)建立复合索引,可能有效(因为不知道你具体的东西)你试试临时表还是要建立的,分两次查询你USERID 不在统计的可以建立个表保存起来啊