用3个表: A ,B , C
A表数据(3个字段A1 A2 A3)
A1 A2
S001
S002 A1 B2 B3
S001 2012-01-01 PP01
S001 2012-01-01 PP02
S001 2012-01-01 PP03A1 C2 B3
S001 2012-01-01 PP01
S001 2012-01-03 PP02
S001 2012-01-01 PP03
S001 2012-01-03 PP03
要求:更新A表A3数据,只要C表数据里的C2数据时间日期与B表里的B2数据相比,在B.A1=C.A1 AND B.B3=C.B3的前提条件下 如果C表里的最大的C2值比B2晚的,则更新A表数据里的A2为T ,如果C表里的C2值最大值比B2早的,则更新A表数据里的A2为F.
求这语句!
A表数据(3个字段A1 A2 A3)
A1 A2
S001
S002 A1 B2 B3
S001 2012-01-01 PP01
S001 2012-01-01 PP02
S001 2012-01-01 PP03A1 C2 B3
S001 2012-01-01 PP01
S001 2012-01-03 PP02
S001 2012-01-01 PP03
S001 2012-01-03 PP03
要求:更新A表A3数据,只要C表数据里的C2数据时间日期与B表里的B2数据相比,在B.A1=C.A1 AND B.B3=C.B3的前提条件下 如果C表里的最大的C2值比B2晚的,则更新A表数据里的A2为T ,如果C表里的C2值最大值比B2早的,则更新A表数据里的A2为F.
求这语句!
insert into a values('S001','')
insert into a values('S002','')
create table b(A1 varchar(10),B2 datetime,B3 varchar(10))
insert into b values('S001', '2012-01-01', 'PP01')
insert into b values('S001', '2012-01-01', 'PP02')
insert into b values('S001', '2012-01-01', 'PP03')
create table C(A1 varchar(10),C2 datetime,B3 varchar(10))
insert into C values('S001', '2012-01-01', 'PP01')
insert into C values('S001', '2012-01-03', 'PP02')
insert into C values('S001', '2012-01-01', 'PP03')
insert into C values('S001', '2012-01-03', 'PP03')
goupdate a set a2 =
(case when exists(select 1 from b where c2 > b2 and b.a1 = c.a1 and b.b3 = c.b3) then 'T' else 'F' end)
from a ,b,c
where a.a1 = b.a1 and b.a1 = c.a1 and b.b3 = c.b3select * from adrop table a , b, c/*
A1 A2
---------- ----
S001 T
S002 (所影响的行数为 2 行)
*/
S001 2012-01-01 PP01
S001 2012-01-03 PP02只要C 表里没有B表里对应的数据,刚A表的A2也要为F, 这个好像没有考虑进去!
insert into a values('S001','')
insert into a values('S002','')
create table b(A1 varchar(10),B2 datetime,B3 varchar(10))
insert into b values('S001', '2012-01-01', 'PP01')
insert into b values('S001', '2012-01-01', 'PP02')
insert into b values('S001', '2012-01-01', 'PP03')
create table C(A1 varchar(10),C2 datetime,B3 varchar(10))
insert into C values('S001', '2012-01-01', 'PP01')
insert into C values('S001', '2012-01-03', 'PP02')
insert into C values('S001', '2012-01-01', 'PP03')
insert into C values('S001', '2012-01-03', 'PP03')
goupdate a set a2 =
isnull((case when exists(select 1 from b where c2 > b2 and b.a1 = c.a1 and b.b3 = c.b3) then 'T' else 'F' end),'F')
from a left join b on a.a1 = b.a1
left join c on b.a1 = c.a1 and b.b3 = c.b3select * from adrop table a , b, c/*
A1 A2
---------- ----
S001 T
S002 F(所影响的行数为 2 行)
*/