如何通过单次查询实现年度分月账务数据聚合展示？

我想实现单次查询获取某一年度内各账户的分月汇总数据，以行列转置形式展示：每行对应一个账户，每列对应1-12月，单元格为对应月份的金额求和值，按acc_no、年份、月份分组。目前只能实现单月数据查询，现有Laravel控制器、Vue页面代码及数据表结构如下：

期望展示的数据格式 #

+---------+---------+------------------------+
| Join Table                                 |
+---------+---------+---+---+-----+-----+-----+
| acc_no  | acc_name|Jan|Feb|March| ... | Dec |
+---------+---------+---+---+-----+-----+-----+
| 6220    | Sales   |   |   |     |     |     |
| 6221    | Material|   |   |     |     |     |
| 6222    | Others  |   |   |     |     |     |
+---------+---------+------------------------+

当前实现的单月数据格式 #

+---------+---------+------+
| Join Table               |
+---------+---------+------+
| acc_no  | acc_name|Jan   |
+---------+---------+------+
| 6220    | Sales   |sum   |
| 6221    | Material|data  |
| 6222    | Others  |      |
+---------+---------+------+

现有代码 #

Laravel Controller代码 #

$year = 2024; // 后续计划改成下拉选择
$january = 1; // 后续计划改成下拉选择
$january_Balance = DB::table('general_ledgers')
    ->select(
        'general_ledgers.acc_no', 
        'account_codes.account_name',
        'general_ledgers.jnl_date',
        'general_ledgers.name_supplier_desc as desciption',
        'general_ledgers.currency',
        'general_ledgers.amount',
        'general_ledgers.fx_rate',
        'general_ledgers.d_rate'
    )
    ->selectRaw("SUM(general_ledgers.amount) as total_amount")
    ->selectRaw("YEAR(general_ledgers.jnl_date) as year,MONTH(general_ledgers.jnl_date) as month")
    ->groupBy('account_codes.acc_no','year','month')
    ->whereYear('general_ledgers.jnl_date', $year)
    ->whereMonth('general_ledgers.jnl_date', $january)
    ->leftJoin('account_codes', 'account_codes.acc_no', '=', 'general_ledgers.acc_no')
    ->get();
return Inertia::render('Accounting/TrialBalance',[
    'items' =>$january_Balance
]);

Vue页面代码 #

<table class="table table-zebra ">
    <!-- head -->
    <thead>
    <tr>
        <th>Acc No.</th>
        <th>Account Name</th>
        <th>January</th>
        <th>February</th>
        <th>March</th>
        <th>April</th>
        <th>May</th>
        <th>June</th>
        <th>July</th>
        <th>August</th>
        <th>September</th>
        <th>October</th>
        <th>November</th>
        <th>December</th>
    </tr>
    </thead>
    <tbody>
        <tr v-for="item in items" :key="item.id" >
            <td>{{ item.acc_no }}</td>
            <td>{{ item.account_name }}</td>
            <td>{{ item.total_amount }}</td>
            <td>for other months</td>
        </tr>
    </tbody>
</table>

数据表结构 #

account_codes表 #

+---------+---------+------+
| acc_no  | acc_name| Desc |
+---------+---------+------+
| 6220    | Sales   | some |
| 6221    | Material| remark|
| 6222    | Others  |      |
+---------+---------+------+

general_ledgers表 #

+----+--------+-------+----------+--------+------------+
| id | acc_id | Desc  | currency | amount | jnl_date   |
+----+--------+-------+----------+--------+------------+
| 1  | 6220   | some  | JYP      | 30000  | 01/27/24   |
| 2  | 6221   | remark| PHP      | -20000 | 02/02/24   |
| 3  | 6223   |       | JYP      | 15000  | 03/05/24   |
+----+--------+-------+----------+--------+------------+

解决方案：单次查询实现行列转置 #

1. 修改Laravel查询，用条件聚合实现分月求和 #

通过CASE WHEN配合SUM实现每个月份的金额汇总，一次查询即可获取全年数据：

$year = 2024;

