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   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