求一sql语句,如何分组更新记录?
有一表：tableaname   (a   int,b   int)
a     b
1     0
1     0  
1     0
2     0
5     0
5     0
5     0
5     0
现要按a列分组更新b列依次按1递增更新，结果为：
a     b
1     1
1     2
1     3
2     1
5     1
5     2
5     3
5     4
谢谢！



------解决方案--------------------
create table T(a int, b int)
insert T select 1, 0
union all select 1, 0
union all select 1, 0
union all select 2, 0
union all select 5, 0
union all select 5, 0
union all select 5, 0
union all select 5, 0

select ID=identity(int, 1, 1), * into #T from T

delete T

insert T
select a, b=(select count(*) from #T where a=A.a and ID <=A.ID) from #T as A

select * from T

--result
a b
----------- -----------
1 1
1 2
1 3
2 1
5 1
5 2
5 3
5 4

(8 row(s) affected)


------解决方案--------------------
CREATE TABLE T (a INT,b INT)
INSERT INTO T
select 1 ,0 union all
select 1 ,0 union all
select 1 ,0 union all
select 2 ,0 union all
select 5 ,0 union all
select 5 ,0 union all
select 5 ,0 union all
select 5 ,0

SELECT * FROM T

DECLARE @B INT
DECLARE @A INT
SET @B = 0
SET @A = 0
UPDATE T
SET
b =@B
,@B = CASE WHEN @A =a THEN (@B+1) ELSE 1 END
,@A =a
SELECT * FROM T

DROP TABLE T