MySQL 按年月日统计，创建视图

本文主要是介绍MySQL 按年月日统计，创建视图，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

按日统计，前十天

SELECTdays.day dateField,COALESCE(COUNT(archive_no), 0) AS total_quantityFROM(SELECTDATE_FORMAT(DATE_SUB(now(), INTERVAL a.a DAY), '%Y-%m-%d') AS dayFROM(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9  ) AS a) AS daysLEFT JOIN zw_archive ON days.day = DATE_FORMAT(zw_archive.create_time, '%Y-%m-%d') and archive_status = '2'GROUP BY days.dayORDER BY days.day;

按月统计

SELECTmonths.month dateField,COALESCE(COUNT(archive_no), 0) AS total_quantityFROM(SELECTDATE_FORMAT(DATE_SUB(now(), INTERVAL a.a MONTH), '%Y-%m') AS monthFROM(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19) AS a) AS monthsLEFT JOIN zw_archive ON months.month = DATE_FORMAT(create_time, '%Y-%m') and archive_status = '2'GROUP BY months.monthORDER BY months.month

按年统计

SELECTdays.day dateField,COALESCE(COUNT(archive_no), 0) AS total_quantityFROM(SELECTDATE_FORMAT(DATE_SUB(now(), INTERVAL a.a YEAR), '%Y') AS dayFROM(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9  ) AS a) AS daysLEFT JOIN zw_archive ON days.day = DATE_FORMAT(zw_archive.create_time, '%Y') and archive_status = '2'GROUP BY days.dayORDER BY days.day;

创建视图

Create view selectBusiness as SELECTt1.*FROM(SELECTt1.*,t2.customer_nameFROM(SELECTorder_no business_id,STATUS order_status,'' jd_status,'' return_status,( SELECT count( 1 ) FROM pzda_box_code WHERE  scan_status != '0' ) scan_status,customer_code,advance_quantity,operation_time,'待交接' AS operation,CASE order_typeWHEN '1' THEN '上门取档'WHEN '2' THEN '档案自送'ELSE '未知状态'END AS order_type,update_time,create_time,contacts_user,contacts_phone,addressFROMpzda_orderWHEREdel_flag = '1'-- AND order_type = '1'-- AND ( status = '4' OR status = '5' )AND DATE_FORMAT( operation_time, '%y%m%d' ) &lt;= DATE_FORMAT( now( ), '%y%m%d' )) t1LEFT JOIN pzda_customer t2 ON t2.customer_code = t1.customer_codeUNIONSELECTt1.business_id,'' order_status,'' jd_status,t1.STATUS return_status,( SELECT count( 1 ) FROM pzda_box_code WHERE scan_status != '0' ) scan_status,t2.customer_code,t2.outbound_quantity advance_quantity,t1.operation_time,'归还取档' AS operation,'归还取档' order_type,t1.update_time,t1.create_time,t1.contacts contacts_user,t1.contacts_phone,t1.address,t3.customer_nameFROM(SELECTmax( id ),outbound_id business_id,STATUS,operation_time,update_time,create_time,contacts,contacts_phone,addressFROMpzda_transfer_outbound_return-- WHERE  STATUS = '1'GROUP BYoutbound_id,STATUS,operation_time,update_time,create_time,contacts,contacts_phone,address) t1LEFT JOIN pzda_transfer_outbound t2 ON t2.outbound_id = t1.business_idLEFT JOIN pzda_customer t3 ON t3.customer_code = t2.customer_codeUNIONSELECTt1.*,t2.customer_address address,t2.customer_nameFROM(SELECToutbound_id business_id,'' order_status,STATUS jd_status,'' return_status,( SELECT count( 1 ) FROM pzda_box_code WHERE scan_status != '0' ) scan_status,customer_code,outbound_quantity advance_quantity,CONCAT_WS( ' ', arrival_date, arrival_time ) operation_time,'借调送档' AS operation,'借调送档' order_type,update_time,create_time,contacts contacts_user,contacts_phoneFROMpzda_transfer_outboundWHEREdel_flag = '1'-- AND return_status = '1'AND return_type is null-- AND STATUS = '3'-- AND transfer_type = '2') t1LEFT JOIN pzda_customer t2 ON t2.customer_code = t1.customer_code) t1 order by order_status,jd_status,return_status asc, business_id desc