玩SQL的达人都帮忙进来看看，一个分组自动编号问题
有如下数据：
code id
36048121220001,
36048121220001,
36048121220002,
36048121220002,
36048121220002,
36048121220003,
36048121220003,
36048121220003,
36048121220003,
36048121220003,
36048121220004,
36048121220004,
36048121220005,
36048121220005,
36048121220005,
要求达到如下效果
code id
36048121220001,1
36048121220001,2
36048121220002,1
36048121220002,2
36048121220002,3
36048121220003,1
36048121220003,2
36048121220003,3
36048121220003,4
36048121220004,1
36048121220004,2
36048121220005,1
36048121220005,2
36048121220005,3
要求根据code分组，id自动编号，并且每组重新开始编号。


[解决办法]
-- sql 2005用row_number就行了
select code, id = row_number()over(partition by code order by code)
from tb
[解决办法]
Create Table TEST
(code Char(14),
id Int)
Insert TEST Select '36048121220001 ', Null
Union All Select '36048121220001 ', Null
Union All Select '36048121220002 ', Null
Union All Select '36048121220002 ', Null
Union All Select '36048121220002 ', Null
Union All Select '36048121220003 ', Null
Union All Select '36048121220003 ', Null
Union All Select '36048121220003 ', Null
Union All Select '36048121220003 ', Null
Union All Select '36048121220003 ', Null
Union All Select '36048121220004 ', Null
Union All Select '36048121220004 ', Null
Union All Select '36048121220005 ', Null
Union All Select '36048121220005 ', Null
Union All Select '36048121220005 ',Null
GO
Declare @code Char(14), @id Int
Select @code = ' ', @id = 0
Update TEST Set id = @id, @id = (Case code When @code Then @id + 1 Else 1 End), @code = code

Select * From TEST
GO
Drop Table TEST
--Result
/*
codeid
360481212200021
360481212200022
360481212200023
360481212200031
360481212200032
360481212200033
360481212200034
360481212200035
360481212200041
360481212200042
360481212200051
360481212200052
360481212200053
*/