SQL Server 2005/2008 排名函数 ROW_NUMBER ，RANK，DENSE_RANK 比较

SQL Server 2005/2008 排名函数ROW_NUMBER ，RANK，DENSE_RANK 比较

--ROW_NUMBER() --返回结果集分区内行的序列号，每个分区的第一行从1 开始。返回类型bigint

--RANK() --返回结果集的分区内每行的排名。行的排名是相关行之前的排名数加一。RANK 函数并不总返回连续整数。返回类型bigint

--DENSE_RANK() --返回结果集分区中行的排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。返回类型bigint

--举个例子比较看一下就清楚了

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'TB') AND type = N'U')

DROP TABLE TB

GO

CREATE TABLE TB (col int, [type] varchar(10))

INSERT INTO TB (col,[type])

SELECT 1,'A' UNION ALL

SELECT 2,'A'  UNION ALL

SELECT 2,'A'  UNION ALL

SELECT 3,'A'  UNION ALL

SELECT 4,'A'  UNION ALL

SELECT 4,'A'  UNION ALL

SELECT 1,'B'  UNION ALL

SELECT 2,'B'  UNION ALL

SELECT 3,'B'  UNION ALL

SELECT 3,'B'  UNION ALL

SELECT 5,'B' 

 

SELECT * FROM TB

SELECT ROW_NUMBER() over(order by col) id,* FROM TB

/*

id  col type

1   1   A

2   1   B

3   2   B

4   2   A

5   2   A

6   3   A

7   3   B

8   3   B

9   4   A

10  4   A

11  5   B*/

SELECT RANK() over(order by col) id,* FROM TB

/*

1   1   A

1   1   B

3   2   B

3   2   A

3   2   A

6   3   A

6   3   B

6   3   B

9   4   A

9   4   A

11  5   B

*/

SELECT DENSE_RANK() over(order by col) id,* FROM TB

/*

id  col type

1   1   A

1   1   B

2   2   B

2   2   A

2   2   A

3   3   A

3   3   B

3   3   B

4   4   A

4   4   A

5   5   B

*/

--加partition by 每个分区内重新从开始排名

SELECT ROW_NUMBER() over(partition by [type] order by col) id,* FROM TB

/*

id  col type

1   1   A

2   2   A

3   2   A

4   3   A

5   4   A

6   4   A

1   1   B

2   2   B

3   3   B

4   3   B

5   5   B

*/

SELECT RANK() over(partition by [type] order by col) id,* FROM TB

/*

1   1   A

2   2   A

2   2   A

4   3   A

5   4   A

5   4   A

1   1   B

2   2   B

3   3   B

3   3   B

5   5   B*/

SELECT DENSE_RANK() over(partition by [type] order by col) id,* FROM TB

/*

1   1   A

2   2   A

2   2   A

3   3   A

4   4   A

4   4   A

1   1   B

2   2   B

3   3   B

3   3   B

4   5   B

*/