有以下表: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
set
b_id=(select count(*) from t where services=a.services and b_flag=a.b_flag and b_id<=a.b_id)
from
t a
where
a.b_flag=1
insert into @t values(1,'A',1,1)
insert into @t values(2,'A',1,2)
insert into @t values(3,'A',1,4)
insert into @t values(4,'A',0,0)
insert into @t values(5,'A',0,0)
insert into @t values(6,'B',1,1)update a
set
b_id=(select count(*) from @t where services=a.services and b_flag=a.b_flag and b_id<=a.b_id)
from
@t a
where
a.b_flag=1select * from @t
/*
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
*/