Laravel使用selectRaw构建查询时出现SQL语法错误求助

你的SQL语法错误根源在于：不能在聚合函数的字段定义中直接使用WHERE子句。WHERE是用来过滤整个查询的行数据，而要实现按条件统计聚合结果，需要使用条件聚合（结合CASE WHEN和聚合函数）。

修正后的代码 #

方式一：使用COUNT + CASE WHEN

$revs = DB::table('reviews')
    ->where('website_id', $website_id)
    ->selectRaw('
        COUNT(id) AS total_count,
        COUNT(CASE WHEN review_source_id != 1 THEN 1 END) AS third_party_reviews_count,
        COUNT(CASE WHEN review_source_id = 1 THEN 1 END) AS normal_reviews_count,
        COUNT(CASE WHEN stars = 1 THEN 1 END) AS total_1_star,
        COUNT(CASE WHEN stars = 2 THEN 1 END) AS total_2_star,
        COUNT(CASE WHEN stars = 3 THEN 1 END) AS total_3_star,
        COUNT(CASE WHEN stars = 4 THEN 1 END) AS total_4_star,
        COUNT(CASE WHEN stars = 5 THEN 1 END) AS total_5_star
    ')
    ->get();

方式二：使用SUM + CASE WHEN

$revs = DB::table('reviews')
    ->where('website_id', $website_id)
    ->selectRaw('
        COUNT(id) AS total_count,
        SUM(CASE WHEN review_source_id != 1 THEN 1 ELSE 0 END) AS third_party_reviews_count,
        SUM(CASE WHEN review_source_id = 1 THEN 1 ELSE 0 END) AS normal_reviews_count,
        SUM(CASE WHEN stars = 1 THEN 1 ELSE 0 END) AS total_1_star,
        SUM(CASE WHEN stars = 2 THEN 1 ELSE 0 END) AS total_2_star,
        SUM(CASE WHEN stars = 3 THEN 1 ELSE 0 END) AS total_3_star,
        SUM(CASE WHEN stars = 4 THEN 1 ELSE 0 END) AS total_4_star,
        SUM(CASE WHEN stars = 5 THEN 1 ELSE 0 END) AS total_5_star
    ')
    ->get();

原理说明 #

• COUNT(CASE WHEN 条件 THEN 1 END)：当条件满足时返回1（非NULL值），COUNT会统计这些非NULL的条目数；条件不满足时返回NULL，COUNT会忽略这些值。
• SUM(CASE WHEN 条件 THEN 1 ELSE 0 END)：当条件满足时累加1，不满足时累加0，最终结果就是符合条件的条目总数。两种方式效果完全一致，可根据习惯选择。

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