求助：如何根据学生类型字段返回对应编码的邮箱地址（SQL实现）

需求说明 #

需根据学生类型（字段SGBSTDN.SGBSTDN_COLL_CODE_1，别名Coll_Code）返回对应类型的邮箱地址：

• 学生类型为YC时，返回邮箱编码GOREMAL.GOREMAL_Emal_Code为YALE的地址
• 学生类型为SU时，返回邮箱编码为HAPP的地址

原SQL代码 #

select distinct
             SPRIDEN.SPRIDEN_ID as SID, 
             SGBSTDN.SGBSTDN_COLL_CODE_1 Coll_Code,
             SPRIDEN.SPRIDEN_LAST_NAME as LNAME,
             SPRIDEN.SPRIDEN_FIRST_NAME as FNAME,
             SFRSTCR.SFRSTCR_PTRM_CODE PoT,
             SSBSECT.SSBSECT_PTRM_START_DATE Session_Start, 
             SSBSECT.SSBSECT_PTRM_END_DATE Session_End,
              SLRRASG.SLRRASG_ASCD_CODE Housing,
              sum(tbraccd.tbraccd_balance) Balance,
              goremal.goremal_email_address Email
--              listagg( distinct goremal.goremal_email_address, ',') within group (order by goremal.goremal_email_address) as Emails
--              
            from 
              SATURN.SFRSTCR
              join SATURN.SGBSTDN
               on SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
                 and SGBSTDN.SGBSTDN_TERM_CODE_EFF = SFRSTCR.SFRSTCR_TERM_CODE
              join SATURN.SPRIDEN
                on SPRIDEN.SPRIDEN_PIDM = SGBSTDN.SGBSTDN_PIDM
               join SATURN.SSBSECT
              on SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
                and SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
              join TAISMGR.TBRACCD
                on SGBSTDN.SGBSTDN_PIDM = TAISMGR.TBRACCD.TBRACCD_PIDM
                 --and SGBSTDN.SGBSTDN_TERM_CODE_EFF = TAISMGR.TBRACCD.TBRACCD_TERM_CODE
             left join general.goremal
                on SPRIDEN.SPRIDEN_PIDM = "GENERAL".GOREMAL.GOREMAL_PIDM
                      and GOREMAL.GOREMAL_Emal_Code  in ('YALE', 'HAPP')
              left join SATURN.SLRRASG
              on SGBSTDN.SGBSTDN_PIDM = SLRRASG.SLRRASG_PIDM
                 and SGBSTDN.SGBSTDN_TERM_CODE_EFF = SLRRASG.SLRRASG_TERM_CODE
                 and  SLRRASG_BEGIN_DATE > '01-July-2023'
             
            where 
                
                SGBSTDN.SGBSTDN_TERM_CODE_EFF = '202302' 
               and TAISMGR.TBRACCD.TBRACCD_TERM_CODE = '202302'
               --and SFRSTCR.SFRSTCR_PTRM_CODE = 'H8'
              --and SFRSTCR.SFRSTCR_PTRM_CODE in ('H10', 'H4B', 'H5A', 'H5B', 'HE3', 'H6','HND','HYW')
              and SFRSTCR.SFRSTCR_PTRM_CODE in ('H4B', 'HE3', 'HND', 'H5B')
                and SFRSTCR.SFRSTCR_RSTS_CODE in ('RE', 'RW')
               -- and SGBSTDN.SGBSTDN_COLL_CODE_1 = 'SU'
               
                --and SPRIDEN.SPRIDEN_ID = '925858581'
                and SPRIDEN.SPRIDEN_CHANGE_IND is null 
           
              and ( SGBSTDN.SGBSTDN_COLL_CODE_1 = 'YC' and goremal.goremal_email_address in ('Yale') or SGBSTDN.SGBSTDN_COLL_CODE_1 = 'SU' and goremal.goremal_email_address in ('HAPP') )
                 
            group by
              goremal.goremal_email_address,
               SFRSTCR.SFRSTCR_CRN,
               SPRIDEN.SPRIDEN_ID,
              SPRIDEN.SPRIDEN_LAST_NAME, 
              SPRIDEN.SPRIDEN_FIRST_NAME,
              SFRSTCR.SFRSTCR_PTRM_CODE,
              SSBSECT.SSBSECT_PTRM_START_DATE, 
             SSBSECT.SSBSECT_PTRM_END_DATE,
               SLRRASG.SLRRASG_ASCD_CODE,
             SGBSTDN.SGBSTDN_COLL_CODE_1
             
            order by SPRIDEN.SPRIDEN_LAST_NAME

