使用Knex.js实现左连接table1关联去重取最大id的table2并保留无匹配记录

现有查询无法返回全量table1记录的原因如下：

• 关联逻辑错配：用于取最大id的派生表kst仅通过std_id和原table2关联，未加上t2.id = kst.id的精确匹配条件，没有真正锁定每个std_id下的最大id条目，关联结果存在数据错配。
• 软删过滤缺失：全查询仅过滤了t1.deleted_at is null，既没有在计算最大id的子查询中过滤table2.deleted_at is null，也没有在关联环节排除table2的软删记录，会把已删除的无效成绩数据纳入统计。
• 连接顺序不合理：先关联全量table2、再叠加最大id判断的结构，一旦后续加入任何针对table2字段的过滤条件，就会把左连接生成的空匹配行（即无对应成绩的学生记录）意外过滤，最终出现“仅返回有匹配table2记录的table1条目”的漏行问题。

正确的执行逻辑是先将table2中符合软删要求、每个std_id对应最大id的有效条目单独筛选，再与全量有效table1记录做左连接，保证无匹配成绩的学生记录也能完整返回：

// 先计算table2中每个有效std_id对应的最大id
const maxValidT2Subquery = knex('table2')
  .whereNull('deleted_at') // 排除已软删的table2记录
  .select('std_id')
  .max('id as max_t2_id')
  .groupBy('std_id');

// 主查询
const query = knex
  .select([
    't1.id as t1id',
    't1.std_name as name',
    't2.score as score'
  ])
  .from('table1 as t1')
  .whereNull('t1.deleted_at') // 仅保留未软删的table1记录
  .leftJoin(
    // 子查询精准获取最大id对应的真实成绩
    knex('table2 as t2_source')
      .innerJoin(maxValidT2Subquery.as('max_info'), function() {
        // 精确匹配最大id对应的条目，避免关联错行
        this.on('t2_source.id', '=', 'max_info.max_t2_id')
            .andOn('t2_source.std_id', '=', 'max_info.std_id');
      })
      .as('t2'),
    't2.std_id',
    '=',
    't1.id'
  )
  .orderBy('t1.id');

基于提供的测试数据，查询输出完全符合预期：

同时满足以下要求：

• 所有table1、table2的查询条目自动排除deleted_at不为null的软删数据
• 同个std_id重复的table2记录，仅取id最大的有效条目
• 无对应有效table2记录的table1条目，score字段自动返回null，不会出现漏行

内容的提问来源于stack exchange，提问作者Raiyan Rashid Prodhan