前言
今天一个简单项目需要使用排行榜,做的挺多的,记录一下
需求:可以查询出每天的排行榜
表结构如下
用户表
create table usr_users(
user_id int primary key identity(1,1),
nick_name nvarchar(20),
head_img_url nvarchar(20)
)
游戏记录表
create table usr_game_rec(
game_rec_id int primary key identity(1,1),
[user_id] int foreign key references usr_users([user_id]) not null,
paly_time datetime default(getdate()),
end_time datetime ,
dates nvarchar(20),
score int default(0)
)
sql根据传入的日期查询每天排行榜
select a.user_id,a.score,b.nick_name,b.head_img_url from
(select top 5 user_id,max(score) as score from usr_game_rec where dates='2021-12-21' group by user_id order by score desc) a
left join usr_users b on a.user_id=b.user_id
查询结果:
注意:有 order by 的子查询必须加top,不然会报错
2023-11-02更新
感觉上面的还差点意思,这个可以应对更复杂的场景,每个用户有n条数据,根据得分和花费时间排序,进行用户去重分日期>_<
select top 30 a.game_rec_id ,a.point,a.spend_time spendTime,b.nick_name nickName,b.head_img_url headImgUrl from
(select *,ROW_NUMBER() over(partition by user_id,game_date order by point desc,spend_time) rn from sg_game_rec) a
left join usr_users b on a.user_id=b.user_id
where a.game_date='{0}' and rn=1
order by point desc,a.spend_time