SQL —— 获取重复某个字段的第一条记录

时间:2024-04-15 19:40:04
----------用来双重排序,且获取唯一
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进行排序所得