mysql中怎么实现数据统计
本篇文章为大家展示了MySQL中怎么实现数据统计，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。
成都创新互联从2013年成立，先为黟县等服务建站，黟县等地企业，进行企业商务咨询服务。为黟县企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
CREATE TABLE `yyd_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_format
SELECT 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, date
SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d %H') t_date, COUNT(1) t_count FROM yyd_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 by
SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d %H') t_date, COUNT(1) t_count FROM yyd_order t WHERE
DATE_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 FROM
cmoo_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 mobp2p.`yyd_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 yyd_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 hour
SELECT * FROM (SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d') t_date,COUNT(1) '成功数' FROM yyd_order t WHERE t.`create_time` > '2018-05-17' AND t.`status` = 'repay_yes' GROUP BY DATE_FORMAT(t.`create_time`, '%Y-%m-%d')) t1 RIGHT JOIN (SELECT DATE_FORMAT(t.`create_time`, '%Y-%m-%d') t_date,COUNT(1) '总数' FROM yyd_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) t1 GROUP 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 xxx
CREATE TABLE t_m AS SELECT * FROM t_order;
上述内容就是mysql中怎么实现数据统计，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注创新互联行业资讯频道。
文章标题：mysql中怎么实现数据统计
文章源于：http://cdiso.cn/article/jhhjis.html
