select userid,sum(winscore+lostscore) as resultscore, winscorefrom dbo.StreamScoreInfo where FirstCollectDate between '2013-4-07 00:00:13' and '2013-4-07 23:55:32 'group by userid,winscoreorder by resultscore desc
在sql中如果使用到sum,那么group by中必须包含select中其它的所有项
select m.* from (SELECT top 1000 ROW_NUMBER() over (order by winscore desc) as NewNo, a.userid ,b.accounts ,a.winscore as winscore,a.FirstCollectDate as startdate,a.LastCollectDate as enddate FROM [XJPTreasureDB].[dbo].[StreamScoreInfo] a , [XJPAccountsDB].[dbo].[AccountsInfo] b where a.userid=b.userid and a.FirstCollectDate>='" + Start_time + "' AND a.LastCollectDate<='" + End_time + "' order by a.winscore desc) m where NewNo>" + (CPage - 1) * PageSize + " and NewNo<="+ CPage * PageSize ;
如果用到ROW_NUMBER() 而后又作为条件,需要把它放入到一个()中然后select再使用
类似的例子:http://stackoverflow.com/questions/9617616/how-to-combine-group-by-and-row-number