update A set cc=( case when A.bb<B.dd then (select min(t.bb) from B t where t.bb<=B.bb) else B.cc end ) from A join B on A.aa=B.aa
不好意思,修改一下create table A ( aa varchar(10), bb datetime, cc varchar(10) ) create table B ( aa varchar(10), bb varchar(10), cc varchar(10), dd datetime ) insert A select '0001','2005-09-14 08:45:06',0 union select '0001','2005-09-14 09:45:06',0 union select '0001','2005-09-18 11:49:32',0 union select '0001','2005-09-18 08:45:06',0 union select '0001','2005-09-20 17:55:01',0 union select '0001','2005-09-20 08:45:06',0 insert B select '0001','x','y','2005-09-18 00:58:58' union select '0001','y','z','2005-09-20 13:49:03' --更新 update A set cc=( case when A.bb<T.dd then (select bb from B where dd=(select min(dd) from B where dd>A.bb)) else T.cc end ) from A join B T on A.aa=T.aaselect * from A--删除测试环境 drop table A,B --结果 /* aa bb cc ---------- ------------------------------------------------------ ---------- 0001 2005-09-14 08:45:06.000 x 0001 2005-09-14 09:45:06.000 x 0001 2005-09-18 08:45:06.000 y 0001 2005-09-18 11:49:32.000 y 0001 2005-09-20 08:45:06.000 y 0001 2005-09-20 17:55:01.000 z(所影响的行数为 6 行) */
好像没用呢 结果为这样了 aa bb cc 0001 2005-09-14 08:45:06 y 0001 2005-09-14 09:45:06 y 0001 2005-09-18 11:49:32 y 0001 2005-09-18 08:45:06 y 0001 2005-09-20 17:55:01 z 0001 2005-09-20 08:45:06 y
INSERT A SELECT '0001' ,'2005-09-14 08:45:06','0' UNION ALL SELECT '0001', '2005-09-14 09:45:06','0' UNION ALL SELECT '0001', '2005-09-18 11:49:32', '0' UNION ALL SELECT '0001', '2005-09-18 08:45:06', '0' UNION ALL SELECT '0001', '2005-09-20 17:55:01', '0' UNION ALL SELECT '0001', '2005-09-20 08:45:06', '0'INSERT B SELECT '0001', 'x', 'y', '2005-09-18 00:58:58' UNION ALL SELECT '0001', 'y', 'z', '2005-09-20 13:49:03'UPDATE A SET A.CC=C.BB FROM A,( SELECT (CASE WHEN B.AA IS NULL THEN B1.AA ELSE B.AA END) AS AA, (CASE WHEN B.CC IS NULL THEN B1.BB ELSE B.CC END)AS BB , CASE WHEN B.DD IS NULL THEN '1753-1-1' ELSE B.DD END AS BEGINDATE , CASE WHEN B1.DD IS NULL THEN '9999-12-31' ELSE B1.DD END AS ENDDATE FROM B FULL JOIN B B1 ON B.CC=B1.BB AND B.AA=B1.AA ) AS C WHERE A.AA=C.AA AND A.BB BETWEEN C.BEGINDATE AND C.ENDDATE
create table A ( aa varchar(10), bb datetime, cc varchar(10) ) create table B ( aa varchar(10), bb varchar(10), cc varchar(10), dd datetime )INSERT A SELECT '0001' ,'2005-09-14 08:45:06','0' UNION ALL SELECT '0001', '2005-09-14 09:45:06','0' UNION ALL SELECT '0001', '2005-09-18 11:49:32', '0' UNION ALL SELECT '0001', '2005-09-18 08:45:06', '0' UNION ALL SELECT '0001', '2005-09-20 17:55:01', '0' UNION ALL SELECT '0001', '2005-09-20 08:45:06', '0'INSERT B SELECT '0001', 'x', 'y', '2005-09-18 00:58:58' UNION ALL SELECT '0001', 'y', 'z', '2005-09-20 13:49:03'UPDATE A SET A.CC=C.BB FROM A,( SELECT (CASE WHEN B.AA IS NULL THEN B1.AA ELSE B.AA END) AS AA, (CASE WHEN B.CC IS NULL THEN B1.BB ELSE B.CC END)AS BB , CASE WHEN B.DD IS NULL THEN '1753-1-1' ELSE B.DD END AS BEGINDATE , CASE WHEN B1.DD IS NULL THEN '9999-12-31' ELSE B1.DD END AS ENDDATE FROM B FULL JOIN B B1 ON B.CC=B1.BB AND B.AA=B1.AA ) AS C WHERE A.AA=C.AA AND A.BB BETWEEN C.BEGINDATE AND C.ENDDATE结果:--结果 /* aa bb cc ---------- ------------------------------------------------------ ---------- 0001 2005-09-14 08:45:06.000 x 0001 2005-09-14 09:45:06.000 x 0001 2005-09-18 08:45:06.000 y 0001 2005-09-18 11:49:32.000 y 0001 2005-09-20 08:45:06.000 y 0001 2005-09-20 17:55:01.000 z(所影响的行数为 6 行) */
如表A中 2005-09-14 08:45:06 小于表B中的 2005-09-18 00:58:58和 2005-09-20 13:49:03
但更新时,只取 2005-09-18 00:58:58对应的那一行的bb (x) 而不是取 2005-09-20 13:49:03 对应的
(y)只取最近的
set cc=( case when A.bb<B.dd
then (select min(t.bb) from B t where t.bb<=B.bb)
else B.cc
end
)
from A
join B on A.aa=B.aa
(
aa varchar(10),
bb datetime,
cc varchar(10)
)
create table B
(
aa varchar(10),
bb varchar(10),
cc varchar(10),
dd datetime
)
insert A
select '0001','2005-09-14 08:45:06',0 union
select '0001','2005-09-14 09:45:06',0 union
select '0001','2005-09-18 11:49:32',0 union
select '0001','2005-09-18 08:45:06',0 union
select '0001','2005-09-20 17:55:01',0 union
select '0001','2005-09-20 08:45:06',0
insert B
select '0001','x','y','2005-09-18 00:58:58' union
select '0001','y','z','2005-09-20 13:49:03' --更新
update A
set cc=( case when A.bb<T.dd
then (select bb from B where dd=(select min(dd) from B where dd>A.bb))
else T.cc
end
)
from A
join B T on A.aa=T.aaselect * from A--删除测试环境
drop table A,B
--结果
/*
aa bb cc
---------- ------------------------------------------------------ ----------
0001 2005-09-14 08:45:06.000 x
0001 2005-09-14 09:45:06.000 x
0001 2005-09-18 08:45:06.000 y
0001 2005-09-18 11:49:32.000 y
0001 2005-09-20 08:45:06.000 y
0001 2005-09-20 17:55:01.000 z(所影响的行数为 6 行)
*/
结果为这样了
aa bb cc
0001 2005-09-14 08:45:06 y
0001 2005-09-14 09:45:06 y
0001 2005-09-18 11:49:32 y
0001 2005-09-18 08:45:06 y
0001 2005-09-20 17:55:01 z
0001 2005-09-20 08:45:06 y
SELECT '0001' ,'2005-09-14 08:45:06','0'
UNION ALL SELECT '0001', '2005-09-14 09:45:06','0'
UNION ALL SELECT '0001', '2005-09-18 11:49:32', '0'
UNION ALL SELECT '0001', '2005-09-18 08:45:06', '0'
UNION ALL SELECT '0001', '2005-09-20 17:55:01', '0'
UNION ALL SELECT '0001', '2005-09-20 08:45:06', '0'INSERT B
SELECT '0001', 'x', 'y', '2005-09-18 00:58:58'
UNION ALL SELECT '0001', 'y', 'z', '2005-09-20 13:49:03'UPDATE A SET A.CC=C.BB
FROM A,(
SELECT (CASE WHEN B.AA IS NULL THEN B1.AA ELSE B.AA END) AS AA,
(CASE WHEN B.CC IS NULL THEN B1.BB ELSE B.CC END)AS BB ,
CASE WHEN B.DD IS NULL THEN '1753-1-1' ELSE B.DD END AS BEGINDATE ,
CASE WHEN B1.DD IS NULL THEN '9999-12-31' ELSE B1.DD END AS ENDDATE
FROM B FULL JOIN B B1 ON B.CC=B1.BB AND B.AA=B1.AA
) AS C
WHERE A.AA=C.AA AND A.BB BETWEEN C.BEGINDATE AND C.ENDDATE
(
aa varchar(10),
bb datetime,
cc varchar(10)
)
create table B
(
aa varchar(10),
bb varchar(10),
cc varchar(10),
dd datetime
)INSERT A
SELECT '0001' ,'2005-09-14 08:45:06','0'
UNION ALL SELECT '0001', '2005-09-14 09:45:06','0'
UNION ALL SELECT '0001', '2005-09-18 11:49:32', '0'
UNION ALL SELECT '0001', '2005-09-18 08:45:06', '0'
UNION ALL SELECT '0001', '2005-09-20 17:55:01', '0'
UNION ALL SELECT '0001', '2005-09-20 08:45:06', '0'INSERT B
SELECT '0001', 'x', 'y', '2005-09-18 00:58:58'
UNION ALL SELECT '0001', 'y', 'z', '2005-09-20 13:49:03'UPDATE A SET A.CC=C.BB
FROM A,(
SELECT (CASE WHEN B.AA IS NULL THEN B1.AA ELSE B.AA END) AS AA,
(CASE WHEN B.CC IS NULL THEN B1.BB ELSE B.CC END)AS BB ,
CASE WHEN B.DD IS NULL THEN '1753-1-1' ELSE B.DD END AS BEGINDATE ,
CASE WHEN B1.DD IS NULL THEN '9999-12-31' ELSE B1.DD END AS ENDDATE
FROM B FULL JOIN B B1 ON B.CC=B1.BB AND B.AA=B1.AA
) AS C
WHERE A.AA=C.AA AND A.BB BETWEEN C.BEGINDATE AND C.ENDDATE结果:--结果
/*
aa bb cc
---------- ------------------------------------------------------ ----------
0001 2005-09-14 08:45:06.000 x
0001 2005-09-14 09:45:06.000 x
0001 2005-09-18 08:45:06.000 y
0001 2005-09-18 11:49:32.000 y
0001 2005-09-20 08:45:06.000 y
0001 2005-09-20 17:55:01.000 z(所影响的行数为 6 行)
*/