110,565
社区成员
发帖
与我相关
我的任务
分享
create table t1
(
p int,
q char(1)
)
--delete * from t1
insert t1 (p, q) values (1, 'a')
insert t1 (p, q) values (2, 'b')
insert t1 (p, q) values (3, 'c')
insert t1 (p, q) values (4, 'd')
create table t2
(
p int,
q char(1)
)
--delete * from t2
insert t2 (p, q) values (1, 'a')
insert t2 (p, q) values (1, 'b')
insert t2 (p, q) values (2, 'b')
insert t2 (p, q) values (2, 'd')
insert t2 (p, q) values (4, 'f')
select *
from
(
select t1.p, qq1 = t1.q,
qq2 = case when t1.q=t2.q then t2.q else null end
from t1 left join t2
on t1.p = t2.p
union
select t2.p,
qq1 = case when t1.q=t2.q then t1.q else null end ,
qq2 = t2.q
from t1 left join t2
on t1.p = t2.p
) table1
where table1.p is not null