查询以从表中选择具有特定列值的记录

NOT IN 是一种选择：

SQL> with test (bilno, doctyp) as
  2    (select 182, 'bil' from dual union all
  3     select 182, 'xxy' from dual union all
  4     select 111, 'abc' from dual union all
  5     select 111, 'zdv' from dual union all
  6     select 223, 'bil' from dual union all
  7     select 555, 'xzy' from dual
  8    )
  9  select *
 10  from test t
 11  where bilno not in (select bilno
 12                      from test
 13                      where doctyp = 'bil');

     BILNO DOC
---------- ---
       111 zdv
       111 abc
       555 xzy

另一个是NOT EXISTS：

SQL> with test (bilno, doctyp) as
  2    (select 182, 'bil' from dual union all
  3     select 182, 'xxy' from dual union all
  4     select 111, 'abc' from dual union all
  5     select 111, 'zdv' from dual union all
  6     select 223, 'bil' from dual union all
  7     select 555, 'xzy' from dual
  8    )
  9  select *
 10  from test t
 11  where not exists (select null
 12                    from test t1
 13                    where t1.bilno = t.bilno
 14                      and t1.doctyp = 'bil'
 15                   );

     BILNO DOC
---------- ---
       111 zdv
       111 abc
       555 xzy