----------用来双重排序,且获取唯一 SELECT * FROM Mstching025Play WHERE ActivitySceneID=1 go SELECT ROW_NUMBER() OVER (ORDER BY AScore DESC,ATime ASC) AS Rank, * FROM ( SELECT AScore,ATime,MerchantWeixinUserID, ROW_NUMBER() OVER (partition by MerchantWeixinUserID order by ASCORE DESC,ATime ASC) AS SX FROM ( SELECT MerchantWeixinUserID,TodayTimes,SUM(UsedSeconds) AS ATime,SUM(Score) AS AScore,CONVERT(varchar(100),CreateTime, 23) AS DT FROM Mstching025Play WHERE ActivitySceneID=1 GROUP BY CONVERT(varchar(100),CreateTime, 23),MerchantWeixinUserID,TodayTimes )a GROUP BY MerchantWeixinUserID,ATIME,ASCORE )b WHERE SX=1 --AND ASCORE=8
用来测试的数据列表:
筛选后的结果:ASCORE以及ATIME进行排序所得