sql - 选择每月

Question

我有下表:

purchases(id, item, user, price, time);

time 字段是一个时间戳。

我需要一个每月返回一行的查询，该行将包含该月每件商品的 price 总和。

Answer 1

试试这个:

SELECT MONTH(`time`) AS month, SUM(price) AS price
FROM your_table
GROUP BY MONTH(`time`)

如果您有超过一年的数据，您可能还希望按以下方式将年份包括在您的组中:

SELECT YEAR(`time`) AS year, MONTH(`time`) AS month, SUM(price) AS price
FROM your_table
GROUP BY YEAR(`time`), MONTH(`time`)