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.,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.,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, OP ) 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
(
rank函数怎么排名(
[xh] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OP ) 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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。