《T-SQL排序,重复数据的剔除保留,分页存储过程》由会员分享，可在线阅读，更多相关《T-SQL排序,重复数据的剔除保留,分页存储过程（3页珍藏版）》请在金锄头文库上搜索。
1、T-SQL排序，重复数据的剔除保留，分页存储过程select * from score-剔除重复项select distinct(score) from score-保留重复项(注意： with ties 必须和 top.order by 一起使用)select top 1 with ties score from scoreorder by score desc- newid()select newid() as 新 ID,* from score- 根据成绩字段的降序排列生成行号select row_number() over(order by Score desc) as 行号,stuI
2、D 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;- 按照分数进行排名。( 分数相同的并列名次,下一个名次降一名。)se
3、lect 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 Scoreorder by Score D
4、esc-=- 使用 ntile(页数)分页的存储过程-=- 删除存储过程drop procedure up_Pagego- 创建存储过程create procedure up_PagepageCount int, - 定义每页显示的数据个数currentPage int - 选择当前要显示的数据页as select * from (select ntile(select count(*)/pageCount from Score) over(order by StuID) as Page,* from Score) a where Page=currentPagego-查看结果exec up_Page 2,3- 表示：每页显示 2 条数据，当前显示第 3 页。
