如何用Eloquent或原生查询从attendance表获取年度月度汇总数据

嘿，我来帮你搞定这个2017年度报告的查询需求～下面分别用Eloquent ORM和原生SQL两种方式实现，你可以根据自己的习惯选择：

假设你的Eloquent模型名为Attendance，可以通过分组聚合来获取每个月的统计数据：

use Illuminate\Support\Facades\DB;

$annualReport = Attendance::where('year', '2017')
    ->select([
        'month',
        DB::raw('SUM(students) as total_students'),
        DB::raw('SUM(books_borrowed) as total_books_borrowed'),
        DB::raw('SUM(fiction_english) as total_fiction_english'),
        DB::raw('SUM(fiction_swahili) as total_fiction_swahili'),
        DB::raw('SUM(chem) as total_chem'),
        DB::raw('SUM(bio) as total_bio'),
        DB::raw('SUM(geo) as total_geo'),
        DB::raw('SUM(math) as total_math'),
        DB::raw('SUM(civ) as total_civ'),
        DB::raw('SUM(kis) as total_kis'),
        DB::raw('SUM(his) as total_his'),
        DB::raw('SUM(eng) as total_eng'),
    ])
    ->groupBy('month')
    ->orderBy('month') // 可选：按月份顺序排序，若month是英文名称可改用orderByRaw调整排序逻辑
    ->get();

代码说明： #

• where('year', '2017')：筛选出2017年的所有记录
• DB::raw()：用于编写原生聚合函数，计算每个字段当月的总和
• groupBy('month')：确保每个月份只返回一行汇总数据
• orderBy('month')：让结果按月份顺序排列，方便报告展示。如果你的month字段是英文名称（比如"January"），可以用orderByRaw("FIELD(month, 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')")来保证正确的排序顺序。

如果你更倾向于直接写SQL语句，对应的查询如下：

SELECT
    month,
    SUM(students) AS total_students,
    SUM(books_borrowed) AS total_books_borrowed,
    SUM(fiction_english) AS total_fiction_english,
    SUM(fiction_swahili) AS total_fiction_swahili,
    SUM(chem) AS total_chem,
    SUM(bio) AS total_bio,
    SUM(geo) AS total_geo,
    SUM(math) AS total_math,
    SUM(civ) AS total_civ,
    SUM(kis) AS total_kis,
    SUM(his) AS total_his,
    SUM(eng) AS total_eng
FROM attendances
WHERE year = '2017'
GROUP BY month
ORDER BY month; -- 同样，若month是英文名称，可替换为FIELD函数排序

语句说明： #

• 核心逻辑和Eloquent一致：先筛选2017年的数据，再按month分组，用SUM()聚合每个字段的当月总数，最后按月份排序输出。

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