SQL Server 2014 按年度、部门分组排名并且统计组内排名人数

最新推荐文章于 2022-12-21 16:23:23 发布

程序员思维

最新推荐文章于 2022-12-21 16:23:23 发布

阅读量1.1k

点赞数

分类专栏： SQL Server

本文链接：https://blog.csdn.net/qq_35434967/article/details/121604233

版权

SQL Server 2014 年度排名部门分组 DENSE_RANK COUNT

关键词由CSDN通过智能技术生成

SQL Server 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

按年度、部门分组排名并且统计组内排名人数

1、数据库（sql server2014）
2、测试数据
3、按年度、部门分组排名并且统计组内排名人数

1、数据库（sql server2014）

CREATE TABLE [dbo].[OA_UserRanking](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeNo] [nvarchar](50) NULL,
	[EmployeeName] [nvarchar](50) NULL,
	[Year] [varchar](10) NULL,
	[DepartmentID] [varchar](50) NULL,
	[TotalScore] [decimal](18, 2) NULL,
	[Ranking] [int] NULL,
 CONSTRAINT [PK_OA_UserRanking] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

2、测试数据

SET IDENTITY_INSERT [dbo].[OA_UserRanking] ON 

GO
INSERT [dbo].[OA_UserRanking] ([ID], [EmployeeNo], [EmployeeName], [Year], [DepartmentID], [TotalScore], [Ranking]) VALUES (1, N'00001', N'张三', N'2021', N'D0101', CAST(60.00 AS Decimal(18, 2)), 0)
GO
INSERT [dbo].[OA_UserRanking] ([ID], [EmployeeNo], [EmployeeName], [Year], [DepartmentID], [TotalScore], [Ranking]) VALUES (2, N'00002', N'李四', N'2021', N'D0101', CAST(80.00 AS Decimal(18, 2)), 0)
GO
INSERT [dbo].[OA_UserRanking] ([ID], [EmployeeNo], [EmployeeName], [Year], [DepartmentID], [TotalScore], [Ranking]) VALUES (3, N'00003', N'徐凯', N'2021', N'D0102', CAST(70.00 AS Decimal(18, 2)), 0)
GO
INSERT [dbo].[OA_UserRanking] ([ID], [EmployeeNo], [EmployeeName], [Year], [DepartmentID], [TotalScore], [Ranking]) VALUES (4, N'00005', N'吴朵', N'2021', N'D0102', CAST(40.00 AS Decimal(18, 2)), 0)
GO
INSERT [dbo].[OA_UserRanking] ([ID], [EmployeeNo], [EmployeeName], [Year], [DepartmentID], [TotalScore], [Ranking]) VALUES (5, N'00006', N'李世伟', N'2021', N'D0102', CAST(60.00 AS Decimal(18, 2)), 0)
GO
SET IDENTITY_INSERT [dbo].[OA_UserRanking] OFF
GO

在这里插入图片描述

3、按年度、部门分组排名并且统计组内排名人数

SELECT  ur.ID ,
        ur.EmployeeNo ,
        ur.EmployeeName ,
        ur.Year ,
        ur.DepartmentID ,
        ur.TotalScore ,
        COUNT(1) OVER ( PARTITION BY ur.Year, ur.DepartmentID ) AS TotalUser ,
        DENSE_RANK() OVER ( PARTITION BY ur.Year, ur.DepartmentID ORDER BY ur.TotalScore DESC ) AS Ranking
FROM    dbo.OA_UserRanking ur;