sqlserver分页及分析函数sum over rank over count over

目录

基础数据

排序及分析函数

 ROW_NUMBER按照年龄简单排序,并显示序号

 ROW_NUMBER按照班级分组,并按照年龄,性别排序显示出对应的序号

 ROW_NUMBER分页每页5条

 offset分页每页5条

 分页时同时查询出总数量count over

 分页时查询出总数量,并查出每班人数count over

 分页时查询出总数量,并查出每班年龄和sum over

 分页时查询出总数量,并查出每班年龄平均值avg over

 所有学生按照年龄排序,年龄相同排名一样,下个阶段排名累计 rank over

 所有学生按照年龄排序,年龄相同排名一样,下个阶段排名不累计 dense_rank over

---

• 基础数据

USE [TestDb]
GO
/****** Object:  Table [dbo].[Student]    Script Date: 2022/8/30 10:55:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Sex] [int] NULL,
	[Age] [int] NULL,
	[ClassNumber] [nvarchar](50) NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Student] ON 

INSERT [dbo].[Student] ([Id], [Name], [Sex], [Age], [ClassNumber]) VALUES (1, N'小明', 1, 3, N'小一班')
INSERT [dbo].[Student] ([Id], [Name], [Sex], [Age], [ClassNumber]) VALUES (2, N'小红', 0, 4, N'中一班')
INSERT [dbo].[Student] ([Id], [Name], [Sex], [Age], [ClassNumber]) VALUES (3, N'小丽', 0, 4, N'中一班')
INSERT [dbo].[Student] ([Id], [Name], [Sex], [Age], [ClassNumber]) VALUES (4, N'小雅', 0, 5, N'大一班')
INSERT [dbo].[Student] ([Id], [Name], [Sex], [Age], [ClassNumber]) VALUES (5, N'小阳', 1, 3, N'小一班')
INSERT [dbo].[Student] ([Id], [Name], [Sex], [Age], [ClassNumber]) VALUES (6, N'小新', 1, 4, N'小二班')
INSERT [dbo].[Student] ([Id], [Name], [Sex], [Age], [ClassNumber]) VALUES (7, N'小肥', 1, 5, N'小三班')
INSERT [dbo].[Student] ([Id], [Name], [Sex], [Age], [ClassNumber]) VALUES (8, N'小鹏', 1, 3, N'小一班')
SET IDENTITY_INSERT [dbo].[Student] OFF
GO

数据整体效果为

•   排序及分析函数

•  ROW_NUMBER按照年龄简单排序,并显示序号

select    ROW_NUMBER() over(order by Age ) as Number,Age,Name,Sex,ClassNumber from Student

   

•  ROW_NUMBER按照班级分组,并按照年龄,性别排序显示出对应的序号

select * 
from (select	
	  ROW_NUMBER() over(partition by ClassNumber order by Age,Sex) as Number,Age,Name,Sex,ClassNumber 
	  from Student) t

         

•  ROW_NUMBER分页每页5条

declare @pagesize int
set @pagesize=5
declare @pageindex int
set @pageindex = 1
select * from (select  ROW_NUMBER() over(order by Age ) as Number,Age,Name,Sex,ClassNumber  from Student) t 
where Number between (@Pageindex - 1) * @PageSize + 1 and @Pageindex * @PageSize

• offset分页每页5条

declare @pagesize int
set @pagesize=5
declare @pageindex int
set @pageindex = 1
select Age,Name,Sex,ClassNumber from Student
order by Age
offset (@pageindex-1)*@pagesize row
fetch next @pagesize row only

 

• 分页时同时查询出总数量count over

declare @pagesize int
set @pagesize=5
declare @pageindex int
set @pageindex = 1
select Age,Name,Sex,ClassNumber,COUNT(1) over() as Total from Student
order by Age
offset (@pageindex-1)*@pagesize row
fetch next @pagesize row only

 

•  分页时查询出总数量,并查出每班人数count over

declare @pagesize int
set @pagesize=5
declare @pageindex int
set @pageindex = 1
select Age,Name,Sex,ClassNumber,(COUNT(1) over()) as Total,(COUNT(1) over(partition by ClassNumber)) as ClassTotal from Student
order by Age
offset (@pageindex-1)*@pagesize row
fetch next @pagesize row only

•  分页时查询出总数量,并查出每班年龄和sum over

declare @pagesize int
set @pagesize=5
declare @pageindex int
set @pageindex = 1
select Age,Name,Sex,ClassNumber,(COUNT(1) over()) as Total,(sum(Age) over(partition by ClassNumber)) as ClassSum from Student
order by Age
offset (@pageindex-1)*@pagesize row
fetch next @pagesize row only

•   分页时查询出总数量,并查出每班年龄平均值avg over

declare @pagesize int
set @pagesize=5
declare @pageindex int
set @pageindex = 1
select Age,Name,Sex,ClassNumber,(COUNT(1) over()) as Total,(avg(Age) over(partition by ClassNumber)) as ClassAvg from Student
order by Age
offset (@pageindex-1)*@pagesize row
fetch next @pagesize row only

•  所有学生按照年龄排序,年龄相同排名一样,下个阶段排名累计 rank over

 select RANK() over(order by Age) as AgeIndex,Age,Name,Sex,ClassNumber from Student

•  所有学生按照年龄排序,年龄相同排名一样,下个阶段排名不累计 dense_rank over

select dense_rank() over(order by Age) as AgeIndex,Age,Name,Sex,ClassNumber from Student