select userinfo from a where lineinfo='line2' minus select a.userinfo from a a, (select userinfo from a where lineinfo='line2') b where a.lineinfo<>'line2' and a.userinfo=b.userinfo and a.status='1';测试过的
以下两种方法都行,均测试通过!方法1:select userinfo from your_tab where lineinfo = 'line2' and userinfo not in ( select userinfo from your_tab where lineinfo <> 'line2' and status ='1' )方法2:select userinfo from your_tab a where a.lineinfo = 'line2' and not exists ( select userinfo from your_tab b where b.lineinfo <> 'line2' and a.userinfo = b.userinfo and b.status ='1' )
minus
select a.userinfo from a a,
(select userinfo from a where lineinfo='line2') b
where a.lineinfo<>'line2' and a.userinfo=b.userinfo and a.status='1';测试过的
where lineinfo = 'line2'
and userinfo not in (
select userinfo from your_tab
where lineinfo <> 'line2'
and status ='1' )方法2:select userinfo from your_tab a
where a.lineinfo = 'line2'
and not exists (
select userinfo from your_tab b
where b.lineinfo <> 'line2'
and a.userinfo = b.userinfo
and b.status ='1' )