Left join与Inner join查询课程用户最新attempt_date返回记录不符合预期

问题根因 #

PostgreSQL默认执行降序排序时，NULL值优先级高于所有非NULL值，会排在结果集最前位置。当你改用LEFT JOIN后，只要学生存在任意一条attempt_date为NULL的提交记录，就会被DISTINCT ON优先匹配到，导致返回NULL而非最新的有效提交日期。

修改后查询（基于原有语法调整） #

select distinct on (cu.pk1, cm.course_id)
    cu.pk1,
    cm.course_id,
    a.attempt_date
from course_users cu
    inner join course_main cm on cm.pk1 = cu.crsmain_pk1
    inner join course_term ct on ct.crsmain_pk1 = cm.pk1 /* through table */
    inner join term t on t.pk1 = ct.term_pk1
    left join gradebook_grade gg on gg.course_users_pk1 = cu.pk1
    left join attempt a on a.gradebook_grade_pk1 = gg.pk1
where t.name like '%Fall 2021%'
    and cu.role = 'S'
    and cu.row_status = '0'
order by cu.pk1, cm.course_id, a.attempt_date desc nulls last;

调整说明 #

• 关联逻辑修改：将gradebook_grade、attempt两张表的关联从INNER JOIN改为LEFT JOIN，保证所有符合条件的选课学生都会被返回，不受是否有提交记录的影响
• 排序规则修正：在attempt_date desc后添加nulls last配置，指定降序排序时NULL值排在所有非NULL值之后，DISTINCT ON会优先匹配最新的非NULL提交日期，仅当学生无任何有效提交/所有提交日期都为NULL时，才返回NULL
• 补充优化：将DISTINCT ON的条件扩展为(cu.pk1, cm.course_id)，适配一个学生选修多门课程的场景，确保每个学生的每门课都返回独立记录，避免结果被误去重。

备选兼容方案（分组聚合实现，无数据库方言依赖） #

如果不需要沿用DISTINCT ON语法，使用聚合函数的写法逻辑更直观，兼容性更强：

select 
    cu.pk1,
    cm.course_id,
    max(a.attempt_date) as latest_attempt_date
from course_users cu
    inner join course_main cm on cm.pk1 = cu.crsmain_pk1
    inner join course_term ct on ct.crsmain_pk1 = cm.pk1
    inner join term t on t.pk1 = ct.term_pk1
    left join gradebook_grade gg on gg.course_users_pk1 = cu.pk1
    left join attempt a on a.gradebook_grade_pk1 = gg.pk1
where t.name like '%Fall 2021%'
    and cu.role = 'S'
    and cu.row_status = '0'
group by cu.pk1, cm.course_id;

max()函数会自动忽略NULL值，仅当该学生所有attempt_date都为NULL时才返回NULL，完全符合预期逻辑。

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