如何编写含多WHERE条件的SQL查询？求技术指导

需求说明 #

获取新学年9月在校学生列表，需遵循以下规则：

• 排除当前13年级确定离校的学生
• 排除其他年级标记为"Definite Leaver"且离校日期为2024-08-31的学生
• 纳入已确认录取（状态为"6. Accepted"）、2024年Michaelmas学期入学的新生

涉及数据表 #

1. dbo.TblPupilManagementPupils #

用到的列及说明：

• txtSchoolID：唯一学生标识
• txtSurname：学生姓氏
• txtForename：学生名字
• intNCYear：年级
• txtAcademicHouse：学院/社屋
• txtForm：班级
• intSystemStatus：系统状态（-1=已离校，0=未入学，1=在读）
• intEnrolmentSchoolYear：入学学年
• txtEnrolmentTerm：入学学期
• txtAdmissionsStatus：录取状态（需为"6. Accepted"）
• txtLeavingDate：离校日期

2. dbo.TblPupilManagementCustomFieldValue #

用到的列及说明：

• txtSchoolID：关联学生标识
• txtValue：自定义字段值（需筛选"Definite Leaver"）

现有单独查询 #

1. 当前在读且9月留校的学生 #

SELECT ppl.txtSurname, ppl.txtForename, ppl.intNCYear, ppl.txtAcademicHouse, ppl.txtForm
FROM TblPupilManagementPupils AS PPL
WHERE ppl.intSystemStatus = '1' AND ppl.intNCYear < 13 
ORDER BY ppl.txtSurname, ppl.txtForename

2. 当前在读但已告知将离校的学生 #

SELECT PPL.txtSurname, PPL.txtForename, PPL.txtLeavingDate, TblPupilManagementCustomFieldValue.txtValue AS LeavingType
FROM TblPupilManagementPupils AS PPL 
LEFT OUTER JOIN TblPupilManagementCustomFieldValue 
    ON PPL.txtSchoolID = TblPupilManagementCustomFieldValue.txtSchoolId
WHERE (TblPupilManagementCustomFieldValue.txtValue = 'Definite Leaver') 
  AND (CONVERT(VARCHAR(25), PPL.txtLeavingDate, 126) LIKE '2024-08-31%')

3. 新学年即将入学的学生 #

SELECT ppl.txtSurname, ppl.txtforename, ppl.intEnrolmentSchoolYear, ppl.txtEnrolmentTerm, ppl.txtAdmissionsStatus
FROM dbo.TblPupilManagementPupils AS ppl
WHERE ppl.intsystemstatus = 0 
  AND ppl.intEnrolmentSchoolYear = '2024' 
  AND ppl.txtAdmissionsStatus = '6. Accepted' 
  AND ppl.txtEnrolmentTerm = 'Michaelmas'

合并查询解决方案 #

通过UNION ALL合并在读留校生和新生，同时用子查询精准排除离校学生，统一输出列保证结果结构一致：

-- 合并符合条件的在读留校生和新入学学生，排除已确认离校的学生
SELECT 
    txtSurname, 
    txtForename, 
    intNCYear, 
    txtAcademicHouse, 
    txtForm,
    '在读留校' AS StudentType
FROM TblPupilManagementPupils AS ppl
WHERE 
    intSystemStatus = 1
    -- 排除13年级学生
    AND intNCYear < 13
    -- 排除已标记为确定离校且8月31日离校的学生
    AND txtSchoolID NOT IN (
        SELECT cf.txtSchoolID
        FROM TblPupilManagementCustomFieldValue AS cf
        JOIN TblPupilManagementPupils AS p 
            ON cf.txtSchoolID = p.txtSchoolID
        WHERE cf.txtValue = 'Definite Leaver'
          AND CONVERT(date, p.txtLeavingDate) = '2024-08-31'
    )

UNION ALL

SELECT 
    txtSurname, 
    txtForename, 
    -- 若新生有预分配年级可替换为对应字段，否则留空
    NULL AS intNCYear, 
    NULL AS txtAcademicHouse, 
    NULL AS txtForm,
    '新入学' AS StudentType
FROM dbo.TblPupilManagementPupils AS ppl
WHERE 
    intsystemstatus = 0 
    AND intEnrolmentSchoolYear = '2024' 
    AND txtAdmissionsStatus = '6. Accepted' 
    AND txtEnrolmentTerm = 'Michaelmas'

ORDER BY txtSurname, txtForename;

逻辑说明 #

1. 在读留校生筛选：先取当前在读且年级低于13的学生，再通过NOT IN排除已标记为"Definite Leaver"且离校日期为2024-08-31的学生，确保这些学生不会出现在9月在校列表中。
2. 新生筛选：直接取已确认录取、2024年Michaelmas学期入学的未入学学生，补充到结果集。
3. 合并结果：用UNION ALL合并两个数据集（在读生和新生的intSystemStatus不同，不会重复），最后按姓氏、名字排序。
4. 优化点：将日期匹配从LIKE改为CONVERT(date, txtLeavingDate) = '2024-08-31'，更高效且避免格式匹配误差。

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