MYSQL统计每年、每月、每日的数据

日常我们经常需要查询每年、或者每月等等的数据信息（例如访问记录、订单信息等等），以下方法是查询每年、每月、或每日的方法！

这是测试的订单信息，包括了上一年的交易信息，还有每月的多条交易信息（下面会附上数据表代码）

查询每年数据

select SUM(total) AS total FROM `order` GROUP BY YEAR(trade_time)

select SUM(total) AS total FROM `order` GROUP BY MONTH(trade_time)

select SUM(total) AS total FROM `order` GROUP BY WEEK(trade_time)

select SUM(total) AS total FROM `order` GROUP BY DAY(trade_time)

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `order` varchar(100) DEFAULT NULL COMMENT "订单",
  `total` decimal(10,2) DEFAULT NULL COMMENT "交易金额",
  `trade_time` timestamp NULL DEFAULT NULL COMMENT "交易时间",
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `order`
-- ----------------------------
BEGIN;
INSERT INTO `order` VALUES ("1", "111111", "100.00", "2016-11-11 10:11:27"), ("2", "22222", "200.00", "2016-12-22 10:08:52"), ("3", "33333", "150.00", "2017-10-16 10:11:45"), ("4", "44444", "300.00", "2017-10-18 10:12:07"), ("5", "55555", "400.00", "2017-11-01 10:12:07");
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;