修改方案 #

原SQL存在两个核心问题：一是WHERE条件错误地用邮箱地址匹配编码规则；二是JOIN条件未关联学生类型与邮箱编码，可能拉取无关邮箱。以下是修正后的实现：

修正后的SQL代码

select
             SPRIDEN.SPRIDEN_ID as SID, 
             SGBSTDN.SGBSTDN_COLL_CODE_1 Coll_Code,
             SPRIDEN.SPRIDEN_LAST_NAME as LNAME,
             SPRIDEN.SPRIDEN_FIRST_NAME as FNAME,
             SFRSTCR.SFRSTCR_PTRM_CODE PoT,
             SSBSECT.SSBSECT_PTRM_START_DATE Session_Start, 
             SSBSECT.SSBSECT_PTRM_END_DATE Session_End,
             SLRRASG.SLRRASG_ASCD_CODE Housing,
             sum(tbraccd.tbraccd_balance) Balance,
             -- 用聚合函数确保每个学生仅返回对应类型的邮箱
             max(goremal.goremal_email_address) Email
from 
              SATURN.SFRSTCR
              join SATURN.SGBSTDN
               on SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
                 and SGBSTDN.SGBSTDN_TERM_CODE_EFF = SFRSTCR.SFRSTCR_TERM_CODE
              join SATURN.SPRIDEN
                on SPRIDEN.SPRIDEN_PIDM = SGBSTDN.SGBSTDN_PIDM
               join SATURN.SSBSECT
              on SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
                and SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
              join TAISMGR.TBRACCD
                on SGBSTDN.SGBSTDN_PIDM = TAISMGR.TBRACCD.TBRACCD_PIDM
             -- 调整JOIN条件，根据学生类型匹配对应邮箱编码
             left join general.goremal
                on SPRIDEN.SPRIDEN_PIDM = GENERAL.GOREMAL.GOREMAL_PIDM
                and (
                    (SGBSTDN.SGBSTDN_COLL_CODE_1 = 'YC' and GOREMAL.GOREMAL_Emal_Code = 'YALE')
                    or (SGBSTDN.SGBSTDN_COLL_CODE_1 = 'SU' and GOREMAL.GOREMAL_Emal_Code = 'HAPP')
                )
              left join SATURN.SLRRASG
              on SGBSTDN.SGBSTDN_PIDM = SLRRASG.SLRRASG_PIDM
                 and SGBSTDN.SGBSTDN_TERM_CODE_EFF = SLRRASG.SLRRASG_TERM_CODE
                 and SLRRASG_BEGIN_DATE > '01-July-2023'
             
where 
                SGBSTDN.SGBSTDN_TERM_CODE_EFF = '202302' 
               and TAISMGR.TBRACCD.TBRACCD_TERM_CODE = '202302'
              and SFRSTCR.SFRSTCR_PTRM_CODE in ('H4B', 'HE3', 'HND', 'H5B')
                and SFRSTCR.SFRSTCR_RSTS_CODE in ('RE', 'RW')
                and SPRIDEN.SPRIDEN_CHANGE_IND is null 
              -- 过滤仅保留目标学生类型
              and SGBSTDN.SGBSTDN_COLL_CODE_1 in ('YC', 'SU')
group by
               SFRSTCR.SFRSTCR_CRN,
               SPRIDEN.SPRIDEN_ID,
              SPRIDEN.SPRIDEN_LAST_NAME, 
              SPRIDEN.SPRIDEN_FIRST_NAME,
              SFRSTCR.SFRSTCR_PTRM_CODE,
              SSBSECT.SSBSECT_PTRM_START_DATE, 
             SSBSECT.SSBSECT_PTRM_END_DATE,
               SLRRASG.SLRRASG_ASCD_CODE,
             SGBSTDN.SGBSTDN_COLL_CODE_1
             
order by SPRIDEN.SPRIDEN_LAST_NAME

关键修改点

1. 修正JOIN条件：将学生类型与邮箱编码的关联逻辑移至GOREMAL表的连接条件中，确保仅拉取对应类型的邮箱，避免无关数据。
2. 调整SELECT与GROUP BY：用max(goremal_email_address)聚合邮箱字段，并从GROUP BY中移除该字段，避免因邮箱导致的重复行，保证每个学生仅返回一条记录。
3. 修正WHERE条件：移除原错误的邮箱地址判断，改为直接过滤目标学生类型（YC/SU），逻辑更清晰。

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