Over 的使用

测试数据如下

ID，成绩，科目(1英语，2数学，3语文)，科目名称，学生名称，添加时间

问题：如果想查询每个学科的前5名学生怎么写sql?

--按照学科，成绩倒序查前5条，查三次

select top 5 * from [dbo].[Achievement] where [subject]='1' order by Fraction desc

select top 5 * from [dbo].[Achievement] where [subject]='2' order by Fraction desc

select top 5 * from [dbo].[Achievement] where [subject]='3' order by Fraction desc

--使用Over取每个学科成绩的前5名

--关键字row_number() over(partition by 分区字段 order by 排序字段 desc)

select * from

(

select [name],[subject],subjectName,Fraction,

--按照学科分区，成绩倒序加一列编号

row_number() over(partition by [subject] order by Fraction desc) mm

from [dbo].[Achievement]

) a

where mm<6

 

--取所有成绩的前5名，不过滤重复数据

select * from

(

select [name],[subject],subjectName,Fraction,

--成绩倒序加一列编号

row_number() over( order by Fraction desc) mm

from [dbo].[Achievement]

) a

where mm<6--取前5名

 

--成绩逐行累计

select * from

(

select [name],[subject],subjectName,Fraction,

--成绩倒序，成绩累计

SUM(Fraction) over( order by Fraction desc) mm

from [dbo].[Achievement]

) a

 

 

--表结构

CREATE TABLE [dbo].[Achievement](

[Id] [int] IDENTITY(1,1) NOT NULL, --ID

[Fraction] [decimal](18, 0) NULL, --成绩

[Subject] [nvarchar](100) NULL, --科目(1英语，2数学，3语文)

[SubjectName] [nvarchar](100) NULL, --科目名称

[Name] [nvarchar](100) NULL, --学生名称

[AddTime] [datetime] NULL, --添加时间

CONSTRAINT [PK_Achievement] 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]

GO

 

--数据，3门学科，11位学生，每位学生3课成绩，共33条数据

begin

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (1, CAST(45 AS Decimal(18, 0)), N'1', N'英语', N'小红', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (2, CAST(67 AS Decimal(18, 0)), N'1', N'英语', N'小蓝', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (3, CAST(23 AS Decimal(18, 0)), N'1', N'英语', N'小白', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (4, CAST(89 AS Decimal(18, 0)), N'1', N'英语', N'小字', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (5, CAST(67 AS Decimal(18, 0)), N'1', N'英语', N'小嗯嗯', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (6, CAST(23 AS Decimal(18, 0)), N'1', N'英语', N'小规范', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (7, CAST(32 AS Decimal(18, 0)), N'1', N'英语', N'小而二哥', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (8, CAST(56 AS Decimal(18, 0)), N'1', N'英语', N'小啥第三方', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (9, CAST(88 AS Decimal(18, 0)), N'1', N'英语', N'小为', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (10, CAST(78 AS Decimal(18, 0)), N'1', N'英语', N'小男女', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (11, CAST(80 AS Decimal(18, 0)), N'1', N'英语', N'小爽肤水', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (12, CAST(42 AS Decimal(18, 0)), N'2', N'数学', N'小红', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (13, CAST(6 AS Decimal(18, 0)), N'2', N'数学', N'小蓝', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (14, CAST(98 AS Decimal(18, 0)), N'2', N'数学', N'小白', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (15, CAST(56 AS Decimal(18, 0)), N'2', N'数学', N'小字', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (16, CAST(34 AS Decimal(18, 0)), N'2', N'数学', N'小嗯嗯', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (17, CAST(23 AS Decimal(18, 0)), N'2', N'数学', N'小规范', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (18, CAST(67 AS Decimal(18, 0)), N'2', N'数学', N'小而二哥', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (19, CAST(12 AS Decimal(18, 0)), N'2', N'数学', N'小啥第三方', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (20, CAST(33 AS Decimal(18, 0)), N'2', N'数学', N'小为', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (21, CAST(90 AS Decimal(18, 0)), N'2', N'数学', N'小男女', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (22, CAST(95 AS Decimal(18, 0)), N'2', N'数学', N'小爽肤水', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (23, CAST(78 AS Decimal(18, 0)), N'3', N'语文', N'小红', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (24, CAST(55 AS Decimal(18, 0)), N'3', N'语文', N'小蓝', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (25, CAST(45 AS Decimal(18, 0)), N'3', N'语文', N'小白', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (26, CAST(23 AS Decimal(18, 0)), N'3', N'语文', N'小字', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (27, CAST(90 AS Decimal(18, 0)), N'3', N'语文', N'小嗯嗯', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (28, CAST(23 AS Decimal(18, 0)), N'3', N'语文', N'小规范', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (29, CAST(45 AS Decimal(18, 0)), N'3', N'语文', N'小而二哥', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (30, CAST(47 AS Decimal(18, 0)), N'3', N'语文', N'小啥第三方', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (31, CAST(12 AS Decimal(18, 0)), N'3', N'语文', N'小为', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (32, CAST(84 AS Decimal(18, 0)), N'3', N'语文', N'小男女', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

INSERT [dbo].[Achievement] ([Id], [Fraction], [Subject], [SubjectName], [Name], [AddTime]) VALUES (33, CAST(97 AS Decimal(18, 0)), N'3', N'语文', N'小爽肤水', CAST(N'2011-01-02T00:00:00.000' AS DateTime))

end