如果表B多一行1 A1000 A1009 2007-12-22 S001 ab 北京
* A1000 A1007 2007-12-22 S001 ac 上海
2 A1001 A1002 2007-12-23 S002 bc 上海
3 A2005 A2010 2007-12-24 S007 ac 北京A1001对应哪个城市?
* A1000 A1007 2007-12-22 S001 ac 上海
2 A1001 A1002 2007-12-23 S002 bc 上海
3 A2005 A2010 2007-12-24 S007 ac 北京A1001对应哪个城市?
id xlh sv scpi oem
-- ---- -- --- ---
1 A1001
2 A2008
b表:
id sn end_sn sc_date scpi sv oem
-- -- ---- ----- --- -- ---
1 A1000 A1009 2007-12-22 S001 ab 北京
2 A1001 A1002 2007-12-23 S002 bc 上海
3 A2005 A2010 2007-12-24 S007 ac 北京 --
update a
set sv = b.sv,scpi = b.scpi,oem = b.sem
from tablea a
left join (select * from tableb bb where not exists(select 1 from tableb where left(sn,4) = left(bb.sn,4) and sv<bb.sv) b
on left(a.xlh,4) = left(b.sn,4)
gocreate table t1 (
id int,
xlh varchar(10),
sv varchar(10),
scpi varchar(8),
oem nvarchar(10)
)
goinsert into t1
select 1,'A1001','','',N'' union all
select 2,'A2008','','',N''
gocreate table t2 (
id int,
sn varchar(10),
end_sn varchar(10),
sc_date smalldatetime,
scpi varchar(8),
sv varchar(10),
oem nvarchar(10)
)
goinsert into t2
select 1,'A1000','A1009',getdate(),'S001','ab',N'北京' union all
select 2,'A1001','A1002',getdate(),'S002','bc',N'上海' union all
select 3,'A2005','A2010',getdate(),'S007','ac',N'北京'
goupdate t1
set t1.sv=t3.sv, t1.scpi=t3.scpi, t1.oem=t3.oem
from t1, (
select #.xlh, t2.* from t2 inner join (
select t1.xlh, max(sc_date) sc_date
from t1 left join t2
on t1.xlh>=t2.sn and t1.xlh<=t2.end_sn
group by t1.xlh ) # on t2.sc_date = #.sc_date
) t3
where t1.xlh=t3.xlh
goselect * from t1
go
/*
id xlh sv scpi oem
----------- ---------- ---------- -------- ----------
1 A1001 bc S002 上海
2 A2008 ac S007 北京
*/
----------- ---------- ---------- ------------------------------------------------------ -------- ---------- ----------
1 A1000 A1009 2007-12-23 11:58:00 S001 ab 北京
2 A1001 A1002 2007-12-23 11:59:00 S002 bc 上海
3 A2005 A2010 2007-12-24 11:59:00
update t1
set t1.sv=t3.sv, t1.scpi=t3.scpi, t1.oem=t3.oem
from t1, (
select #.xlh, t2.* from t2 inner join (
select t1.xlh, max(sc_date) sc_date
from t1 left join t2
on t1.xlh>=t2.sn and t1.xlh<=t2.end_sn
group by t1.xlh ) # on t2.sc_date = #.sc_date
) t3
where t1.xlh=t3.xlh
你的这个能给我分析下么?今天头痛,看东西有点晕晕的,谢谢
--最复杂的是对于一个a.xlh,满足a.xlh <=b.end_sn,a.xlh> =b.sn的条件的记录可能在b表中存在多条记录,必须取sc_date--(datetime型)最大的那条来更新a表
--所以进行左连接并group by选出对每个xlh的最大日期
select t1.xlh, max(sc_date) sc_date
from t1 left join t2
on t1.xlh>=t2.sn and t1.xlh<=t2.end_sn
group by t1.xlh select #.xlh, t2.* from t2 inner join (...) # on t2.sc_date = #.sc_date
--则是为了取全表B中的相应字段 好做随后的更新 ()起来做t3update t1
set t1.sv=t3.sv, t1.scpi=t3.scpi, t1.oem=t3.oem
from t1, (...) t3
where t1.xlh=t3.xlh
--则是根据xlh匹配把t3中的字段更新到t1 即表A--语句看起来有点复杂 其实很简单的