本文主要介绍了SQLServer RANK() 排名函数的使用,具体如下:
1
2
3
4
5
6
|
-- 例子表数据 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 ; |
求助问答存储过程使用:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
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 |
partition的意思是对数据进行分区,sql语句如下
1
2
3
4
5
6
7
8
|
SELECT * FROM ( SELECT ROW_NUMBER() over(partition by [姓名] order by [打卡时间] desc ) as rowNum, [姓名], [打卡时间] FROM [dbo].[打卡记录表] ) temp WHERE temp .rowNum = 1 |
通过 partition by [姓名] order by [打卡时间] desc,这句就可以做到,让数据按照姓名分组,并且在每组内部按照时间进行排序
到此这篇关于SQLServer RANK() 排名函数的使用的文章就介绍到这了,更多相关SQLServer RANK()内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家!
原文链接:https://blog.csdn.net/stupid_leilei/article/details/123522074