求一条SQL语句

[lsp69]

有以下表:t
servicesid services b_flag b_id
1 A 1 1
2 A 1 2
3 A 1 4
4 A 0 0
5 A 0 0
6 B 1 1
..
..

我要得到的结果是:修改表中的b_id列,使他产生顺序号,条件是:
where services=? and b_flag=1,例如上面应该要变为:
servicesid services b_flag b_id
1 A 1 1
2 A 1 2
3 A 1 3
4 A 0 0
5 A 0 0
6 B 1 1
..
..

[昵称被占用了]

update a
set b_id =(select count(*) from t where services=a.services and b_flag=1 and servicesid<=a.servicesid)
from t a
where  b_flag=1

[wzy_love_sly]

create table ceshi(servicesid int,services varchar(10),b_flag int,b_id int)
insert into ceshi select 1,'a',1,1
insert into ceshi select 2,'a',1,2
insert into ceshi select 3,'a',1,4
insert into ceshi select 4,'a',0,0
insert into ceshi select 5,'a',0,0
insert into ceshi select 6,'b',1,1
select * from ceshi 

update ceshi set b_id=(select count(1) from ceshi 
where services=a.services and servicesid<=a.servicesid)
from ceshi a where b_flag=1

select * from ceshi

servicesid services b_flag b_id
1 a 1 1
2 a 1 2
3 a 1 3
4 a 0 0
5 a 0 0
6 b 1 1

[dawugui]

select servicesid,services,b_flag,b_id = (select count(1) from tb where services=t.services and b_flag = t.b_flag and b_flag <> 0 servicesid < t.servicesid) + 1 from tb t

[dawugui]

create table tb(servicesid int,services varchar(10),b_flag int,b_id int)
insert into tb select 1,'a',1,1
insert into tb select 2,'a',1,2
insert into tb select 3,'a',1,4
insert into tb select 4,'a',0,0
insert into tb select 5,'a',0,0
insert into tb select 6,'b',1,1
go

select servicesid,services,b_flag,b_id = (select count(1) from tb where services=t.services and b_flag = t.b_flag and b_flag <> 0 and servicesid < t.servicesid) + 1 from tb t where b_flag <> 0
union all
select * from tb where b_flag = 0
order by servicesid

drop table tb

/*
servicesid  services   b_flag      b_id
----------- ---------- ----------- -----------
1           a          1           1
2           a          1           2
3           a          1           3
4           a          0           0
5           a          0           0
6           b          1           1

(6 行受影响)

*/

[wzy_love_sly]

lz,怎么？

[zhengchunjia]

利用一下楼上的测试表啊:)
create table ceshi(servicesid int,services varchar(10),b_flag int,b_id int)
insert into ceshi select 1,'a',1,1
insert into ceshi select 2,'a',1,2
insert into ceshi select 3,'a',1,4
insert into ceshi select 4,'a',0,0
insert into ceshi select 5,'a',0,0
insert into ceshi select 6,'b',1,1
select * from ceshi

declare @i int
set @i=0
update
ceshi
set
@i=@i+1,
b_id=@i
如果需要每个services都有自己的序号,可以根据这段代码再改一点即可!

where
b_flag=1