SQL 排列函数

一，Row_Number() 函数
 Row_Number函数以作为参数传递给这个函数的Order By 子句为基础，返回一个不停递增的整数值。如果Row_Number的Order By和结果集中的顺序相匹配，那么返回值将是递增的而且是以升序排列的。如果Row_Number的Order By和结果集中的顺序不同，这些值将不会按顺序列出，但Row_Number函数的Order By子句的顺序。
 Select ID,Name,Row_Number() over (order by Name) As RowNum From Category Order by Name (图1)
 Select ID,Name,Row_Number() over (order by Name) As RowNum From Category Order by ID(图2)
 图1，                                    

ID	Name	RowNum
3	A	1
1	B	2
2	C	3

图2

ID	Name	RowNum
1	B	2
2	C	3
3	A	1

二，Rank()和Dense_Rank()
Select ID,Name,Rank() Over (Order by Name) As Rank From Category Order by Name

ID	Name	Rank
1	A	1
2	A	1
3	B	3
4	B	3
5	C	5

Select ID,Name,Dense_Rank() Over (Order by Name) As Rank From Category Order by Name

ID	Name	Rank
1	A	1
2	A	1
3	B	2
4	B	2
5	C	3