Laravel 5.8 聚合查询中如何计算统计字段差值得到未注册学生数

你遇到的错误来自三个核心问题叠加：

• SQL解析顺序限制：同一SELECT层级中定义的列别名，无法被当前块内的其他计算语句直接引用。SQL的执行流程是先处理FROM/JOIN、WHERE、GROUP BY、HAVING阶段，最后才会解析SELECT里的列定义与别名，当执行到差值计算语句时，还没有生成total_students、total_registered的内存映射，自然会触发列不存在的错误。
• 字段拼写偏差：你尝试的代码中把别名total_students错写为total_student，就算解决了别名引用规则的限制，这个拼写差异也会触发同样的报错。
• 冗余聚合逻辑：当前查询已经按班级ID做了分组，每个分组的统计结果都是单值，不需要再额外套一层SUM做聚合，这层逻辑完全多余。

方案1：聚合层直接计算差值（性能最优、兼容性最好） #

不需要引用之前定义的别名，直接复用已有的两个聚合统计逻辑做差，这是所有关系型数据库都支持的标准写法，没有兼容问题。
在原select字段列表中直接新增以下代码即可：

DB::raw('COALESCE(COUNT(s.id) - COUNT(r.id), 0) AS total_unregistered')

这个写法的统计逻辑和你已定义的两个字段完全对齐：

• COUNT(s.id) 完全匹配total_students的计算逻辑
• COUNT(r.id) 完全匹配total_registered的计算逻辑
• 保留COALESCE是为了统一空值处理逻辑，和原有字段的返回值格式保持一致，避免出现null值。

方案2：嵌套子查询引用别名计算 #

如果不想重复编写聚合逻辑，可以把原分组查询作为子查询，在外层做差值计算（外层查询可以正常引用内层定义的所有列别名）：

1. 保留原查询的所有JOIN、WHERE、GROUP BY逻辑，去掉最后的->get()，将整个查询作为基础统计层
2. 在外层查询中直接引用别名做差值计算，示例代码：

// 原统计逻辑（去掉最终的get()调用）
$baseClassStats = DB::table('my_classess AS c')
   ->leftJoin('students AS ch', function($join) use ($school) {
       $join->on('ch.id', '=', 'c.class_head_id')
           ->where('ch.school_id', '=', $school);
   })
   ->leftJoin('students AS s', function($join) use ($student) {
       $join->on('c.id', '=', 's.class_id')
           ->where('s.school_id', '=', $school);
   })
   ->leftJoin('registrations AS r', function($join) use ($school) {
       $join->on('s.id', '=', 'r.student_id')
           ->where('r.company_id', '=', $school)
           ->where('r.is_registered', '1');
   })
   ->leftJoin('registration_types AS rt', function($join) use ($school, $registrationTypeName) {
       $join->on('r.registration_type_id', '=', 'rt.id')
           ->where('rt.school_id', '=', $school)
           ->where('rt.registration_type', 'LIKE', '%'.$registrationTypeName.'%');
    })
    ->where('c.school_id', '=', $school)
    ->select(
        'c.class_name',
        DB::raw('COALESCE(CONCAT(ch.first_name, " ", ch.last_name), "None") AS class_head'),
        DB::raw('COALESCE(COUNT(r.id), "0") AS total_registered'),
        DB::raw('COALESCE(COUNT(s.id), "0") AS total_students'),
    )
    ->groupby('c.id');

// 外层查询计算未注册人数
$studentRegistrations = DB::query()
    ->fromSub($baseClassStats, 'stats')
    ->select([
        '*',
        DB::raw('(total_students - total_registered) AS total_unregistered')
    ])
    ->get();

• 原代码中对registration_types表使用了INNER JOIN，这会导致没有Core类型注册记录的班级会被完全过滤掉，如果你需要保留所有符合学校范围的班级（哪怕没有对应注册记录，也要展示全量学生数、0注册数的统计结果），需要将这个JOIN改为LEFT JOIN，不然最终统计的班级集合本身就存在偏差。
• 因为LEFT JOIN关联的注册记录为空时，r.id的统计结果会自动被COUNT忽略，差值计算的结果不会出现逻辑错误。

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