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