概述mysql 作为常用数据库， *** 作贼六是必须的，对于数字 *** 作相关的东西，那是相当方便，本节就来拎几个统计案例出来供参考！order订单表,样例如下：CREATE TABLE `t_order` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,`user_id` int(11) NOT NULL,`order_nid` varchar(50) NOT NULL,`status` varchar(50) NOT NULL DEFAULT '0',`money` decimal(20,2) NOT NULL DEFAULT '0.00',`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`),KEY `userid` (`user_id`),KEY `createtime` (`create_time`),KEY `updatetime` (`update_time`)) ENGINE=InnoDB;1. 按天统计进单量，date_formatSELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d') t_date, COUNT(1) t_count FROM t_order t WHERE t.`create_time` > '2018-05-11' GROUP BY DATE_FORMAT(t.`create_time`, '%Y-%m-%d'); 2. 按小时统计进单量SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d %H') t_hour, COUNT(1) t_count FROM t_order t WHERE t.`create_time` > '2018-05-11' GROUP BY DATE_FORMAT(t.`create_time`, '%Y-%m-%d %H'); 3. 同比昨天进单量对比，order by h, dateSELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d %H') t_date, COUNT(1) t_count FROM t_order t WHERE t.`create_time` > '2018-05-11' GROUP BY DATE_FORMAT(t.`create_time`, '%Y-%m-%d %H')ORDER BY DATE_FORMAT(t.`create_time`, '%H'),DATE_FORMAT(t.`create_time`, '%Y-%m-%d %H'); 4. 环比上周同小时进单，date in ，order bySELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d %H') t_date, COUNT(1) t_count FROM t_order t WHEREDATE_FORMAT(t.`create_time`,'%Y-%m-%d') IN ('2018-05-03','2018-05-11') GROUP BY DATE_FORMAT(t.`create_time`, '%Y-%m-%d %H')ORDER BY DATE_FORMAT(t.`create_time`, '%H'),DATE_FORMAT(t.`create_time`, '%Y-%m-%d %H'); 5. 按照remark字段中的返回值进行统计，group by remark like ...SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d') t_date, COUNT(1) t_count, SUBSTRING_INDEX(SUBSTRING_INDEX(t.`msg`, '{', -1), '}', 1) t_rsp_msg FROMcmoo_tab t WHERE t.`create_time` > '2018-05-17' AND t.`rsp_msg` LIKE '%nextProcessCode%C9000%'GROUP BY DATE_FORMAT(t.`create_time`, '%Y-%m-%d'),SUBSTRING_INDEX(SUBSTRING_INDEX(t.`rsp_msg`, '{', -1), '}', 1); 6. 统计每小时的各金额的区间数统计，sum if 1 0，各自统计SELECT DATE_FORMAT(t.create_time,'%Y-%m-%d') t_date, SUM(IF(t.`amount`>0 AND t.`amount`<1000, 1, 0)) t_0_1000, SUM(IF(t.`amount`>1000 AND t.`amount`<5000, 1, 0)) t_1_5000,SUM(IF(t.`amount`>5000, 1, 0)) t_5000m FROM t_order t WHERE t.`create_time` > '2018-05-11' GROUP BY DATE_FORMAT(t.`create_time`, '%Y-%m-%d');7. 按半小时统计进单量，floor h / 30，同理10分钟，20分钟SELECT CONCAT(DATE_FORMAT(create_time, '%Y-%m-%d %H:' ),IF(FLOOR(DATE_FORMAT(create_time, '%i') / 30 ) = 0, '00','30')) AS time_scope, COUNT(*)FROM t_order WHERE create_time>'2018-05-11' GROUP BY time_scope ORDER BY DATE_FORMAT(create_time, '%H:%i'), DATE_FORMAT(create_time, '%Y-%m-%d') DESC ;8. 成功率，失败率，临时表 join on hourSELECT * FROM(SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d') t_date,COUNT(1) '成功数' FROM t_order t WHERE t.`create_time` > '2018-05-17' AND t.`status` = 'repay_yes' GROUP BY DATE_FORMAT(t.`create_time`, '%Y-%m-%d')) t1RIGHT JOIN(SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d') t_date,COUNT(1) '总数' FROM t_order t WHERE t.`create_time` > '2018-05-11' GROUP BY DATE_FORMAT(t.`create_time`, '%Y-%m-%d')) t2 ON t1.t_date=t2.t_date;9. 更新日志表中最后条一条日志状态值到信息表中状态，update a join b on xx set a.status=b.status where tmp group by userid tmp2，注意索引UPDATE t_order t0 LEFT JOIN (SELECT * FROM (SELECT * FROM t_order_log t WHERE t.create_time>'2018-05-11' ORDER BY id DESC) t1GROUP BY t1.user_id ) ON t.user_id=t2.user_id SET t0.`status`=t2.status WHERE t0.`create_time`>'2018-05-11' AND t0.`status`=10;10. 备份表，create table as select xxx where xxxCREATE TABLE t_m AS SELECT * FROM t_order;11. 纯改备注不锁表，快，类型全一致12. 动态查询环比上周数据SELECT DATE_FORMAT(t.create_time, '%Y-%m-%d %H') t_hour, COUNT(1) FROM `t_order` t WHERE t.`create_time` > CURDATE()OR (t.`create_time` > DATE_SUB(CURDATE(), INTERVAL 8 DAY) AND t.`create_time` < DATE_SUB(CURDATE(), INTERVAL 7 DAY))GROUP BY DATE_FORMAT(t.create_time, '%H'), DATE_FORMAT(t.create_time, '%Y-%m-%d');结果如之前环比，只是不用每次变换日期以迎合不同的时间查询，同理可能同比昨天的数据问题！
MysqL 作为常用数据库， *** 作贼六是必须的，对于数字 *** 作相关的东西，那是相当方便，本节就来拎几个统计案例出来供参考！
order订单表,样例如下：
() unsigned ` () () () ` (,) `userID` (`InnoDB;1. 按天统计进单量，date_format
DATE_FORMAT(t.`create_time`,) t_date,() t_count t_order t t.`create_time` DATE_FORMAT(t.`create_time`,);2. 按小时统计进单量
DATE_FORMAT(t.`create_time`,) t_hour,);3. 同比昨天进单量对比，order by h,date
DATE_FORMAT(t.`create_time`,) t_date, DATE_FORMAT(t.`create_time`,),DATE_FORMAT(t.`create_time`,);
4. 环比上周同小时进单，date in ，order by
DATE_FORMAT(t.`create_time`,() t_count t_order t ) (,) DATE_FORMAT(t.`create_time`,);
5. 按照remark字段中的返回值进行统计，group by remark like ...
DATE_FORMAT(t.`create_time`,() t_count,SUBSTRING_INDEX(SUBSTRING_INDEX(t.`msg`,,),,) t_rsp_msg t.`create_time` t.`rsp_msg` DATE_FORMAT(t.`create_time`,),SUBSTRING_INDEX(SUBSTRING_INDEX(t.`rsp_msg`,);6. 统计每小时的各金额的区间数统计，sum if 1 0，各自统计
DATE_FORMAT(t.create_time,((t.`amount` t.`amount`,,)) t_0_1000,((t.`amount` t.`amount`,)) t_1_5000,((t.`amount`,)) t_5000m t_order t t.`create_time` DATE_FORMAT(t.`create_time`,);7. 按半小时统计进单量，floor h / 30，同理10分钟，20分钟
CONCAT(DATE_FORMAT(create_time,((DATE_FORMAT(create_time,) ) ,,)) time_scope,( t_order create_time DATE_FORMAT(create_time,),DATE_FORMAT(create_time,) ;8. 成功率，失败率，临时表 join on hour
DATE_FORMAT(t.`create_time`,() t_order t t.`create_time` DATE_FORMAT(t.`create_time`, 