select a.* from a,b where a. PART_NO=b. PART_NO and a.PART_GRP =b.PART_GRP and (a.WHSE_ID<>b.WHSE_ID and a.WHSE_LOC<>b.WHSE_LOC)
select a.* from a,b where a. PART_NO=b. PART_NO and a.PART_GRP =b.PART_GRP and (a.WHSE_ID<>b.WHSE_ID and a.WHSE_LOC<>b.WHSE_LOC)
运行之后会出来三条记录
WHSE_ID WHSE_LOC PART_NO PART_GRP
---------- ---------- ---------- ----------
B B TEST TEST1
A A TEST TEST1
B B TEST TEST1
insert into a values('A' , 'A' , 'TEST' , 'TEST1')
insert into a values('B' , 'B' , 'TEST' , 'TEST1')
create table B(WHSE_ID varchar(10), WHSE_LOC varchar(10), PART_NO varchar(10), PART_GRP varchar(10))
insert into b values('A' , 'A' , 'TEST' , 'TEST1')
insert into b values('C' , 'C' , 'TEST' , 'TEST1')
goselect a.* from a where not exists(select 1 from b where WHSE_ID = a.WHSE_ID and WHSE_LOC = a.WHSE_LOC)drop table a , b/*
WHSE_ID WHSE_LOC PART_NO PART_GRP
---------- ---------- ---------- ----------
B B TEST TEST1(所影响的行数为 1 行)
*/