计算同比
SELECT old.now_time, ROUND( ( new.count - old.count ) / old.count * 100, 2 ), new.count ncount, old.count ocount FROM (SELECT sum( IF ( b.count > 1, 1, 1 ) ) count, b.now_time, b.tenant_code FROM ( SELECT date_format( ro.create_dt, '%Y-%m' ) AS now_time, a.tenant_code, a.order_no, count( a.id ) count FROM t_rep_order_timeout a INNER JOIN t_rep_order ro on a.tenant_code = ro.tenant_code and a.order_no = ro.order_no WHERE a.tenant_code = 'zlyy' GROUP BY date_format( ro.create_dt, '%Y-%m' ), a.tenant_code, a.order_no ) b GROUP BY b.now_time, b.tenant_code ) new LEFT JOIN(SELECT sum( IF ( b.count > 1, 1, 1 ) ) count, b.now_time, b.tenant_code FROM ( SELECT date_format( DATE_ADD( ro.create_dt, INTERVAL 1 YEAR ), '%Y-%m') AS now_time, a.tenant_code, a.order_no, count( a.id ) count FROM t_rep_order_timeout a INNER JOIN t_rep_order ro on a.tenant_code = ro.tenant_code and a.order_no = ro.order_no WHERE a.tenant_code = 'zlyy' GROUP BY date_format( DATE_ADD( ro.create_dt, INTERVAL 1 YEAR ), '%Y-%m'), a.tenant_code, a.order_no ) b GROUP BY b.now_time, b.tenant_code) old on old.tenant_code = new.tenant_code and old.now_time = new.now_time计算环比
SELECT old.now_time, ROUND( ( new.count - old.count ) / old.count * 100, 2 ), new.count ncount, old.count ocount FROM (SELECT sum( IF ( b.count > 1, 1, 1 ) ) count, b.now_time, b.tenant_code FROM ( SELECT date_format( ro.create_dt, '%Y-%m' ) AS now_time, a.tenant_code, a.order_no, count( a.id ) count FROM t_rep_order_timeout a INNER JOIN t_rep_order ro on a.tenant_code = ro.tenant_code and a.order_no = ro.order_no WHERE a.tenant_code = 'zlyy' GROUP BY date_format( ro.create_dt, '%Y-%m' ), a.tenant_code, a.order_no ) b GROUP BY b.now_time, b.tenant_code ) new LEFT JOIN(SELECT sum( IF ( b.count > 1, 1, 1 ) ) count, b.now_time, b.tenant_code FROM ( SELECT date_format( DATE_ADD( ro.create_dt, INTERVAL 1 MONTH ), '%Y-%m') AS now_time, a.tenant_code, a.order_no, count( a.id ) count FROM t_rep_order_timeout a INNER JOIN t_rep_order ro on a.tenant_code = ro.tenant_code and a.order_no = ro.order_no WHERE a.tenant_code = 'zlyy' GROUP BY date_format( DATE_ADD( ro.create_dt, INTERVAL 1 MONTH ), '%Y-%m'), a.tenant_code, a.order_no ) b GROUP BY b.now_time, b.tenant_code) old on old.tenant_code = new.tenant_code and old.now_time = new.now_time获取某一年份所有月份
SELECTCASE WHEN length( mon ) = 1 THEN concat( '2019-0', mon ) ELSE concat( '2019-', mon ) END months FROM ( SELECT @m := @m + 1 mon FROM t_rep_order_timeout, ( SELECT @m := 0 ) a ) aa LIMIT 12此处所用的辅助表t_rep_oder_timeot表数据必须超过12条
删除表内的反复数据
DELETE t FROM interview t LEFT JOIN ( SELECT title, min( id ) AS min_id FROM interview GROUP BY title ) t1 ON t.id = t1.min_id WHERE t1.min_id IS NULL;欢送搜寻关注自己与敌人共同开发的微信面经小程序【大厂面试助手】和公众号【微瞰技术】