我正在尝试用 PHP 编写 MySQL SELECT 语句.
I'm trying to write a MySQL SELECT statement in PHP.
我有 2 个表,sales 和 sale_items.
I have 2 tables, sales and sale_items.
sales 有列:sale_id、status
sale_items 有列:sale_id、date_ added (DATETIME)、quantity
sale_items has columns: sale_id, date_added (DATETIME), quantity
我需要从 sale_items 返回过去 12 个月按月分组的数量以及 sales 等于已完成"(如您所见,sales 和 sale_items 可以由 sale_id 连接).
I need to return the quantities from sale_items, over the last 12 months grouped by month and where the status of the corresponding row in sales is equal to 'completed' (as you can see, sales and sale_items can be joined by sale_id).
我尝试修改以下两个以满足我的需要,但没有运气:
I have tried modifying both of the following to suit my needs, but with no luck:
MySQL 每月销售最后一个12 个月,包括没有销售的月份
过去 12 个月的 Mysql 总和
推荐答案使用 MySQL 非常简单 MONTH() 函数以及GROUP BY caluse.
It's very easy you can use MySQL MONTH() function for this along with GROUP BY caluse.
SELECT SUM(SI.quantity),MONTH(SI.date_added) FROM sale_items SI JOIN sales S ON S.id=SI.sale_id WHERE S.status = 'completed' GROUP BY MONTH(SI.date_added);更多推荐
MySQL 获取按月分组的过去 12 个月的数量
发布评论