with tmp as (select 1 id, 'aaa' value from dual union all select 1 id, 'aab' value from dual) select * from tmp a, tmp b where a.id = b.id and a.value <> b.value
用自链接就OK了。with tmp as (select 1 id, 'aaa' value from dual union all select 1 id, 'aab' value from dual) select * from tmp a, tmp b where a.id = b.id and a.value <> b.value
(select 1 id, 'aaa' value
from dual
union all
select 1 id, 'aab' value from dual)
select *
from tmp a, tmp b
where a.id = b.id
and a.value <> b.value
(select 1 id, 'aaa' value
from dual
union all
select 1 id, 'aab' value from dual)
select *
from tmp a, tmp b
where a.id = b.id
and a.value <> b.value