有以下表: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
..
..
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
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=1select * from ceshiservicesid 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
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
goselect 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 servicesiddrop 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 行受影响)*/
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