Laravel中基于Result模型按科目统计及格/不及格人数的实现需求

需求说明 #

数据库存储的数据示例：

NAME    SUBJECT        RESULT 
 A        HX            PASS  
 B        HX            FAIL
 C        DX            PASS
 D        DX            PASS
 E        MR            FAIL

期望在Blade模板中展示的统计表格：

SUBJECT   PASS    FAIL
  HX       1       1
  DX       2       0
  MR       0       1 

解决方案 #

1. 控制器编写统计逻辑

在对应控制器方法中，通过Eloquent分组聚合查询，统计各科目及格（PASS）、不及格（FAIL）的人数，用COALESCE确保无对应结果时显示0：

use App\Models\Result;

public function showResultStats()
{
    $stats = Result::select(
        'subject',
        \DB::raw('COALESCE(SUM(CASE WHEN result = "PASS" THEN 1 ELSE 0 END), 0) as pass_count'),
        \DB::raw('COALESCE(SUM(CASE WHEN result = "FAIL" THEN 1 ELSE 0 END), 0) as fail_count')
    )
    ->groupBy('subject')
    ->get();

    return view('result-stats', compact('stats'));
}

也可以用子查询写法，逻辑更直观：

$stats = Result::groupBy('subject')
    ->select('subject')
    ->selectRaw('(SELECT COUNT(*) FROM results WHERE subject = results.subject AND result = "PASS") as pass_count')
    ->selectRaw('(SELECT COUNT(*) FROM results WHERE subject = results.subject AND result = "FAIL") as fail_count')
    ->get();

2. Blade模板渲染表格

创建resources/views/result-stats.blade.php，循环统计数据生成表格：

<table border="1">
    <thead>
        <tr>
            <th>SUBJECT</th>
            <th>PASS</th>
            <th>FAIL</th>
        </tr>
    </thead>
    <tbody>
        @foreach($stats as $stat)
        <tr>
            <td>{{ $stat->subject }}</td>
            <td>{{ $stat->pass_count }}</td>
            <td>{{ $stat->fail_count }}</td>
        </tr>
        @endforeach
    </tbody>
</table>

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