多表update问题，在线等。
表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
非常着急，请各位帮忙。谢谢！
[解决办法]
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
[解决办法]
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
[解决办法]
select id ,min(dno)+ '- '+max(dno) as range from ttt group by id
12006100012006Y0012-2006Y0015
22006100022006Y0016-2006Y0017
32006100032006Y0018-2006Y0018
[解决办法]
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
*/
[解决办法]
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
[解决办法]
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
*/
