T-SQL 常用排名函数

 

提纲：

-- distinct              剔除重复项
-- 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 1with ties scorefrom score
order by score desc

-- newid()
select newid() as'新ID',*from score

-- 根据‘成绩’字段的降序排列生成‘行号’
select row_number()over(orderby Score desc) as'行号',
       stuID as'学号',Scoreas '成绩'from Score

-- 根据临时表 temp_Score 的‘行号’rowNum，获得‘行号’在 1-20之间的记录。
with temp_Scoreas
(
   select row_number()over(orderby Score desc) as rowNum,
          stuID,Score from Score
)
select rowNumas '行号',stuIDas '学号',Scoreas '成绩'
from temp_Scorewhere rowNum between 1and 20;

-- 按照分数进行排名。(分数相同的并列名次,下一个名次降一名。)
select StuID,Score,
  rank() over(orderby Score desc) as'名次'
from Score

-- 按照分数进行排名。(分数相同的并列名次,下一个名次不降一名。)
select StuID,Score,
  dense_rank() over(orderby Score desc) as'名次'
from Score

-- ntile(页数)：用来将整个表进行分页(或分组),
            -- 并指定每条记录属于哪一页。
select stuID,Score,
ntile(3)over(orderby Score desc) as'页码'
from Score
order by Score Desc

--===================================
-- 使用ntile(页数)分页的存储过程
--===================================

-- 删除存储过程
drop procedure up_Page
go

-- 创建存储过程
create procedure up_Page
    @pageCountint,         -- 定义每页显示的数据个数
    @currentPageint        -- 选择当前要显示的数据页
as  
    select * from (
      select ntile((selectcount(*)/@pageCountfrom Score)) 
             over(orderby 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