如何编写SQL查询按月度统计orders表当年amount总和用于图表展示？

假设我们有一张orders表，结构和数据如下：

id | amount | order_date
---------------------------------
1 | 100.00 | 2018-03-12
2 | 120.00 | 2018-03-25
3 | 200.00 | 2018-04-10
4 | 250.00 | 2018-04-20
5 | 300.00 | 2018-05-02

现在需要编写SQL查询，计算当年每个月的订单金额总和，最终输出格式如下：

month | sum(amount)
-------------------
3     | 220.00
4     | 450.00
5     | 300.00

解决方案 #

不同数据库的日期处理函数略有差异，下面给出几种主流数据库的实现方案：

MySQL/MariaDB

SELECT
    MONTH(order_date) AS month,
    SUM(amount) AS `sum(amount)`
FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE()) -- 筛选当年数据，也可直接指定年份如`YEAR(order_date) = 2018`
GROUP BY MONTH(order_date)
ORDER BY month;

PostgreSQL

SELECT
    EXTRACT(MONTH FROM order_date) AS month,
    SUM(amount) AS "sum(amount)"
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY month;

SQL Server

SELECT
    MONTH(order_date) AS month,
    SUM(amount) AS [sum(amount)]
FROM orders
WHERE YEAR(order_date) = YEAR(GETDATE())
GROUP BY MONTH(order_date)
ORDER BY month;

关键细节说明 #

• 用日期提取函数（MONTH()或EXTRACT）从order_date中拿到月份数字
• SUM(amount)负责计算每个月的金额总和
• WHERE子句用来限定只统计当年数据，若需要固定年份，直接替换成具体数值即可
• GROUP BY按月份分组，保证每个月只返回一条汇总记录
• ORDER BY month让结果按月份顺序排列，更贴合后续图表制作的需求

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