ROW_NUMBER() OVER函数和identity

今天朋友问了我一个问题，虽然解决了但是怕以后忘记所以记录下来，也希望能给其他朋友带来一些提示。问题是：

将表1变成表2 的结果用sql语句实现。

表结构是：

 1 CREATE TABLE [dbo].[table1](
 2 
 3          [a] [int] NULL
 4 
 5 )
 6 
 7  
 8 
 9 insert into table1 values(10)
10 
11 insert into table1 values(10)
12 
13 insert into table1 values(23)
14 
15 insert into table1 values(23)
16 
17 insert into table1 values(23)
18 
19 insert into table1 values(34)
20 
21 insert into table1 values(34)
22 
23 insert into table1 values(234)

View Code

大家都知道sql2000和sql2005的不同之处，就是sql2005新增了一些特性，其中有ROW_NUMBER()这个函数。如果你的sql版本是sql2005及其以上版本那就好办的多了。

其语法是：语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

简单的说row_number()从1开始，为每一条分组记录返回一个数字。

实现这个问题的方法（只用一条sql语句）是：

select a, ROW_NUMBER() OVER(PARTITION BY a ORDER BY a)as 序号 from [table1]

直接就出来结果了，不需要增加自增长列，不需要存储过程，是不是很简单啊？

如果你的sql版本是sql2000，那么这样做，此时要用到identity。先简单对identity介绍一下

语法：IDENTITY [ ( seed , increment ) ]
参数 seed：装载到表中的第一个行所使用的值。
而 increment 是增量值，该值被添加到前一个已装载的行的标识值上。必须同时指定种子和增量，或者二者都不指定。如果二者都未指定，则取默认值 (1,1)。

那么此题的方法是：

--第一步修改table1表，给table1这张表加两个字段（自增长列id,和序号列）

1 alter table table1 add id int identity(1,1)--新增自增长列
2 
3 alter table table1 add 序号 int--添加序号列

--第二步实现存储过程

create PROCEDURE UpdateOrderBy

AS

BEGIN

        --声明变量

         declare @id int = 0

         declare @a int = 0

         declare @序号 int = 0

         DECLARE NewOrderBy CURSOR FOR select a from table1 group by a  --对a进行分组

         OPEN NewOrderBy --打开名称为NewOrderBy的游标

         FETCH NEXT FROM NewOrderBy INTO @a --填充游标

         WHILE @@FETCH_STATUS<>-1

                   BEGIN

                            set @序号=0 --初始化@序号

                            DECLARE NewOrderBy1 CURSOR FOR select id from table1 where a=@a order by id --获取分组后的a的自增编号

                            OPEN NewOrderBy1--打开NewOrderBy1游标

                            FETCH NEXT FROM NewOrderBy1 INTO @id  --填充游标

                            WHILE @@FETCH_STATUS<>-1

                                     BEGIN  

                                               set @序号=@序号+1

                                               update table1 set 序号 = @序号 where id=@id

                                               FETCH NEXT FROM NewOrderBy1 INTO @id --填充下一条游标NewOrderBy1

                                     END                  

                            CLOSE NewOrderBy1 --关闭游标NewOrderBy1

                            DEALLOCATE NewOrderBy1--删除游标NewOrderBy1

                            FETCH NEXT FROM NewOrderBy INTO @a  --填充下一条游标NewOrderBy

                   END                  

         CLOSE NewOrderBy --关闭游标NewOrderBy

         DEALLOCATE NewOrderBy --删除游标NewOrderBy

END

View Code

--第三步执行存储过程 UpdateOrderBy

Exec UpdateOrderBy

--第四步 --删除列id

alter table table1 drop column [id]

--第五步查询结果

select * from table1 order by a