PLSQL查询需求：优先获取指定学期值，无则取最大学期值

需求说明 #

需要连接application_data（下称a）与information_table（下称it）两张表查询学生数据，规则如下：

• 若学生在it中存在'2022-8'的学期记录，优先返回该条数据
• 若不存在该学期记录，则返回该学生在it中的最大学期记录

示例数据 #

|    Person A         |      Biology     |     2022-5       |                           
|    Person A         |      Biology     |     2023-1       |                        
|    Person B         |      Chemistry   |     2022-1       |                        
|    Person B         |      Psychology  |     2022-8       |                         
|    Person C         |      Mathematics |     2022-8       |                          
|    Person C         |      Statistics  |     2023-1       |

期望输出 #

|    Person A         |      Biology     |     2023-1       |                           
|    Person B         |      Psychology  |     2022-8       |                           
|    Person C         |      Mathematics |     2022-8       |

正确实现SQL #

使用窗口函数ROW_NUMBER()给每个学生的记录按规则排序，筛选出优先级最高的一条：

WITH ranked_student_records AS (
    SELECT 
        a.student_name,
        it.course,
        it.semester,
        -- 给每条记录打排名标签：指定学期优先，其余按学期降序
        ROW_NUMBER() OVER (
            PARTITION BY a.student_name  -- 按学生分组，若有唯一ID（如student_id）建议用ID替代
            ORDER BY 
                CASE WHEN it.semester = '2022-8' THEN 0 ELSE 1 END,
                it.semester DESC
        ) AS record_rank
    FROM application_data a
    JOIN information_table it ON a.student_name = it.student_name  -- 替换为实际关联字段，如student_id
)
-- 取每个学生排名第一的记录
SELECT student_name, course, semester
FROM ranked_student_records
WHERE record_rank = 1;

方案解释 #

1. CTE分组排序：通过PARTITION BY按学生分组，使用CASE语句让'2022-8'学期的记录排序列值为0（优先级最高），其他记录按学期降序排列，确保最大的学期排在前面。
2. 筛选最优记录：最后只保留每个学生排名为1的记录，正好符合“优先指定学期，无则取最大学期”的需求。

注意事项 #

• 若学生有唯一标识（如student_id），建议将PARTITION BY和JOIN的关联字段换成student_id，避免重名学生导致的数据错误。
• 学期格式'YEAR-数字月份'可以直接按字符串降序比较，因为年份在前、月份在后，字符串排序结果和实际时间顺序一致。

内容的提问来源于stack exchange，提问作者Lokelani-U