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

weixin_33725126

于 2017-03-21 17:00:00 发布

阅读量866

点赞数

本文介绍了一种使用SQL进行双重排序并获取唯一记录的方法。通过嵌套子查询实现按分数降序和时间升序排列，并确保每个用户只出现一次最高分记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

----------用来双重排序，且获取唯一
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