mysql按月统计六个月内不同类型订单的成交金额

创建数据库

CREATE DATABASE test;

创建订单表

1CREATE TABLE `t_order` (
2  `id` bigint(4) NOT NULL AUTO_INCREMENT COMMENT '主键，自增',  
3  `source` smallint(1) NOT NULL COMMENT '订单来源:1:商城 2:订单导入',
4  `status` smallint(1) NOT NULL COMMENT '订单状态:1: 待确认 2：已确认 3:已完成',
5  `order_amt` decimal(20,8) NOT NULL COMMENT '订单总金额',
6  `create_user_id` bigint(4) DEFAULT NULL COMMENT '创建人id',
7  `create_time` datetime DEFAULT NULL COMMENT '创建日期',
8  PRIMARY KEY (`id`)
9) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

此处订单表只涉及统计需要的字段，实际开发应该会复杂一些。

插入测试数据

1INSERT INTO `t_order` (`id`, `source`, `status`, `order_amt`, `create_user_id`, `create_time`) VALUES ('1', '1', '2', '8000.00000000', '1', '2018-01-01 22:12:41');
2INSERT INTO `t_order` (`id`, `source`, `status`, `order_amt`, `create_user_id`, `create_time`) VALUES ('2', '2', '3', '9000.00000000', '1', '2018-05-16 22:13:24');
3INSERT INTO `t_order` (`id`, `source`, `status`, `order_amt`, `create_user_id`, `create_time`) VALUES ('3', '1', '3', '5000.00000000', '1', '2018-01-17 22:14:00');
4INSERT INTO `t_order` (`id`, `source`, `status`, `order_amt`, `create_user_id`, `create_time`) VALUES ('4', '1', '3', '12000.00000000', '2', '2017-11-16 22:15:03');
5INSERT INTO `t_order` (`id`, `source`, `status`, `order_amt`, `create_user_id`, `create_time`) VALUES ('5', '2', '3', '7000.00000000', '2', '2018-04-18 22:15:43');

统计六个月内不同类型已完成状态订单的成交额

1SELECT
2    six_month.`month` `month`,
3    source.source source,
4    IFNULL(SUM(o.order_amt), 0) orderAmt
5FROM
6    (
7        SELECT DATE_FORMAT(now(), '%Y-%c') month FROM DUAL
8        UNION ALL SELECT DATE_FORMAT(date_sub(now(), INTERVAL 1 MONTH), '%Y-%c') month FROM DUAL
9        UNION ALL SELECT DATE_FORMAT(date_sub(now(), INTERVAL 2 MONTH), '%Y-%c') month FROM DUAL
10        UNION ALL SELECT DATE_FORMAT(date_sub(now(), INTERVAL 3 MONTH), '%Y-%c') month FROM DUAL
11        UNION ALL SELECT DATE_FORMAT(date_sub(now(), INTERVAL 4 MONTH), '%Y-%c') month FROM DUAL
12        UNION ALL SELECT DATE_FORMAT(date_sub(now(), INTERVAL 5 MONTH), '%Y-%c') month FROM DUAL
13    ) six_month
14CROSS JOIN (
15    SELECT 1 source FROM DUAL
16    union all 
17    SELECT 2 source FROM DUAL
18) source
19LEFT JOIN t_order o ON o.source = source.source
20AND DATE_FORMAT(o.create_time, '%Y-%c') = six_month.`month`
21AND `status` = 3
22GROUP BY
23    six_month.`month`,
24    source.source;

统计结果

从结果可以看出，统计出了最近六个月内每个月每一种订单类型的已完成状态的订单的成交额。