一、从一个熟悉的示例说起
我们熟知的数据库分页查询,以这一篇介绍过的为例吧。分页查询Person表中的人,可以这么写SQL语句:WITH Record AS (二、窗口函数 其中,ROW_NUMBER()是排名函数,而紧随其后的 OVER()函数就是窗口函数。你还在用二次top方式的分页查询吗?可以考虑尝试使用排名函数配合CTE实现分页。 本文介绍窗口函数,以下面的学生成绩表为例:
SELECT
Row_Number() OVER (ORDER BY Id DESC) AS RecordNumber,
Id,
FirstName,
LastName,
Height,
Weight
FROM
Person (NOLOCK)
)
SELECT
RecordNumber,
(SELECT COUNT(0) FROM Record) AS TotalCount,
Id,
FirstName,
LastName,
Height,
Weight
FROM Record
WHERE RecordNumber BETWEEN 1 AND 10
CREATE TABLE [StudentScore](其中,Id是自增Id,CreateDate是录入时间,StudentId 学生,ClassId 班级,CourseId 课程 ,Score 分数。 录入一些测试数据如下:
[Id] [int] IDENTITY(1,1) NOT NULL,
[StudentId] [int] NOT NULL CONSTRAINT [DF_StudentScore_StudentId] DEFAULT ((0)),
[ClassId] [int] NOT NULL CONSTRAINT [DF_StudentScore_ClassId] DEFAULT ((0)),
[CourseId] [int] NOT NULL CONSTRAINT [DF_StudentScore_CourseId] DEFAULT ((0)),
[Score] [float] NOT NULL CONSTRAINT [DF_StudentScore_Score] DEFAULT ((0)),
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_StudentScore_CreateDate] DEFAULT (getdate())
) ON [PRIMARY]
--CourseId 2:语文 4:数学 8:英语窗口函数是SQL Server2005新增的函数。下面就谈谈它的基本概念: 1、窗口函数的作用 窗口函数是对一组值进行操作,不需要使用GROUP BY 子句对数据进行分组,还能够在同一行中同时返回基础行的列和聚合列。举例来说,我们要得到一个年级所有班级所有学生的平均分,按照传统的写法,我们肯定是通过AVG聚合函数来实现求平均分。这样带来的”坏处“是我们不能轻松地返回基础行的列(班级,学生等列),而只能得到聚合列。因为聚合函数的要点就是对一组值进行聚合,以GROUP BY 查询作为操作的上下文,由于GROUP BY 操作对数据进行分组后,查询为每个组只返回一行数据,因此,要限制所有表达式为每个组只返回一个值。而通过窗口函数,基础列和聚合列的查询都轻而易举。 2、基本语法 OVER([PARTITION BY value_expression,..[n] ] <ORDER BY BY_Clause>)
--1班学生成绩
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,1,2,85)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,1,2,95.5)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,1,2,90)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,1,4,90)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,1,4,98)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,1,4,89)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,1,8,80)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,1,8,75.5)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,1,8,77)
--2班学生成绩
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,2,2,90)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,2,2,77)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,2,2,78)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (4,2,2,83)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,2,4,98)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,2,4,95)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,2,4,78)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (4,2,4,100)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,2,8,85)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,2,8,90)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,2,8,86)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (4,2,8,78.5)
--3班学生成绩
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,3,2,82)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,3,2,78)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,3,2,91)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,3,4,83)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,3,4,78)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,3,4,99)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (1,3,8,86)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (2,3,8,78)
INSERT INTO StudentScore(StudentId,ClassId,CourseId,Score)VALUES (3,3,8,97)
SELECT结果如下:
--Id,
--CreateDate,
StudentId,
ClassId,
CourseId,
Score,
CAST(AVG(Score) OVER() AS decimal(5,2) )AS '语文平均分'
FROM
StudentScore
WHERE CourseId=2
SELECT查询结果如下:
Id,
CreateDate,
StudentId,
ClassId,
CourseId,
Score,
CAST(AVG(Score) OVER(PARTITION BY ClassId ) AS decimal(5,2) )AS '语文平均分'
FROM
StudentScore
WHERE CourseId=2
SELECT
Id,
-- CreateDate,
ROW_NUMBER() OVER(ORDER BY Score DESC) AS '序号',
StudentId,
ClassId,
CourseId,
Score
FROM
StudentScore
WHERE CourseId=8
结果如下:
SELECT结果如下:
Id,
-- CreateDate,
RANK() OVER(ORDER BY Score DESC) AS '序号',
StudentId,
ClassId,
CourseId,
Score
FROM
StudentScore
WHERE CourseId=8
SELECT查询结果如下:
Id,
-- CreateDate,
DENSE_RANK() OVER(ORDER BY Score DESC) AS '序号',
StudentId,
ClassId,
CourseId,
Score
FROM
StudentScore
WHERE CourseId=8
SELECT查询的结果如下:
Id,
-- CreateDate,
NTILE(6) OVER(ORDER BY ClassId DESC) AS '组编号',
StudentId,
ClassId,
CourseId,
Score
FROM
StudentScore
WHERE CourseId=8
热门源码