求一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