T-SQL 常用排名函数
提纲:
-- distinct 剔除重复项
-- with ties 保留重复项
-- newid() 新ID
-- row_number() 行号
-- rank() 排名(降一名次)
-- dense_rank() 排名(不降一名次)
-- ntile(页数) 分页
-- 使用ntile(页数) 分页的存储过程
-- with ties 保留重复项
-- newid() 新ID
-- row_number() 行号
-- rank() 排名(降一名次)
-- dense_rank() 排名(不降一名次)
-- ntile(页数) 分页
-- 使用ntile(页数) 分页的存储过程
T-SQL 代码如下:
use
S100801A
go
select * from score
-- 剔除重复项
select distinct (score) from score
-- 保留重复项(注意:with ties 必须和 top...order by 一起使用)
select top 1 with ties score from score
order by score desc
-- newid()
select newid () as ' 新ID ' , * from score
-- 根据‘成绩’字段的降序排列生成‘行号’
select row_number() over ( order by Score desc ) as ' 行号 ' ,
stuID as ' 学号 ' ,Score as ' 成绩 ' from Score
-- 根据临时表 temp_Score 的‘行号’rowNum,获得‘行号’在 1-20之间的记录。
with temp_Score as
(
select row_number() over ( order by Score desc ) as rowNum,
stuID,Score from Score
)
select rowNum as ' 行号 ' ,stuID as ' 学号 ' ,Score as ' 成绩 '
from temp_Score where rowNum between 1 and 20 ;
-- 按照分数进行排名。(分数相同的并列名次,下一个名次降一名。)
select StuID,Score,
rank() over ( order by Score desc ) as ' 名次 '
from Score
-- 按照分数进行排名。(分数相同的并列名次,下一个名次不降一名。)
select StuID,Score,
dense_rank() over ( order by Score desc ) as ' 名次 '
from Score
-- ntile(页数):用来将整个表进行分页(或分组),
-- 并指定每条记录属于哪一页。
select stuID,Score,
ntile( 3 ) over ( order by Score desc ) as ' 页码 '
from Score
order by Score Desc
-- ===================================
-- 使用ntile(页数)分页的存储过程
-- ===================================
-- 删除存储过程
drop procedure up_Page
go
-- 创建存储过程
create procedure up_Page
@pageCount int , -- 定义每页显示的数据个数
@currentPage int -- 选择当前要显示的数据页
as
select * from (
select ntile(( select count ( * ) / @pageCount from Score))
over ( order by StuID) as Page, * from Score
) a where Page = @currentPage
go
-- 查看结果
exec up_Page 2 , 3
-- 表示:每页显示2条数据,当前显示第3页。
go
select * from score
-- 剔除重复项
select distinct (score) from score
-- 保留重复项(注意:with ties 必须和 top...order by 一起使用)
select top 1 with ties score from score
order by score desc
-- newid()
select newid () as ' 新ID ' , * from score
-- 根据‘成绩’字段的降序排列生成‘行号’
select row_number() over ( order by Score desc ) as ' 行号 ' ,
stuID as ' 学号 ' ,Score as ' 成绩 ' from Score
-- 根据临时表 temp_Score 的‘行号’rowNum,获得‘行号’在 1-20之间的记录。
with temp_Score as
(
select row_number() over ( order by Score desc ) as rowNum,
stuID,Score from Score
)
select rowNum as ' 行号 ' ,stuID as ' 学号 ' ,Score as ' 成绩 '
from temp_Score where rowNum between 1 and 20 ;
-- 按照分数进行排名。(分数相同的并列名次,下一个名次降一名。)
select StuID,Score,
rank() over ( order by Score desc ) as ' 名次 '
from Score
-- 按照分数进行排名。(分数相同的并列名次,下一个名次不降一名。)
select StuID,Score,
dense_rank() over ( order by Score desc ) as ' 名次 '
from Score
-- ntile(页数):用来将整个表进行分页(或分组),
-- 并指定每条记录属于哪一页。
select stuID,Score,
ntile( 3 ) over ( order by Score desc ) as ' 页码 '
from Score
order by Score Desc
-- ===================================
-- 使用ntile(页数)分页的存储过程
-- ===================================
-- 删除存储过程
drop procedure up_Page
go
-- 创建存储过程
create procedure up_Page
@pageCount int , -- 定义每页显示的数据个数
@currentPage int -- 选择当前要显示的数据页
as
select * from (
select ntile(( select count ( * ) / @pageCount from Score))
over ( order by StuID) as Page, * from Score
) a where Page = @currentPage
go
-- 查看结果
exec up_Page 2 , 3
-- 表示:每页显示2条数据,当前显示第3页。
参考来源:排名函数 (Transact-SQL)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/tsqlref9/html/e7f917ba-bf4a-4fe0-b342-a91bcf88a71b.htm