RANK()，DENSE_RANK()，ROW_NUMBER()排序

现在有个需求，多个客户有同一个共享信息，相同的共享信息ID设置为同一个序号，这样便于做分页查询，

1、使用函数DENSE_RANK() ,生成的序号是连续的，例如，1,2,2,3

 DECLARE @pageIndex int,@pageSize INT,@counts int,@pageCounts int  
 SET @pageIndex=1
 SET @pageSize=20
 select ROWID,ID,ShareID,ShareType,ClientCode,Remark from ( 
   select DENSE_RANK()over ( order by ShareID )  AS ROWID,* from dbo.ClientShareConfig 
   ) cls
   where ROWID between @pageSize*(@pageIndex-1)+1 and @pageSize*@pageIndex ORDER BY cls.ROWID

set @counts=(select MAX(ROWID) from 
(select DENSE_RANK()over ( order by ShareID ) AS ROWID,* from dbo.ClientShareConfig)client1)

set @pageCounts=(SELECT CASE WHEN MAX(ROWID)%@pageSize=0 THEN MAX(ROWID)/@pageSize ELSE MAX(ROWID)/@pageSize+1 END from 
(select DENSE_RANK()over ( order by ShareID ) AS ROWID,* from dbo.ClientShareConfig)client2)

结果：

2、使用函数RANK() ，生成的序号非连续的，例如：1,2,2,4

SELECT RANK() OVER (ORDER BY ShareID)AS RowIndex, * FROM dbo.ClientShareConfig

执行结果：

3、ROW_NUMBER() 就不一样了，它和上面两种的区别就很明显了，这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名

SELECT ROW_NUMBER() OVER(PARTITION BY ShareID ORDER BY ID)AS RowIndex, * FROM dbo.ClientShareConfig

执行结果：

以上使用表的SQL语句：

CREATE TABLE [dbo].[ClientShareConfig](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ShareID] [uniqueidentifier] NOT NULL,
    [ShareType] [int] NOT NULL,
    [ClientCode] [nvarchar](20) NOT NULL,
    [Remark] [nvarchar](200) NULL,
 CONSTRAINT [PK__ClientShareConfi__1748F343] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自增' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ClientShareConfig', @level2type=N'COLUMN',@level2name=N'ID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'每一组共享数据的ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ClientShareConfig', @level2type=N'COLUMN',@level2name=N'ShareID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'共享类型：1-用于物料编码的客户编号；' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ClientShareConfig', @level2type=N'COLUMN',@level2name=N'ShareType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'客户编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ClientShareConfig', @level2type=N'COLUMN',@level2name=N'ClientCode'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ClientShareConfig', @level2type=N'COLUMN',@level2name=N'Remark'
GO

 相关文档：Sql Server2005对t-sql的增强之排名函数ROW_NUMBER, RANK, DENSE_RANK, NTILE