SQL> WITH t AS (
  2      SELECT '自营' ttype,'001' stockcode,1 mairu FROM dual UNION ALL
  3      SELECT '自营' ttype,'001' stockcode,2 mairu FROM dual UNION ALL
  4      SELECT '自营' ttype,'003' stockcode,1 mairu FROM dual UNION ALL
  5      SELECT '自营' ttype,'003' stockcode,2 mairu FROM dual UNION ALL
  6      SELECT '自营' ttype,'004' stockcode,2 mairu FROM dual UNION ALL
  7      SELECT '资管' ttype,'002' stockcode,1 mairu FROM dual UNION ALL
  8      SELECT '资管' ttype,'001' stockcode,2 mairu FROM dual UNION ALL
  9      SELECT '资管' ttype,'005' stockcode,2 mairu FROM dual UNION ALL
 10      SELECT '资管' ttype,'005' stockcode,2 mairu FROM dual UNION ALL
 11      SELECT '研究' ttype,'003' stockcode,2 mairu FROM dual UNION ALL
 12      SELECT '研究' ttype,'001' stockcode,2 mairu FROM dual
 13  )
 14  SELECT m.ttype,
 15         m.stockcode,
 16         m.mairu
 17    FROM (SELECT t.*,
 18                 COUNT(*) OVER(PARTITION BY t.stockcode) total_cn,
 19                 COUNT(*) OVER(PARTITION BY t.ttype, t.stockcode) cn
 20            FROM t
 21           WHERE t.ttype IN ('自营', '资管')) m
 22   WHERE m.total_cn <> cn
 23  ;
 
TTYPE  STOCKCODE      MAIRU
------ --------- ----------
自营   001                1
自营   001                2
资管   001                2