TSQL排序,重复数据的剔除保留,分页存储过程.docx

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?行号,???????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?Scoreorder?by?Score?Desc--===================================--?使用ntile(页数)分页的存储过程--===================================--?删除存储过程drop?procedure?up_Pagego--?创建存储过程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=@currentPagego--查看结果exec?up_Page?2,3--?表示：每页显示2条数据，当前显示第3页。