如何用Laravel查询构建器或原生SQL实现日期列拆分与分数聚合

原始数据表格 #

目标结果表格 #

1. Raw SQL 实现 #

假设数据表名为scores，分两种字段类型处理：

场景一：Date字段为DATE/DATETIME类型

SELECT
    name,
    SUM(CASE WHEN DAY(date) = 1 THEN score ELSE 0 END) AS `Day 1`,
    SUM(CASE WHEN DAY(date) = 2 THEN score ELSE 0 END) AS `Day 2`,
    SUM(CASE WHEN DAY(date) = 3 THEN score ELSE 0 END) AS `Day 3`
    -- 如需扩展更多天数，继续添加对应CASE WHEN语句
FROM scores
WHERE date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY name;

场景二：Date字段为DD-MM-YYYY格式字符串

需先转换为日期类型再处理：

SELECT
    name,
    SUM(CASE WHEN DAY(STR_TO_DATE(date, '%d-%m-%Y')) = 1 THEN score ELSE 0 END) AS `Day 1`,
    SUM(CASE WHEN DAY(STR_TO_DATE(date, '%d-%m-%Y')) = 2 THEN score ELSE 0 END) AS `Day 2`,
    SUM(CASE WHEN DAY(STR_TO_DATE(date, '%d-%m-%Y')) = 3 THEN score ELSE 0 END) AS `Day 3`
FROM scores
WHERE STR_TO_DATE(date, '%d-%m-%Y') BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY name;

2. Laravel Query Builder 实现 #

场景一：Date字段为DATE/DATETIME类型

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

// 指定目标月份
$targetMonth = Carbon::parse('2023-01-01');
$startOfMonth = $targetMonth->startOfMonth()->toDateString();
$endOfMonth = $targetMonth->endOfMonth()->toDateString();

$result = DB::table('scores')
    ->select([
        'name',
        DB::raw('SUM(CASE WHEN DAY(date) = 1 THEN score ELSE 0 END) AS `Day 1`'),
        DB::raw('SUM(CASE WHEN DAY(date) = 2 THEN score ELSE 0 END) AS `Day 2`'),
        DB::raw('SUM(CASE WHEN DAY(date) = 3 THEN score ELSE 0 END) AS `Day 3`'),
        // 按需添加更多天数的聚合语句
    ])
    ->whereBetween('date', [$startOfMonth, $endOfMonth])
    ->groupBy('name')
    ->get();

场景二：Date字段为DD-MM-YYYY格式字符串

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

$targetMonth = Carbon::parse('2023-01-01');
$startOfMonth = $targetMonth->startOfMonth()->toDateString();
$endOfMonth = $targetMonth->endOfMonth()->toDateString();

$result = DB::table('scores')
    ->select([
        'name',
        DB::raw('SUM(CASE WHEN DAY(STR_TO_DATE(date, "%d-%m-%Y")) = 1 THEN score ELSE 0 END) AS `Day 1`'),
        DB::raw('SUM(CASE WHEN DAY(STR_TO_DATE(date, "%d-%m-%Y")) = 2 THEN score ELSE 0 END) AS `Day 2`'),
        DB::raw('SUM(CASE WHEN DAY(STR_TO_DATE(date, "%d-%m-%Y")) = 3 THEN score ELSE 0 END) AS `Day 3`'),
    ])
    ->whereRaw('STR_TO_DATE(date, "%d-%m-%Y") BETWEEN ? AND ?', [$startOfMonth, $endOfMonth])
    ->groupBy('name')
    ->get();

动态生成当月所有天数列（推荐）

如果需要自动适配当月所有天数，无需手动编写每个日期的聚合：

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

$targetMonth = Carbon::parse('2023-01-01');
$daysInMonth = $targetMonth->daysInMonth;
$startOfMonth = $targetMonth->startOfMonth()->toDateString();
$endOfMonth = $targetMonth->endOfMonth()->toDateString();

// 构建查询字段
$selects = ['name'];
for ($day = 1; $day <= $daysInMonth; $day++) {
    $selects[] = DB::raw("SUM(CASE WHEN DAY(date) = $day THEN score ELSE 0 END) AS `Day $day`");
}

$result = DB::table('scores')
    ->select($selects)
    ->whereBetween('date', [$startOfMonth, $endOfMonth])
    ->groupBy('name')
    ->get();

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