--SQL Server2008 排序函数应用 /******************************************************************************** *主题：SQL Server2008 排序函数用 *说明：本文是个人学习的一些笔记和个人愚见 * 有很多地方你可能觉得有异议，欢迎一起讨论 *作者：Stephenzhou(阿蒙) *日期: 2012.12.3 *Mail:[email protected] *另外：转载请著名出处。 **********************************************************************************/
这里不做果多的解释，直接上实例。
--SQL2008 排名函数 RowNumber ,Rank,Dense_Rank ,Ntilecreate 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 144501 1 2 1 1 245238 1 3 1 1 346042 1 4 1 1 432142 1 5 1 1 553242 1 6 1 1 643232 2 7 7 2 746976 2 8 7 2 847997 2 9 7 2 953453 2 10 7 2 1053345 2 11 7 2 1134566 2 12 7 2 1212312 3 13 13 3 1363452 3 14 13 3 1413543 3 15 13 3 1549321 3 16 13 3 1613124 4 17 17 4 1753451 4 18 17 4 1832422 4 19 17 4 19(19 行受影响) */
*作者：Stephenzhou(阿蒙)
*日期: 2012.12.3
*Mail:[email protected]
*另外：转载请著名出处。
*博客地址：http://blog.csdn.net/szstephenzhou