多表update问题，在线等。

[mathematician]

表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

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

[chuifengde]

update fm set range=x from fm,(select id, min(dno)+'-'+max(dno) x from fl group by id) bb where fm.id=bb.id

[子陌红尘]

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

[friendliu]

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

[hongqi162]

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

[leo_lesley]

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

*/

[GEPIN]

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

[肥胖的柠檬]

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

[fwacky]

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

*/

[mathematician]

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