玩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
/*
code id
36048121220002 1
36048121220002 2
36048121220002 3
36048121220003 1
36048121220003 2
36048121220003 3
36048121220003 4
36048121220003 5
36048121220004 1
36048121220004 2
36048121220005 1
36048121220005 2
36048121220005 3
*/