全部
基础类
应用实例
新技术前沿

多表update问题，在线等。

mathematician 2007-06-18 02:51:47

表fm,fl表为父子表

表fm pk(id)
id range
200610001 null
200610002 null
200610003 null

表fl pk(id,pno)
id pno dno
200610001 1 2006Y0012
200610001 2 2006Y0013
200610001 3 2006Y0014
200610001 4 2006Y0015
200610002 1 2006Y0016
200610002 2 2006Y0017
200610003 1 2006Y0018

现在要求更新fm表，是range为如下形式：
id range
200610001 2006Y0012-2006Y0015
200610002 2006Y0016-2006Y0017
200610003 2006Y0018-2006Y0018

非常着急，请各位帮忙。谢谢！

...全文

254 点赞收藏 9

写回复

9 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

mathematician 2007-06-18

谢谢各位！马上结帖！谢谢！

回复赞

fwacky 2007-06-18

create table fm( id varchar(10), range varchar(50))
insert fm
select '200610001', null
union select '200610002', null
union select '200610003' , null
go
create table pk(id varchar(10), pno int , dno varchar(10))

insert pk
select '200610001', 1 ,'2006Y0012'
union select '200610001', 2 ,'2006Y0013'
union select '200610001', 3 ,'2006Y0014'
union select '200610001', 4 ,'2006Y0015'
union select '200610002', 1 ,'2006Y0016'
union select '200610002', 2 ,'2006Y0017'
union select '200610003', 1 ,'2006Y0018'

select * from fm
select * from pk

update fm set range = B.dno from fm,(select id , min(dno) +'-' + max(dno) as dno from pk group by id) B where B.id = fm.id

/*
id range
---------- --------------------------------------------------
200610001 2006Y0012-2006Y0015
200610002 2006Y0016-2006Y0017
200610003 2006Y0018-2006Y0018

*/

回复赞

肥胖的柠檬 2007-06-18

create table #aa (id int, pno int, dno varchar(20))
insert into #aa
select 200610001 , 1 , '2006Y0012' union all
select 200610001 , 2 , '2006Y0013' union all
select 200610001 , 3 , '2006Y0014' union all
select 200610001, 4 , '2006Y0015' union all
select 200610002, 1 , '2006Y0016' union all
select 200610002 , 2 , '2006Y0017' union all
select 200610003 , 1 , '2006Y0018'
select b.id,(select dno from #aa where a.id=id and pno=a.p1)+'-'+(select dno from #aa where a.id=id and pno=a.p2)
from
(
select id,max(pno) p1,min(pno) p2 from #aa group by id
) a
---------------------------------------------------------------
update fm set range =
(select dno from #aa where a.id=id and pno=a.p1)+'-'+(select dno from #aa where a.id=id and pno=a.p2)
from
(
select id,max(pno) p1,min(pno) p2 from #aa group by id
) a ,b where a.id=b.id

回复赞

GEPIN 2007-06-18

update fm
set range = b.dno + '-' + c.dno
from fm a
outer apply(
select top 1 dno
from fl
where id = a.id
order by pno
)b
outer apply(
select top 1 dno
from fl
where id = a.id
order by pno desc
)c

回复赞

leo_lesley 2007-06-18

create table fm( id varchar(10), range varchar(50))
insert fm
select '200610001', null
union select '200610002', null
union select '200610003' , null
go
create table pk(id varchar(10), pno int , dno varchar(10))
insert pk
select '200610001', 1 ,'2006Y0012'
union select '200610001', 2 ,'2006Y0013'
union select '200610001', 3 ,'2006Y0014'
union select '200610001', 4 ,'2006Y0015'
union select '200610002', 1 ,'2006Y0016'
union select '200610002', 2 ,'2006Y0017'
union select '200610003', 1 ,'2006Y0018'

update fm set range=(select min(dno)+'-'+max(dno) from pk where id=fm.id)

select * from fm

drop table fm,pk

/*
id range
---------- --------------------------------------------------
200610001 2006Y0012-2006Y0015
200610002 2006Y0016-2006Y0017
200610003 2006Y0018-2006Y0018

*/

回复赞

hongqi162 2007-06-18

select id ,min(dno)+'-'+max(dno) as range from ttt group by id

1 200610001 2006Y0012-2006Y0015
2 200610002 2006Y0016-2006Y0017
3 200610003 2006Y0018-2006Y0018

回复赞

friendliu 2007-06-18

declare @id varchar(20)
declare @range varchar(30)
DECLARE fm_cursor CURSOR
FOR SELECT * FROM fm
OPEN fm_cursor
FETCH NEXT FROM fm_cursor into @id
WHILE @@FETCH_STATUS = 0
begin
select @range=min(dno)+'-'+max(dno) from fl where id=@id
update fm set range=@range where id=@id
FETCH NEXT FROM fm_cursor into @id
end
CLOSE fm_cursor
DEALLOCATE fm_cursor

回复赞

子陌红尘 2007-06-18

create table fm(id varchar(10),range varchar(40))
insert into fm values('200610001',null)
insert into fm values('200610002',null)
insert into fm values('200610003',null)

create table fl(id varchar(10),pno int,dno varchar(10))
insert into fl values('200610001',1,'2006Y0012')
insert into fl values('200610001',2,'2006Y0013')
insert into fl values('200610001',3,'2006Y0014')
insert into fl values('200610001',4,'2006Y0015')
insert into fl values('200610002',1,'2006Y0016')
insert into fl values('200610002',2,'2006Y0017')
insert into fl values('200610003',1,'2006Y0018')

update t
set
range= (select dno from fl f where f.id=t.id and not exists(select 1 from fl where id=f.id and pno<f.pno))
+'-'+(select dno from fl f where f.id=t.id and not exists(select 1 from fl where id=f.id and pno>f.pno))
from
fm t

select * from fm
/*
id range
---------- ----------------------------------------
200610001 2006Y0012-2006Y0015
200610002 2006Y0016-2006Y0017
200610003 2006Y0018-2006Y0018
*/

drop table fm,fl

回复赞