甲骨文-不存在的地方
甲骨文-不存在的地方
提问于 2022-05-26 14:54:09
我正试着给出一份参加某些课程的人的名单。这些课程是由一个名为CRN的领域确定的。如果一个人没有参加某些课程(CRN)，我需要返回该列表。使用此查询中不存在的位置当前不返回任何人，而且dbase中有数百人属于此类别。
请看一下qry，让我知道什么是不正确的。
谢谢
select distinct
SPRIDEN.SPRIDEN_ID
from
SATURN.SPRIDEN
where not exists
(select distinct
SPRIDEN.SPRIDEN_ID SID
from
SATURN.SGBSTDN
join SATURN.SFRSTCR
on SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
and SGBSTDN.SGBSTDN_TERM_CODE_EFF = SFRSTCR.SFRSTCR_TERM_CODE
join SATURN.SPRIDEN
on SGBSTDN.SGBSTDN_PIDM = SPRIDEN.SPRIDEN_PIDM
join SATURN.SSBSECT
on SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
and SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
join SATURN.SCBCRSE
on SSBSECT.SSBSECT_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
and SSBSECT.SSBSECT_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
where
SPRIDEN.SPRIDEN_ID like '9%'
and SFRSTCR.SFRSTCR_TERM_CODE = '202202'
and SPRIDEN.SPRIDEN_CHANGE_IND is null
and SFRSTCR.SFRSTCR_RSTS_CODE in ('RE', 'RW')
and SSBSECT.SSBSECT_SSTS_CODE in ('A', 'E', 'O')
and SSBSECT.SSBSECT_PTRM_CODE in ('H5A', 'H10') --,'H5B', 'H10', 'HSL', 'HND', ,'HE3', 'H8')
--Campus Course CRNs below
and SFRSTCR.SFRSTCR_CRN in ('30007',
'30009',
'30011',
'30012',
'30013',
'30023',
'30024',
'30027',
'30028',
'30029',
'30030',
'30031',
'30032',
'30033',
'30034',
'30035',
'30036',
'30039',
'30040',
'30041',
'30042',
'30043',
'30046',
'30048',
'30049',
'30057',
'30059',
'30060',
'30064',
'30066',
'30067',
'30068',
'30069',
'30070',
'30074',
'30075',
'30081',
'30082',
'30083',
'30084',
'30085',
'30087',
'30093',
'30094',
'30095',
'30096',
'30097',
'30098',
'30099',
'30100',
'30101',
'30110',
'30111',
'30115',
'30116',
'30117',
'30121',
'30122',
'30123',
'30125',
'30130',
'30135',
'30136',
'30137',
'30138',
'30139',
'30190',
'30191',
'30192',
'30193',
'30194',
'30197',
'30200',
'30201',
'30202',
'30243',
'30266',
'30269',
'30270',
'30271',
'30272',
'30273',
'30274',
'30283',
'30284',
'30285',
'30295',
'30296',
'30300',
'30303',
'30304',
'30307',
'30308',
'30309',
'30310',
'30311',
'30314',
'30317',
'30318',
'30319',
'30320',
'30321',
'30323',
'30324',
'30325',
'30326',
'30327',
'30339',
'30340',
'30341',
'30343',
'30344',
'30345',
'30346',
'30347',
'30348',
'30349',
'30350',
'30351',
'30352',
'30353',
'30354',
'30355',
'30356',
'30366',
'30367',
'30368',
'30369',
'30372',
'30373',
'30374')
and SCBCRSE.SCBCRSE_EFF_TERM = (select max(scbcrse_eff_term) from saturn.scbcrse xppd
where
xppd.scbcrse_subj_code = scbcrse.scbcrse_subj_code
and
xppd.scbcrse_crse_numb = scbcrse.scbcrse_crse_numb and xppd.scbcrse_eff_term <= ssbsect_term_code)
)回答 2
Stack Overflow用户
发布于 2022-05-26 15:07:24
尝试使用：WHERE <field> NOT IN (SELECT <field> FROM <table>)而不是不存在的地方。
select distinct
SPRIDEN.SPRIDEN_ID
from
SATURN.SPRIDEN
where SPRIDEN.SPRIDEN_ID not in
(select distinct
SPRIDEN.SPRIDEN_ID SID
from
SATURN.SGBSTDN
join SATURN.SFRSTCR
on SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
and SGBSTDN.SGBSTDN_TERM_CODE_EFF = SFRSTCR.SFRSTCR_TERM_CODE
join SATURN.SPRIDEN
on SGBSTDN.SGBSTDN_PIDM = SPRIDEN.SPRIDEN_PIDM
join SATURN.SSBSECT
on SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
and SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
join SATURN.SCBCRSE
on SSBSECT.SSBSECT_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
and SSBSECT.SSBSECT_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
where
SPRIDEN.SPRIDEN_ID like '9%'
and SFRSTCR.SFRSTCR_TERM_CODE = '202202'
and SPRIDEN.SPRIDEN_CHANGE_IND is null
and SFRSTCR.SFRSTCR_RSTS_CODE in ('RE', 'RW')
and SSBSECT.SSBSECT_SSTS_CODE in ('A', 'E', 'O')
and SSBSECT.SSBSECT_PTRM_CODE in ('H5A', 'H10') --,'H5B', 'H10', 'HSL', 'HND', ,'HE3', 'H8')
--Campus Course CRNs below
and SFRSTCR.SFRSTCR_CRN in ('30007',
'30009', '30011', '30012', '30013', '30023', '30024', '30027',
'30028', '30029', '30030', '30031', '30032', '30033', '30034',
'30035', '30036', '30039', '30040', '30041', '30042', '30043',
'30046', '30048', '30049', '30057', '30059', '30060', '30064',
'30066', '30067', '30068', '30069', '30070', '30074', '30075',
'30081', '30082', '30083', '30084', '30085', '30087', '30093',
'30094', '30095', '30096', '30097', '30098', '30099', '30100',
'30101', '30110', '30111', '30115', '30116', '30117', '30121',
'30122', '30123', '30125', '30130', '30135', '30136', '30137',
'30138', '30139', '30190', '30191', '30192', '30193', '30194',
'30197', '30200', '30201', '30202', '30243', '30266', '30269',
'30270', '30271', '30272', '30273', '30274', '30283', '30284',
'30285', '30295', '30296', '30300', '30303', '30304', '30307',
'30308', '30309', '30310', '30311', '30314', '30317', '30318',
'30319', '30320', '30321', '30323', '30324', '30325', '30326',
'30327', '30339', '30340', '30341', '30343', '30344', '30345',
'30346', '30347', '30348', '30349', '30350', '30351', '30352',
'30353', '30354', '30355', '30356', '30366', '30367', '30368',
'30369', '30372', '30373', '30374')
and SCBCRSE.SCBCRSE_EFF_TERM = (select max(scbcrse_eff_term) from saturn.scbcrse xppd
where
xppd.scbcrse_subj_code = scbcrse.scbcrse_subj_code
and
xppd.scbcrse_crse_numb = scbcrse.scbcrse_crse_numb and xppd.scbcrse_eff_term <= ssbsect_term_code))Stack Overflow用户
发布于 2022-05-26 18:27:44
如果不深入了解数据模型，就很难对这个问题给出一个坚实的答案。以下答案假设此查询将返回所有感兴趣的学生和课程的列表，包括只参加在线课程、校园课程和这两类课程的学生：
SELECT
SPRIDEN.SPRIDEN_ID AS S_ID
,SFRSTCR.SFRSTCR_CRN AS COURSE_NBR
FROM
SATURN.SGBSTDN
JOIN SATURN.SFRSTCR
ON SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
AND SGBSTDN.SGBSTDN_TERM_CODE_EFF = SFRSTCR.SFRSTCR_TERM_CODE
JOIN SATURN.SSBSECT
ON SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
AND SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
JOIN SATURN.SCBCRSE
ON SSBSECT.SSBSECT_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
AND SSBSECT.SSBSECT_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
JOIN SATURN.SPRIDEN
ON SGBSTDN.SGBSTDN_PIDM = SPRIDEN.SPRIDEN_PIDM
WHERE
SPRIDEN.SPRIDEN_ID LIKE '9%'
AND SFRSTCR.SFRSTCR_TERM_CODE = '202202'
AND SPRIDEN.SPRIDEN_CHANGE_IND IS NULL
AND SFRSTCR.SFRSTCR_RSTS_CODE IN ('RE', 'RW')
AND SSBSECT.SSBSECT_SSTS_CODE IN ('A', 'E', 'O')
AND SSBSECT.SSBSECT_PTRM_CODE IN ('H5A', 'H10')
AND SCBCRSE.SCBCRSE_EFF_TERM = (
SELECT MAX(XPPD.SCBCRSE_EFF_TERM)
FROM SATURN.SCBCRSE XPPD
WHERE XPPD.SCBCRSE_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
AND XPPD.SCBCRSE_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
AND XPPD.SCBCRSE_EFF_TERM <= SSBSECT_TERM_CODE
)如果这个假设是不正确的，那么这个答案的其余部分就是胡说八道。
通过阅读OP的评论，我们的目标似乎是得到一份只参加在线课程的学生名单。(在原文中，我一点也不清楚。)这意味着，鉴于所有学生和课程的范围，排除任何在他们的时间表中至少有一个校园课程。
因此，创建一个范围内所有学生的CTE，然后查看该CTE，并排除任何与参加校园课程的学生相应的in。
WITH STUDENT_COURSE AS (
SELECT
SPRIDEN.SPRIDEN_ID AS S_ID
,SFRSTCR.SFRSTCR_CRN AS COURSE_NBR
FROM
SATURN.SGBSTDN
JOIN SATURN.SFRSTCR
ON SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
AND SGBSTDN.SGBSTDN_TERM_CODE_EFF = SFRSTCR.SFRSTCR_TERM_CODE
JOIN SATURN.SSBSECT
ON SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
AND SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
JOIN SATURN.SCBCRSE
ON SSBSECT.SSBSECT_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
AND SSBSECT.SSBSECT_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
JOIN SATURN.SPRIDEN
ON SGBSTDN.SGBSTDN_PIDM = SPRIDEN.SPRIDEN_PIDM
WHERE
SPRIDEN.SPRIDEN_ID LIKE '9%'
AND SFRSTCR.SFRSTCR_TERM_CODE = '202202'
AND SPRIDEN.SPRIDEN_CHANGE_IND IS NULL
AND SFRSTCR.SFRSTCR_RSTS_CODE IN ('RE', 'RW')
AND SSBSECT.SSBSECT_SSTS_CODE IN ('A', 'E', 'O')
AND SSBSECT.SSBSECT_PTRM_CODE IN ('H5A', 'H10')
AND SCBCRSE.SCBCRSE_EFF_TERM = (
SELECT MAX(XPPD.SCBCRSE_EFF_TERM)
FROM SATURN.SCBCRSE XPPD
WHERE XPPD.SCBCRSE_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
AND XPPD.SCBCRSE_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
AND XPPD.SCBCRSE_EFF_TERM <= SSBSECT_TERM_CODE
)
)
SELECT
SC1.S_ID
FROM
STUDENT_COURSE SC1
WHERE
SC1.S_ID NOT IN ( SELECT SC2.S_ID
FROM STUDENT_COURSE SC2
WHERE SC2.COURSE_NBR IN
--Campus Course CRNs below
(
'30007', '30009', '30011', '30012', '30013', '30023', '30024'
,'30027', '30028', '30029', '30030', '30031', '30032', '30033'
,'30034', '30035', '30036', '30039', '30040', '30041', '30042'
,'30043', '30046', '30048', '30049', '30057', '30059', '30060'
,'30064', '30066', '30067', '30068', '30069', '30070', '30074'
,'30075', '30081', '30082', '30083', '30084', '30085', '30087'
,'30093', '30094', '30095', '30096', '30097', '30098', '30099'
,'30100', '30101', '30110', '30111', '30115', '30116', '30117'
,'30121', '30122', '30123', '30125', '30130', '30135', '30136'
,'30137', '30138', '30139', '30190', '30191', '30192', '30193'
,'30194', '30197', '30200', '30201', '30202', '30243', '30266'
,'30269', '30270', '30271', '30272', '30273', '30274', '30283'
,'30284', '30285', '30295', '30296', '30300', '30303', '30304'
,'30307', '30308', '30309', '30310', '30311', '30314', '30317'
,'30318', '30319', '30320', '30321', '30323', '30324', '30325'
,'30326', '30327', '30339', '30340', '30341', '30343', '30344'
,'30345', '30346', '30347', '30348', '30349', '30350', '30351'
,'30352', '30353', '30354', '30355', '30356', '30366', '30367'
,'30368', '30369', '30372', '30373', '30374'
)
GROUP BY
SC1.S_ID
;上面的答案是未经测试的，可能包含一些琐碎的错误，但是逻辑应该是合理的。可能有(而且可能是)更好的方法来做到这一点，但是如果不理解附加联接的含义，优化就很困难。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72393810
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号