PS Query中NOT EXISTS查询结果不符预期，请求排查问题

看起来你的NOT EXISTS逻辑没有按预期工作——明明目标值在PS_STDNT_CAR_TERM里存在，却还是出现在了主查询结果里。我帮你梳理几个最可能的原因和对应的排查/解决步骤：

1. 多表关联导致的重复行“漏网” #

你的主查询关联了PS_ACAD_PROG和PS_STDNT_AWD_PR_VW视图，如果同一个EMPLID/ACAD_CAREER/ADMIT_TERM组合在视图里对应多条记录，就可能出现：其中某条记录的NOT EXISTS条件不满足（比如视图里的某个字段和主表不匹配），导致这条行被保留，最终结果里就会出现本应被过滤的ADMIT_TERM值。

快速验证： #

针对结果里的0290值，找一个对应的EMPLID，单独运行下面的查询，看是否能返回结果：

SELECT D.STRM 
FROM PS_STDNT_CAR_TERM D 
WHERE D.EMPLID = '你的测试EMPLID' 
AND D.ACAD_CAREER = '对应ACAD_CAREER' 
AND D.INSTITUTION = '对应INSTITUTION' 
AND D.STDNT_CAR_NBR = '对应STDNT_CAR_NBR' 
AND D.STRM = '0290'

如果能返回记录，那说明问题大概率出在多表关联的重复行上。

解决方案： #

在主查询的SELECT后加上DISTINCT，避免重复行导致的错误结果：

SELECT DISTINCT A.ADMIT_TERM 
FROM PS_ACAD_PROG A, PS_STDNT_AWD_PR_VW B 
WHERE ( A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.ACAD_CAREER = A_ED.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR AND A_ED.EFFDT <= SYSDATE) 
AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.ACAD_CAREER = A_ES.ACAD_CAREER AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR AND A.EFFDT = A_ES.EFFDT) 
AND A.EMPLID = B.EMPLID 
AND A.ACAD_CAREER = B.ACAD_CAREER 
AND B.INSTITUTION = A.INSTITUTION 
AND B.STRM = A.ADMIT_TERM 
AND NOT EXISTS (SELECT D.STRM FROM PS_STDNT_CAR_TERM D WHERE D.EMPLID = A.EMPLID AND D.ACAD_CAREER = A.ACAD_CAREER AND D.INSTITUTION = A.INSTITUTION AND D.STDNT_CAR_NBR = A.STDNT_CAR_NBR AND D.STRM = A.ADMIT_TERM));

2. 最新记录获取逻辑可能存在偏差 #

你用嵌套子查询获取PS_ACAD_PROG的最新EFFDT和EFFSEQ，如果这个逻辑没有正确拿到目标记录，会导致A.ADMIT_TERM不是你预期的值，进而让NOT EXISTS的对比条件失效。

优化方案： #

改用ROW_NUMBER()窗口函数来获取最新记录，逻辑更清晰，也更容易排查：

WITH Latest_Acad_Prog AS (
    SELECT 
        EMPLID, 
        ACAD_CAREER, 
        STDNT_CAR_NBR, 
        ADMIT_TERM, 
        INSTITUTION,
        ROW_NUMBER() OVER (PARTITION BY EMPLID, ACAD_CAREER, STDNT_CAR_NBR ORDER BY EFFDT DESC, EFFSEQ DESC) AS RN
    FROM PS_ACAD_PROG
    WHERE EFFDT <= SYSDATE
)
SELECT DISTINCT L.ADMIT_TERM
FROM Latest_Acad_Prog L
JOIN PS_STDNT_AWD_PR_VW B 
  ON L.EMPLID = B.EMPLID 
  AND L.ACAD_CAREER = B.ACAD_CAREER 
  AND B.INSTITUTION = L.INSTITUTION 
  AND B.STRM = L.ADMIT_TERM
WHERE L.RN = 1
  AND NOT EXISTS (SELECT 1 
                  FROM PS_STDNT_CAR_TERM D 
                  WHERE D.EMPLID = L.EMPLID 
                    AND D.ACAD_CAREER = L.ACAD_CAREER 
                    AND D.INSTITUTION = L.INSTITUTION 
                    AND D.STDNT_CAR_NBR = L.STDNT_CAR_NBR 
                    AND D.STRM = L.ADMIT_TERM);

3. 字段匹配的细节问题 #

仔细检查NOT EXISTS子查询里的所有关联字段：

• 确认PS_STDNT_CAR_TERM里的INSTITUTION、STDNT_CAR_NBR是否和PS_ACAD_PROG的对应值完全一致（注意大小写、空格这类PeopleSoft里偶尔会出现的小问题）
• 检查PS_STDNT_AWD_PR_VW视图的定义，是否有隐藏的过滤条件，导致某些关联记录被异常包含/排除

额外排查步骤 #

先去掉PS_STDNT_AWD_PR_VW的关联，单独运行PS_ACAD_PROG+NOT EXISTS的查询，看结果是否正确。如果正确，那问题肯定出在和这个视图的关联上，进一步排查视图的关联逻辑即可。

内容的提问来源于stack exchange，提问作者Link