导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

求一条SQL语句

lsp69 2007-12-26 03:57:12
有以下表: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
..
..



...全文
50 点赞 收藏 6
写回复
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
zhengchunjia 2007-12-26
利用一下楼上的测试表啊:)
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
回复
wzy_love_sly 2007-12-26
lz,怎么?
回复
dawugui 2007-12-26
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 行受影响)

*/
回复
dawugui 2007-12-26
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
回复
wzy_love_sly 2007-12-26
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
回复

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

回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告