Oracle SQL-不存在-值列表中不存在字符串 [英] Oracle SQL - Not Exists - string does not exist in a list of values

问题描述

背景:对于SPRIDEN_id，sprhold_hldd_code可能具有多个值或一个或多个NO值.

Background: For a SPRIDEN_id, sprhold_hldd_code may have one or more of several values or NO values.

我需要选择SP.SPRIDEN_ID，SP.SPRIDEN_LAST_NAME，SP.SPRIDEN_FIRST_NAME，SR.SHRDGMR_SEQ_NO，SR.SHRDGMR_PROGRAM

I need to select SP.SPRIDEN_ID, SP.SPRIDEN_LAST_NAME, SP.SPRIDEN_FIRST_NAME, SR.SHRDGMR_SEQ_NO, SR.SHRDGMR_PROGRAM

(sh.sprhold_hldd_code ='RH')不存在的地方.

Where (sh.sprhold_hldd_code = 'RH') does not exist.

到目前为止，没有记录返回.

So far, no records are returned.

我发现，如果我在sh.sprhold_hldd_code ='Z'中放置的代码不在可能的值(例如Z)列表中，那么它将返回结果.

I have found that if I put a code that is not in the list of possible values (such as Z) in sh.sprhold_hldd_code = 'Z', then it will return results.

DATA: (column names abbreviated)
SPRIDEN_ID   SPRIDEN_LAST   SPRIDEN_FIRST  SHRDGMR_SEQ_NO   SHRDGMR_PROGRAM sh.sprhold_hldd_code
100001       Smith          Sue            1                ALHE            RH
100001       Smith          Sue            1                ALHE            AA
100001       Smith          Sue            1                ALHE            BB
100005       Conners        Tim            1                BUSN            RH
100008       Occent         Mary           1                MATH            CC
100008       Occent         Mary           1                MATH            AA

Expected Results: Mary is the only record that does NOT have the RH code.

SPRIDEN_ID   SPRIDEN_LAST_NAME   SPRIDEN_FIRST_NAME  SHRDGMR_SEQ_NO  SHRDGMR_PROGRAM
100008       Occent              Mary                1                MATH              

我没有收到任何结果.

CODE:
Select SP.SPRIDEN_ID, SP.SPRIDEN_LAST_NAME, SP.SPRIDEN_FIRST_NAME, SR.SHRDGMR_SEQ_NO,          SR.SHRDGMR_PROGRAM
from spriden SP
join SHRDGMR SR on SP.SPRIDEN_PIDM = SR.SHRDGMR_PIDM
join SPRHOLD SH on sp.spriden_pidm = sh.sprhold_pidm
where SR.SHRDGMR_DEGS_CODE = 'PN'
  and SR.SHRDGMR_TERM_CODE_GRAD >= '201489'
  and sp.spriden_change_ind is NULL
and not exists
(select sh.sprhold_pidm
from sprhold sh
where sh.sprhold_hldd_code = 'RH')

推荐答案

要获取所需的数据，我建议对having子句使用聚合:

To get the data you want, I would recommend using aggregation with a having clause:

Select SP.SPRIDEN_ID, SP.SPRIDEN_LAST_NAME, SP.SPRIDEN_FIRST_NAME, SR.SHRDGMR_SEQ_NO, 
       SR.SHRDGMR_PROGRAM
from spriden SP join
     SHRDGMR SR
     on SP.SPRIDEN_PIDM = SR.SHRDGMR_PIDM join
     SPRHOLD SH
     on sp.spriden_pidm = sh.sprhold_pidm
where SR.SHRDGMR_DEGS_CODE = 'PN' and
      SR.SHRDGMR_TERM_CODE_GRAD >= '201489' and
     sp.spriden_change_ind is NULL
group by SP.SPRIDEN_ID, SP.SPRIDEN_LAST_NAME, SP.SPRIDEN_FIRST_NAME, SR.SHRDGMR_SEQ_NO, 
         SR.SHRDGMR_PROGRAM
having sum(case when sh.sprhold_hldd_code = 'RH' then 1 else 0 end) = 0;

您的方法有两个问题.首先是子查询返回true或false并影响原始查询中的所有行.您确实想要一个相关的子查询.但是，即使您做对了，您仍将为Mary返回重复的行.这样就解决了这两个问题.

You have two problems with your approach. The first is that the subquery either returns true or false and affects all rows in the original query. You really want a correlated subquery. But, even if you got that right, you would be returning duplicate rows for Mary. This solves both those problems.

这篇关于Oracle SQL-不存在-值列表中不存在字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！