$annualData = DB::table('general_ledgers')
    ->select(
        'general_ledgers.acc_no',
        'account_codes.account_name'
    )
    // 1-12月的金额求和
    ->selectRaw("SUM(CASE WHEN MONTH(general_ledgers.jnl_date) = 1 THEN general_ledgers.amount ELSE 0 END) AS january")
    ->selectRaw("SUM(CASE WHEN MONTH(general_ledgers.jnl_date) = 2 THEN general_ledgers.amount ELSE 0 END) AS february")
    ->selectRaw("SUM(CASE WHEN MONTH(general_ledgers.jnl_date) = 3 THEN general_ledgers.amount ELSE 0 END) AS march")
    ->selectRaw("SUM(CASE WHEN MONTH(general_ledgers.jnl_date) = 4 THEN general_ledgers.amount ELSE 0 END) AS april")
    ->selectRaw("SUM(CASE WHEN MONTH(general_ledgers.jnl_date) = 5 THEN general_ledgers.amount ELSE 0 END) AS may")
    ->selectRaw("SUM(CASE WHEN MONTH(general_ledgers.jnl_date) = 6 THEN general_ledgers.amount ELSE 0 END) AS june")
    ->selectRaw("SUM(CASE WHEN MONTH(general_ledgers.jnl_date) = 7 THEN general_ledgers.amount ELSE 0 END) AS july")
    ->selectRaw("SUM(CASE WHEN MONTH(general_ledgers.jnl_date) = 8 THEN general_ledgers.amount ELSE 0 END) AS august")
    ->selectRaw("SUM(CASE WHEN MONTH(general_ledgers.jnl_date) = 9 THEN general_ledgers.amount ELSE 0 END) AS september")
    ->selectRaw("SUM(CASE WHEN MONTH(general_ledgers.jnl_date) = 10 THEN general_ledgers.amount ELSE 0 END) AS october")
    ->selectRaw("SUM(CASE WHEN MONTH(general_ledgers.jnl_date) = 11 THEN general_ledgers.amount ELSE 0 END) AS november")
    ->selectRaw("SUM(CASE WHEN MONTH(general_ledgers.jnl_date) = 12 THEN general_ledgers.amount ELSE 0 END) AS december")
    ->leftJoin('account_codes', 'account_codes.acc_no', '=', 'general_ledgers.acc_no')
    ->whereYear('general_ledgers.jnl_date', $year)
    ->groupBy('general_ledgers.acc_no', 'account_codes.account_name')
    ->get();

return Inertia::render('Accounting/TrialBalance', [
    'items' => $annualData
]);

2. 修改Vue页面，绑定对应月份字段 #

直接将每个月份的字段对应到表格列，用|| 0处理空值：

<table class="table table-zebra ">
    <thead>
    <tr>
        <th>Acc No.</th>
        <th>Account Name</th>
        <th>January</th>
        <th>February</th>
        <th>March</th>
        <th>April</th>
        <th>May</th>
        <th>June</th>
        <th>July</th>
        <th>August</th>
        <th>September</th>
        <th>October</th>
        <th>November</th>
        <th>December</th>
    </tr>
    </thead>
    <tbody>
        <tr v-for="item in items" :key="item.acc_no" >
            <td>{{ item.acc_no }}</td>
            <td>{{ item.account_name }}</td>
            <td>{{ item.january || 0 }}</td>
            <td>{{ item.february || 0 }}</td>
            <td>{{ item.march || 0 }}</td>
            <td>{{ item.april || 0 }}</td>
            <td>{{ item.may || 0 }}</td>
            <td>{{ item.june || 0 }}</td>
            <td>{{ item.july || 0 }}</td>
            <td>{{ item.august || 0 }}</td>
            <td>{{ item.september || 0 }}</td>
            <td>{{ item.october || 0 }}</td>
            <td>{{ item.november || 0 }}</td>
            <td>{{ item.december || 0 }}</td>
        </tr>
    </tbody>
</table>

核心说明 #

• 用条件聚合SUM(CASE...)替代多次查询，一次SQL即可获取所有月份的汇总数据，避免冗余操作
• Vue中通过|| 0处理空值，确保没有数据的月份显示0而不是空
• 分组时按acc_no和account_name分组，保证每个账户一行数据

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