请尝试以下内容：

select
   a.*
from
   mytable as a
   inner join
   (
   select
      some_subset_id,
      sum(case when some_parent_id is null then 0 else 1 end) as have_parent_id,
      sum(case when some_parent_id is null then 1 else 0 end) as do_not_have_parent_id
   from
      mytable
   group by
      some_subset_id
   having
      sum(case when some_parent_id is null then 0 else 1 end) = 1
      and sum(case when some_parent_id is null then 1 else 0 end) = 1
   ) as b on b.some_subset_id = a.some_subset_id;

您可以在子查询中使用分析函数来筛选数据（WHERE子句）。子查询会定义具有IN或OUT值的SELECTION数据栏，因此您可以使用它来撷取标示为其中一个值的数据列。

SELECT  *
FROM    tbl
WHERE   SUBS_ID IN  (   Select  SUBS_ID
                        From (  Select  SUBS_ID "SUBS_ID", CASE WHEN Count(SUBS_ID) OVER(PARTITION BY SUBS_ID) > 1 And P_ID Is Null THEN 'OUT' ELSE 'IN' END "SELECTION"
                                From    tbl  )
                        Group By  SUBS_ID
                        Having    Max(SELECTION) = 'OUT' )
ORDER BY  SUBS_ID, P_ID NULLS LAST

使用示例数据...

WITH
    tbl AS
        (
            Select 123456 "P_ID", 'SPECIAL'     "P_NAME", 'Special Shop'     "SUBS_NAME", 9876 "SUBS_ID", '1234, Road St.' "ADDRESS" From Dual Union All
            Select Null   "P_ID",  Null         "P_NAME", 'Special Shop'     "SUBS_NAME", 9876 "SUBS_ID", '1234, Road St.' "ADDRESS" From Dual Union All
            Select 654321 "P_ID", 'NOT_SPECIAL' "P_NAME", 'Not Special Shop' "SUBS_NAME", 9877 "SUBS_ID", '1234, Road St.' "ADDRESS" From Dual Union All
            Select 654321 "P_ID", 'NOT_SPECIAL' "P_NAME", 'Not Special Shop' "SUBS_NAME", 9877 "SUBS_ID", '1234, Road St.' "ADDRESS" From Dual
        )

...并且'***具有最大（选择）= ' OUT '***'，结果为：
| 管道标识|名称|子系统名称|子系统标识|地址|
| - -|- -|- -|- -|- -|
| 小行星123456|特殊|专卖店|九八七六|路街1234号|
| | | 专卖店|九八七六|路街1234号|
...并且'***具有最大值（SELECTION）= ' IN '***'，结果为：
| 管道标识|名称|子系统名称|子系统标识|地址|
| - -|- -|- -|- -|- -|
| 小行星654321|非特殊|非专卖店|小行星9877|路街1234号|
| 小行星654321|非特殊|非专卖店|小行星9877|路街1234号|