本文主要介绍了SQLServerRANK()排名函数的使用,具体如下:
--例子表数据
SELECT*FROMtest;
--统计分数
SELECTname,SUM(achievement)achievementFROMtestGROUPBYname;
--按统计分数做排行
SELECTRANK()OVER(ORDERBYSUM(achievement)desc)排行,name,SUM(achievement)achievementFROMtestGROUPBYname;
求助问答存储过程使用:
USE[DB]
GO
/******Object:StoredProcedure[dbo].[sp_TodayJoinUser]ScriptDate:2021/1/2614:45:24******/
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
--=============================================
--Author:_Hey_Jude
--Createdate:2021-01-26
--Description:获取今日发表帮助/回复的新用户
--=============================================
CREATEPROCEDURE[dbo].[sp_TodayJoinUser]
@tableLevelint,
@datevarchar(30)
AS
Declare@Sqlnvarchar(max)
declare@minTabIdint
declare@maxTabIdint
declare@maxf_idint
declare@helpTableNamenvarchar(max)
declare@tableCountint
BEGIN
--最小f_id所在表
set@minTabId=0
set@tableCount=@minTabId
--最大f_id所在表
set@maxf_id=(selectMAX(F_ID)from[Table]whereF_IsDelete=0)
set@maxTabId=@maxf_id/@tablelevel
set@helpTableName='SELECTUserID,Max([F_DateTime])ASdtFROM[Table]GROUPBYUserID'
while@tableCount<=@maxTabId
begin
print@tableCount
set@helpTableName+='UNIONSELECTUserID,Max([DateTime])asdtFROMSubTable'+cast(@tableCountasnvarchar(10))+'GROUPBYUserID'
set@tableCount=@tableCount+1
end
set@Sql='SELECT[nikename]FROM(
SELECTUserID,RANK()OVER(PARTITIONBYUserIDORDERBYdt)ASNum,dtFROM('+@helpTableName+')AST)ASNewT
LEFTJOIN[UserTable]AWITH(NOLOCK)ONNewT.UserID=A.UserIdWHERENum=1ANDdt>'''+@date+''''
Execsp_executesql@Sql
END
GO
partition的意思是对数据进行分区,sql语句如下
SELECT*FROM(
SELECT
ROW_NUMBER()over(partitionby[姓名]orderby[打卡时间]desc)asrowNum,
[姓名],
[打卡时间]
FROM[dbo].[打卡记录表]
)temp
WHEREtemp.rowNum=1
通过partitionby[姓名]orderby[打卡时间]desc,这句就可以做到,让数据按照姓名分组,并且在每组内部按照时间进行排序
|