select b.rowid,b.ia1,b.ia2 from test1 a,test2 b where a.id1=b.ia1 and a.id2<>b.ia2 加rowid可以区分重的纪录
select * from test2 b where not exists(select null from test1 a where a.id1=b.ia1 and a.id2=b.ia2)
SELECT DISTINCT IA1, IA2 FROM TEST2, TEST1 WHERE IA1 IN (SELECT ID1 FROM TEST1) AND IA2 NOT IN (SELECT ID2 FROM TEST1 WHERE ID1 = IA1);试过了,可以
仅仅要这么一个结果? select 1 as 'IA!' ,3 as 'IA2' from dual union select 1 as 'IA!' ,4 as 'IA2' from dual union select 1 as 'IA!' ,5 as 'IA2' from dual union select 2 as 'IA!' ,2 as 'IA2' from dual union select 2 as 'IA!' ,4 as 'IA2' from dual union select 2 as 'IA!' ,5 as 'IA2' from dualha 还是把你想要的逻辑关系描述清楚吧 估计楼上几位的猜测是你的意图
加rowid可以区分重的纪录
FROM TEST2, TEST1
WHERE IA1 IN (SELECT ID1 FROM TEST1)
AND IA2 NOT IN (SELECT ID2 FROM TEST1 WHERE ID1 = IA1);试过了,可以
select 1 as 'IA!' ,3 as 'IA2' from dual
union
select 1 as 'IA!' ,4 as 'IA2' from dual
union
select 1 as 'IA!' ,5 as 'IA2' from dual
union
select 2 as 'IA!' ,2 as 'IA2' from dual
union
select 2 as 'IA!' ,4 as 'IA2' from dual
union
select 2 as 'IA!' ,5 as 'IA2' from dualha
还是把你想要的逻辑关系描述清楚吧
估计楼上几位的猜测是你的意图
tsj68(tsj), greenbeer(啤酒心情)的方法正确,看起来tsj68的方法更简洁一些,但不知道select null是什么意思呢??
tsj68的方法不仅更简洁一些,而且效率还更高.
select null -- 仅表示一个集合而已,你也可以
写成"select 'X'"等其他的语句,效果都一样的
其实就是tsj68的做法