update这个字段的值, 语句怎么写?

[okitgo]

我有一个表table1是下面这样:
group_name seq_id
aaa NULL
aaa NULL
aaa NULL
bbb NULL
bbb NULL
ccc NULL
..........
现在我要根据group_name分组,再加上一个序列号,就像下面这样:
group_name seq_id
aaa 1
aaa 2
aaa 3
bbb 1
bbb 2
ccc 1
............

[jixiaojie]

下面的这个需要一个自增列，没有自增列的不知道怎么写


create table tb3
(id int identity(1,1),
group_name varchar(100),
seq_id int
)

insert into tb3
select 'aaa',null union all
select 'aaa',null union all
select 'bbb',null union all
select 'bbb',null union all
select 'aaa',null union all
select 'ccc',null

update tb3
set seq_id =(select count(*) from tb3 a where tb3.id >=a.id and tb3.group_name=a.group_name)


select group_name,seq_id from tb3
order by group_name,seq_id


drop table tb3

/*

aaa 1
aaa 2
aaa 3
bbb 1
bbb 2
ccc 1
*/

[zjcxc]

-- sql 2005
select group_name, seq_id=row_number() over(order by group_name partion by group_name)
from table1

[lxzm1001]

create table table1
(group_name varchar(10),
seq_id int)
insert table1 select 'aaa',NULL
union all select 'aaa',NULL
union all select 'aaa',NULL
union all select 'bbb',NULL
union all select 'bbb',NULL
union all select 'ccc',NULL
select id=identity(int),* into #t from table1
update #t set seq_id=(select count(*) from #t where group_name=t.group_name and id<=t.id) from #t t
select group_name,seq_id from #t

[Ninjai_Chan]

SQL2005有这样的函数吗?要用一下了.呵呵!
楼上的都是正解啊!

[lsqkeke]

一楼和三楼的方法都一样！