我试图返回正在学习某些课程的人员列表。这些课程由一个叫做crn的领域来识别。如果一个人没有参加某些课程(crn)，我需要返回该列表。在此查询中使用Where Not Exists当前不返回任何人，而数据库中确实有数百人属于此类别。
请看一下问题，告诉我哪里不对。
谢谢
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)
)
尝试使用: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))
如果没有对数据模型的深刻理解，就很难对这个问题给出可靠的答案。下面的答案假设该查询将返回感兴趣范围内的所有学生和课程的列表，包括只上在线课程的学生、只上校园课程的学生以及两种类型的课程:
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并排除与参加校园课程的学生对应的任何id。
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
;
上面的答案是未经测试的，可能包含一些小错误，然而，逻辑应该是合理的。可能有(很可能是)更好的方法来做到这一点，但是如果不理解附加连接的含义，优化是困难的。