Laravel 8查询如何为无数据的月份补全空值生成连续月份统计数据

方案一：PHP层数据补全（推荐，实现简单易维护） #

这个方案不需要修改原有SQL逻辑，仅需在查询后通过Carbon生成连续月份做数据对齐即可，适合绝大多数中小数据量场景。

use Carbon\Carbon;

// 1. 获取统计起始月份，兼容无用户的边界情况
$firstUser = User::orderBy('created_at', 'asc')->first();
$startDate = $firstUser ? Carbon::parse($firstUser->created_at)->startOfMonth() : Carbon::now()->startOfMonth();
$endDate = Carbon::now()->endOfMonth();

// 2. 查询已删除用户的按月统计数据
// 注意：如果你的业务逻辑是统计「删除月份」的用户数，保持SQL里用deleted_at；如果是统计「创建月份中后续被删除的用户数」，改回created_at即可
$stats = User::selectRaw('DATE_FORMAT(deleted_at, "%Y-%m") as date, COUNT(*) as total')
    ->groupByRaw('DATE_FORMAT(deleted_at, "%Y-%m")')
    ->withTrashed()
    ->whereNotNull('deleted_at')
    ->get()
    // 转成以日期为键的关联数组，方便后续快速匹配
    ->keyBy('date');

// 3. 生成连续月份列表，无数据月份默认填充0
$period = Carbon::parse($startDate)->monthsUntil($endDate);
$data = [];
foreach ($period as $month) {
    $dateStr = $month->format('Y-%m');
    $data[] = [
        'date' => $dateStr,
        'total' => $stats->has($dateStr) ? $stats[$dateStr]->total : 0
    ];
}

该方案时间复杂度仅为O(n)，n为统计的月份数，即使统计10年数据也只有120次循环，性能完全够用。

方案二：数据库层面生成连续月份（适合超大数据量场景） #

如果你的统计区间跨度超过10年、或者需要在SQL层面直接返回完整数据，可以通过生成临时连续月份表左关联统计结果实现（仅支持MySQL 8.0+、MariaDB 10.2+以上支持公共表表达式的版本）：

use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

$firstUser = User::orderBy('created_at', 'asc')->first();
$startDate = $firstUser ? Carbon::parse($firstUser->created_at)->startOfMonth() : Carbon::now()->startOfMonth();
$endDate = Carbon::now()->endOfMonth();

$monthsSql = <<<SQL
WITH RECURSIVE months AS (
    SELECT DATE_FORMAT(?, '%Y-%m-01') as month
    UNION ALL
    SELECT DATE_ADD(month, INTERVAL 1 MONTH) 
    FROM months 
    WHERE month < DATE_FORMAT(?, '%Y-%m-01')
)
SELECT DATE_FORMAT(m.month, '%Y-%m') as date, IFNULL(COUNT(u.id), 0) as total
FROM months m
LEFT JOIN users u 
    ON DATE_FORMAT(u.deleted_at, '%Y-%m') = DATE_FORMAT(m.month, '%Y-%m')
    AND u.deleted_at IS NOT NULL
GROUP BY m.month
SQL;

$data = DB::select($monthsSql, [$startDate, $endDate]);

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