[SQL Server]SQL Server2008排序函数应用RowNumber,Rank,Dense_Rank,Ntile

楼主^#

更多发布于：2012-12-08 13:46

[] 1


	SQL Server2008排序函数应用RowNumber,Rank,Dense_Rank,Ntile [sql] --SQL Server2008 排序函数应用 www.atcpu.com /******************************************************************************** 主题：SQL Server2008 排序函数用说明：本文是个人学习的一些笔记和个人愚见 * 有很多地方你可能觉得有异议，欢迎一起讨论 *********************************************************************************/ 这里不做果多的解释，直接上实例。 [sql] --SQL2008 排名函数 RowNumber ,Rank,Dense_Rank ,Ntile create table #t( OrderID int ,CustomerID int ) insert into #t values(43860,1) insert into #t values(44501,1) insert into #t values(45238,1) insert into #t values(46042,1) insert into #t values(46976,2) insert into #t values(47997,2) insert into #t values(49321,3) insert into #t values(32142,1) insert into #t values(43232,2) insert into #t values(53242,1) insert into #t values(13124,4) insert into #t values(12312,3) insert into #t values(53345,2) insert into #t values(34566,2) insert into #t values(32422,4) insert into #t values(53453,2) insert into #t values(63452,3) insert into #t values(13543,3) insert into #t values(53451,4) select , ROW_NUMBER()over(order by CustomerID)ROW_NUMBER, RANK()over(order by CustomerID)RANK, DENSE_RANK()over(order by CustomerID)DENSE_RANK, NTILE(100)over(order by CustomerID)NTILE from #t /* OrderID CustomerID ROW_NUMBER RANK DENSE_RANK NTILE ----------- ----------- -------------------- -------------------- -------------------- -------------------- 43860 1 1 1 1 1 44501 1 2 1 1 2 45238 1 3 1 1 3 46042 1 4 1 1 4 32142 1 5 1 1 5 53242 1 6 1 1 6 43232 2 7 7 2 7 46976 2 8 7 2 8 47997 2 9 7 2 9 53453 2 10 7 2 10 53345 2 11 7 2 11 34566 2 12 7 2 12 12312 3 13 13 3 13 63452 3 14 13 3 14 13543 3 15 13 3 15 49321 3 16 13 3 16 13124 4 17 17 4 17 53451 4 18 17 4 18 32422 4 19 17 4 19 (19 行受影响) */