数据库技术:SQLServer RANK() 排名函数的使用

-- 例子表数据  select * from test;  -- 统计分数  select name,sum(achievement) achievement from test group by name;   -- 按统计分数做排行  select rank() over( order by sum(achievement) desc) 排行,name,sum(achievement) achievement from test group by name;

use [db]  go     /****** object:  storedprocedure [dbo].[sp_todayjoinuser]    script date: 2021/1/26 14:45:24 ******/  set ansi_nulls on  go     set quoted_identifier on  go        -- =============================================  -- author:		_hey_jude  -- create date: 2021-01-26  -- description:	获取今日发表帮助/回复的新用户  -- =============================================  create procedure [dbo].[sp_todayjoinuser]   	@tablelevel int,      @date varchar(30)  as  declare @sql nvarchar(max)   declare @mintabid int  declare @maxtabid int  declare @maxf_id int  declare @helptablename nvarchar(max)  declare @tablecount int     begin  	 --最小f_id所在表  	 set @mintabid=0  	 set @tablecount=@mintabid  	 --最大f_id所在表  	 set @maxf_id=(select max(f_id) from [table] where f_isdelete=0)  	 set @maxtabid=@maxf_id/@tablelevel     	 set @helptablename='select userid, max([f_datetime]) as dt from [table] group by userid'     	 while @tablecount<=@maxtabid  	 begin		  		print @tablecount  		set @helptablename += ' union select userid, max([datetime]) as dt from subtable'+cast(@tablecount as nvarchar(10))+' group by userid '  		set @tablecount=@tablecount+1  	 end   	                 set @sql='select [nikename] from (   					select userid, rank() over(partition by userid order by dt) as num,dt from ( '+@helptablename+' ) as t ) as newt  			   left join [usertable] a with(nolock) on newt.userid = a.userid where num = 1 and dt > '''+@date+''''     	 exec sp_executesql @sql  end     go

select* from (      select          row_number() over(partition by [姓名] order by [打卡时间] desc) as rownum,          [姓名],          [打卡时间]      from [dbo].[打卡记录表]      ) temp  where temp.rownum = 1