create table a1(a1 int,b1 int,a2 int,b2 int,a3 int,b3 int)
insert into a1 select 1,1,2,2,3,3
union all select 11,11,22,22,33,33
union all select 111,111,222,222,333,333
create table b1(a int,b int,c int)
insert into b1 select 1,1,5
union all select 22,22,6
union all select 333,333,7select c1=case
when a1.a1=b1.a and a1.b1=b1.b then b1.c
else '0'
end,
c2=case
when a1.a2=b1.a and a1.b2=b1.b then b1.c
else '0'
end,
c3=case
when a1.a3=b1.a and a1.b3=b1.b then b1.c
else '0'
end
from a1,b1
where (a1.a1=b1.a and a1.b1=b1.b) or (a1.a2=b1.a and a1.b2=b1.b) or (a1.a3=b1.a and a1.b3=b1.b)
drop table a1
drop table b1
insert into a1 select 1,1,2,2,3,3
union all select 11,11,22,22,33,33
union all select 111,111,222,222,333,333
create table b1(a int,b int,c int)
insert into b1 select 1,1,5
union all select 22,22,6
union all select 333,333,7select c1=case
when a1.a1=b1.a and a1.b1=b1.b then b1.c
else '0'
end,
c2=case
when a1.a2=b1.a and a1.b2=b1.b then b1.c
else '0'
end,
c3=case
when a1.a3=b1.a and a1.b3=b1.b then b1.c
else '0'
end
from a1,b1
where (a1.a1=b1.a and a1.b1=b1.b) or (a1.a2=b1.a and a1.b2=b1.b) or (a1.a3=b1.a and a1.b3=b1.b)
drop table a1
drop table b1
能不能把他们拼成一条记录哪?
比如
B有三条记录
a b c
1 2 9
3 4 8
5 6 7
A有这样一条记录
a1 b1 a2 b2 a3 b3
1 2 3 4 5 6
现在的结果是
c1 c2 c3
9 0 0
0 8 0
0 0 7
能不能得到
c1 c2 c3
9 8 7
这样的结果?
拜托拜托~
from a t1,a t2,a t3,b t4,b t5 ,b t6
where (t1.a1 = t4.a and t1.b1 = t4.b) and
(t2.a2 = t5.a and t2.b2 = t5.b) and
(t3.a3 = t6.a and t3.b3 = t6.b)