您似乎只想获取这样的行，其中存在一个N，并且每个PN都有一个L，并且这些N也与筛选器匹配;在这种情况下，使用带有条件聚合解析COUNT函数：

SELECT sms, pgm, pn, team, num
FROM   (
  SELECT a.*,
         COUNT(CASE PGM WHEN 'N' THEN 1 END) OVER (PARTITION BY PN) AS cnt_n,
         COUNT(CASE PGM WHEN 'L' THEN 1 END) OVER (PARTITION BY PN) AS cnt_l,
         COUNT(CASE WHEN PGM IN ('L', 'N') THEN NULL ELSE 1 END) OVER (PARTITION BY PN) AS cnt_other
  FROM   ALPHA a
  WHERE  PN   LIKE 'BB%'
  AND    NUM  = '2350'
  AND    TEAM = 'R2D2'
)
WHERE cnt_l > 0
AND   cnt_n > 0
AND   cnt_other = 0
ORDER BY PN ASC;

小提琴
如果有动态列表，则可以创建集合数据类型：

CREATE TYPE char_list IS TABLE OF CHAR(1);

然后使用查询：

SELECT sms, pgm, pn, team, num
FROM   (
  SELECT a.*,
         COUNT(
           DISTINCT
           CASE WHEN PGM MEMBER OF char_list('N', 'L') THEN PGM END
         ) OVER (PARTITION BY PN) AS cnt_match,
         COUNT(
           CASE WHEN PGM NOT MEMBER OF char_list('N', 'L') THEN 1 END
         ) OVER (PARTITION BY PN) AS cnt_other
  FROM   ALPHA a
  WHERE  PN   LIKE 'BB%'
  AND    NUM  = '2350'
  AND    TEAM = 'R2D2'
)
WHERE cnt_match = CARDINALITY( char_list('N', 'L') )
AND   cnt_other = 0
ORDER BY PN ASC;

并且，如果您使用的客户端支持它（Java支持），您可以将数组作为绑定变量传递给集合：

SELECT sms, pgm, pn, team, num
FROM   (
  SELECT a.*,
         COUNT(
           DISTINCT
           CASE WHEN PGM MEMBER OF :your_list THEN PGM END
         ) OVER (PARTITION BY PN) AS cnt_match,
         COUNT(
           CASE WHEN PGM NOT MEMBER OF :your_list THEN 1 END
         ) OVER (PARTITION BY PN) AS cnt_other
  FROM   ALPHA a
  WHERE  PN   LIKE 'BB%'
  AND    NUM  = '2350'
  AND    TEAM = 'R2D2'
)
WHERE cnt_match = CARDINALITY( :your_list )
AND   cnt_other = 0
ORDER BY PN ASC;

如果您不想使用集合，则：

WITH match_values (value) AS (
  SELECT 'N' FROM DUAL UNION ALL
  SELECT 'L' FROM DUAL
)
SELECT sms, pgm, pn, team, num
FROM   (
  SELECT a.*,
         COUNT(
           DISTINCT
           CASE WHEN PGM IN (SELECT value FROM match_values) THEN PGM END
         ) OVER (PARTITION BY PN) AS cnt_match,
         COUNT(
           CASE WHEN PGM NOT IN (SELECT value FROM match_values) THEN 1 END
         ) OVER (PARTITION BY PN) AS cnt_other
  FROM   ALPHA a
  WHERE  PN   LIKE 'BB%'
  AND    NUM  = '2350'
  AND    TEAM = 'R2D2'
)
WHERE cnt_match = (SELECT COUNT(*) FROM match_values)
AND   cnt_other = 0
ORDER BY PN ASC;

fiddle

您可以创建CTE（pgm_filter）并在以后在WHERE子句中使用它.

WITH
    pgm_filter AS
        (
            Select   PN, PGM_LIST
            From     (SELECT DISTINCT PN, PGM, LISTAGG(PGM, '') WITHIN GROUP (Order By PGM) OVER(PARTITION BY PN) "PGM_LIST"
                      FROM   ALPHA a)
            Where    PGM_LIST  = 'LN'
            Group By PN, PGM_LIST      
        )

LISTAGG（）分析函数用于生成每个PN的PGM值列表。其中，条件仅筛选具有值'L'和'N'的特定组合的PN-不多不少。
WHERE子句中包含pgm_filter的主SQL：

SELECT    SMS, PGM, PN, TEAM, NUM
FROM      ALPHA
WHERE     PN IN(Select PN From pgm_filter) And
          PN   LIKE 'PP%' And
          NUM  = '2350' And
          TEAM = 'R2D2'

...这将过滤具有PGM值的'LN'组合的行沿着您需要的任何其他过滤器。
此致