34,575
社区成员
发帖
与我相关
我的任务
分享
select * from A,B where a.P_id=b.P_id and( A.p_size<>B.p_size or A.p_color<>B.p_color)
select
*
from
a
where
exists(select 1 from B where p_id=a.p_id)
and
not exists(select 1 from B where p_id=a.p_id and p_color<>a.p_color and p_size<>a.p_size)
create table a(p_id int,p_color int,p_size int)
create table b(p_id int,p_color int,p_size int)
insert into a select 1,1,1 union all select 1,2,2
insert into b select 1,1,1 union all select 1,2,2
go
select * from A,B where a.P_id=b.P_id and A.p_size<>B.p_size and A.p_color<>B.p_color
select * from A
where exists(select 1 from B where p_id=a.p_id)
and not exists(select 1 from B where p_id=a.p_id and p_color<>a.p_color and p_size<>a.p_size)
--楼主说的是"和",但我感觉应该这儿或许应该为
-- p_id=a.pid and (p_color<>a.p_color or p_size<>a.p_size)
--即或p_id相同,P_color不同或p_size不同
union all
select * from B
where exists(select 1 from A where p_id=b.p_id)
and not exists(select 1 from A where p_id=b.p_id and p_color<>b.p_color and p_size<>b.p_size)
select * from A,B where a.P_id=b.P_id and A.p_size<>B.p_size and A.p_color<>B.p_color
select * from A full join B on a.p_id=b.p_id and a.p_size=b.p_size and A.p_color=B.p_color
where nullif(a.P_id,b.P_id) is not null
select * from A full join B on a.p_id=b.p_id and a.p_size=b.p_size where nullif(a.P_id,b.P_id) is not null
select A.*,B.* from A inner join B on A.p_id=B.p_id where A.p_color<>B.p_color and A.p_size<>B.p_size