Laravel技术需求：按created_at分组并按月汇总cost值

以下是不同主流数据库下的实现代码，按需选用：

MySQL/MariaDB #

通过DATE_FORMAT函数提取年份+月份，避免跨年的同月份数据被错误合并：

SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    SUM(cost) AS total_cost
FROM
    your_table_name
GROUP BY
    DATE_FORMAT(created_at, '%Y-%m')
ORDER BY
    month;

PostgreSQL #

使用TO_CHAR函数格式化日期：

SELECT
    TO_CHAR(created_at, 'YYYY-MM') AS month,
    SUM(cost) AS total_cost
FROM
    your_table_name
GROUP BY
    TO_CHAR(created_at, 'YYYY-MM')
ORDER BY
    month;

SQL Server #

推荐用DATEPART组合年份和月份的方式（性能更优）：

SELECT
    CONCAT(DATEPART(YEAR, created_at), '-', RIGHT('0' + CAST(DATEPART(MONTH, created_at) AS VARCHAR), 2)) AS month,
    SUM(cost) AS total_cost
FROM
    your_table_name
GROUP BY
    DATEPART(YEAR, created_at),
    DATEPART(MONTH, created_at)
ORDER BY
    DATEPART(YEAR, created_at),
    DATEPART(MONTH, created_at);

也可以用FORMAT函数简化写法：

SELECT
    FORMAT(created_at, 'yyyy-MM') AS month,
    SUM(cost) AS total_cost
FROM
    your_table_name
GROUP BY
    FORMAT(created_at, 'yyyy-MM')
ORDER BY
    month;

注意事项 #

• 替换代码中的your_table_name为实际表名
• 如果不需要区分年份，可将日期格式改为%m（MySQL）、MM（PostgreSQL/SQL Server），但会合并不同年份的同月数据，需谨慎使用

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