Laravel 9如何用查询构造器实现含SELECT子句子查询的MySQL查询

问题分析 #

你之前的代码错误在于手动拼接子查询的SQL字符串（使用toSql()），这会导致参数无法正确绑定，同时selectRaw()结合数组的方式也不适合处理子查询实例，最终引发语法错误。下面提供两种正确的实现方式，其中第二种是性能更优的优化方案。

方法一：直接使用查询构造器实例作为子查询字段 #

Laravel允许将查询构造器实例直接作为select()方法的参数，框架会自动处理子查询的生成和参数绑定，避免手动拼接的问题：

return DB::table('rates')
    ->select(
        DB::raw('COUNT(id) as total'),
        DB::raw('(SUM(score) / COUNT(id)) as average'),
        // 子查询实例直接作为字段，用as()指定别名
        DB::table('rates')
            ->selectRaw('COUNT(id)')
            ->where('book_id', $bookId)
            ->where('score', '>=', 1)
            ->where('score', '<', 2)
            ->as('count_1'),
        DB::table('rates')
            ->selectRaw('COUNT(id)')
            ->where('book_id', $bookId)
            ->where('score', '>=', 2)
            ->where('score', '<', 3)
            ->as('count_2'),
        DB::table('rates')
            ->selectRaw('COUNT(id)')
            ->where('book_id', $bookId)
            ->where('score', '>=', 3)
            ->where('score', '<', 4)
            ->as('count_3'),
        DB::table('rates')
            ->selectRaw('COUNT(id)')
            ->where('book_id', $bookId)
            ->where('score', '>=', 4)
            ->where('score', '<', 5)
            ->as('count_4'),
        DB::table('rates')
            ->selectRaw('COUNT(id)')
            ->where('book_id', $bookId)
            ->where('score', 5)
            ->as('count_5')
    )
    ->where('book_id', $bookId)
    ->first(); // 聚合查询用first()获取单条结果更合理

方法二：使用条件聚合优化（推荐） #

原查询多次扫描同一张表，性能较低。通过CASE WHEN结合聚合函数，只需一次表扫描即可完成所有统计，效率更高：

return DB::table('rates')
    ->select(
        DB::raw('COUNT(id) as total'),
        DB::raw('AVG(score) as average'), // AVG()等价于SUM(score)/COUNT(id)，语法更简洁
        DB::raw('COUNT(CASE WHEN score >= 1 AND score < 2 THEN 1 END) as count_1'),
        DB::raw('COUNT(CASE WHEN score >= 2 AND score < 3 THEN 1 END) as count_2'),
        DB::raw('COUNT(CASE WHEN score >= 3 AND score < 4 THEN 1 END) as count_3'),
        DB::raw('COUNT(CASE WHEN score >= 4 AND score < 5 THEN 1 END) as count_4'),
        DB::raw('COUNT(CASE WHEN score = 5 THEN 1 END) as count_5')
    )
    ->where('book_id', $bookId)
    ->first();

原理说明 #

• CASE WHEN会在满足条件时返回1，不满足时返回NULL
• COUNT()函数会自动忽略NULL值，从而统计出符合条件的记录数

内容的提问来源于stack exchange，提问作者Albert Lazaro de Lara