sql 查询学生成绩排名 并列第几 要标记是并列第几名 排名函数 DENSE_RANK

实际要求 查询学生 英语成绩前五名 并列的要指出

表的情况

学生表:

成绩表

实现SQL : 没用排名函数之前

with ranks as(
 
			select  score,   '第'+ cast(  row_number() over( order by score desc ) as  nvarchar) ++ '名' as  rank from (
 
				select distinct top 5  score    from tt left join t on t.xh = tt. xh  
				where t.xh is not null 
				and t.kc= '英语'   order by  score desc
 
			) base
 
)
select  tt.xh,tt.xm,tt.sex,tt.age,t.kc,t.score,ranks.rank   ,
 
case  (select count(1)  from  t mt where   mt.score = t.score and mt. kc = t.kc )
when 1 then ''
else '并列'
end  type

from tt 
left join t on t.xh = tt. xh  
left join ranks on t.score = ranks.score
where t.xh is not null 
and t.kc= '英语'  and  ranks.score is not null  order by rank

效果:

我这边逻辑还是蛮简单的 :

• 第一步 查询零时表得到前五名 英语成绩 因为用 distinct 排重过不会有重复
• 第二步 用整个班的英语成绩来和前五名的进行 left jion 因为是 left join 还得加上 右边 score 不能为空
• 第三步 用 case 语句判断一下有没有英语成绩相同的 为1 就代表这个分数只有一个人,为其他就代表有并列的

 

后面了解到 还有排名函数   而且 用法 oracle 和 sql server mysql 都有这几个函数 而且用法一致

row_number()

 row_number() 是获取行号经常在分页中看见,我之前一直以为在 oracle 中没有,常在分页时用到

select  row_number() over (order by  CLS_ID desc  ) ranks , c. * from VDW_MD_CLASS c    

rank()  一般用不到,太特殊

就和 rank 的翻译一样就是排名 但是这个排名有点特殊 特殊如下:两个并列第三名后面是第五名 不是 第四名

dense_rank()  

这个函数就是我们期望的  即使有并列的数据 后续的 排名也没有直接跳过

NTILE(3) 

这个方法是 将 整个结果进行分组的 函数  参数是必填的 代表将整个结果按照某个值 正序 或者 倒序分成 几份

例如 按照英语成绩 将这些人分成  三组  优 、良、 一般   如下 1就是优秀组 、2的就是良好组 、3是一般组

上边的成绩排序问题 现在处理起来就简单了

• 第一步用densc_rank 算出排名临时表
• 第二步用case 标出并列的

with base as (

	select  tt.xh,tt.xm,tt.sex,tt.age,t.kc,t.score, 
	 dense_rank() over(order by t.score desc)   as ranks 
	from tt 
	left join t on t.xh = tt. xh  
	where t.xh is not null 
	and t.kc= '英语'   
)
select base.*,
'第'+ cast( ranks as  nvarchar) ++ '名'   as ranksName ,
case  (select count(1)  from  base  tb where   tb.ranks = base.ranks )
when 1 then ''
else '并列'
end  type

from base where ranks <=5

 

 

 

以下是测试数据的 sql:

USE [testpro]
GO
/****** Object:  Table [dbo].[T]    Script Date: 2020/9/27 15:47:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T](
	[xh] [nvarchar](6) NOT NULL,
	[kc] [nvarchar](4) NOT NULL,
	[score] [int] NOT NULL,
 CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED 
(
	[xh] ASC,
	[kc] 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
/****** Object:  Table [dbo].[TT]    Script Date: 2020/9/27 15:47:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TT](
	[xh] [nvarchar](6) NOT NULL,
	[xm] [nvarchar](6) NOT NULL,
	[sex] [nvarchar](2) NOT NULL,
	[age] [int] NOT NULL,
 CONSTRAINT [PK_TT] PRIMARY KEY CLUSTERED 
(
	[xh] 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
INSERT [dbo].[T] ([xh], [kc], [score]) VALUES (N'K02502', N'神经', 98)
GO
INSERT [dbo].[T] ([xh], [kc], [score]) VALUES (N'S08901', N'国学', 135)
GO
INSERT [dbo].[T] ([xh], [kc], [score]) VALUES (N'S08901', N'英语', 120)
GO
INSERT [dbo].[T] ([xh], [kc], [score]) VALUES (N'S08902', N'国学', 99)
GO
INSERT [dbo].[T] ([xh], [kc], [score]) VALUES (N'S08902', N'英语', 135)
GO
INSERT [dbo].[T] ([xh], [kc], [score]) VALUES (N'S08903', N'英语', 120)
GO
INSERT [dbo].[T] ([xh], [kc], [score]) VALUES (N'S08904', N'英语', 121)
GO
INSERT [dbo].[T] ([xh], [kc], [score]) VALUES (N'S08905', N'英语', 119)
GO
INSERT [dbo].[T] ([xh], [kc], [score]) VALUES (N'S08906', N'英语', 118)
GO
INSERT [dbo].[TT] ([xh], [xm], [sex], [age]) VALUES (N'S08901', N'Tom', N'男', 18)
GO
INSERT [dbo].[TT] ([xh], [xm], [sex], [age]) VALUES (N'S08902', N'Jack', N'男', 16)
GO
INSERT [dbo].[TT] ([xh], [xm], [sex], [age]) VALUES (N'S08903', N'Ham', N'女', 19)
GO
INSERT [dbo].[TT] ([xh], [xm], [sex], [age]) VALUES (N'S08904', N'qal', N'男', 20)
GO
INSERT [dbo].[TT] ([xh], [xm], [sex], [age]) VALUES (N'S08905', N'rxh', N'女', 18)
GO
INSERT [dbo].[TT] ([xh], [xm], [sex], [age]) VALUES (N'S08906', N'yhq', N'男', 20)
GO