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

按日统计，前十天

SELECT
          days.day dateField,
          COALESCE(COUNT(archive_no), 0) AS total_quantity
        FROM
          (
            SELECT
              DATE_FORMAT(DATE_SUB(now(), INTERVAL a.a DAY), '%Y-%m-%d') AS day
            FROM
              (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 days
        LEFT JOIN zw_archive ON days.day = DATE_FORMAT(zw_archive.create_time, '%Y-%m-%d') and archive_status = '2'
        GROUP BY days.day
        ORDER BY days.day;

按月统计

SELECT
          months.month dateField,
          COALESCE(COUNT(archive_no), 0) AS total_quantity
        FROM
          (
            SELECT
              DATE_FORMAT(DATE_SUB(now(), INTERVAL a.a MONTH), '%Y-%m') AS month
            FROM
              (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 months
        LEFT JOIN zw_archive ON months.month = DATE_FORMAT(create_time, '%Y-%m') and archive_status = '2'
        GROUP BY months.month
        ORDER BY months.month

按年统计

SELECT
          days.day dateField,
          COALESCE(COUNT(archive_no), 0) AS total_quantity
        FROM
          (
            SELECT
              DATE_FORMAT(DATE_SUB(now(), INTERVAL a.a YEAR), '%Y') AS day
            FROM
              (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 days
        LEFT JOIN zw_archive ON days.day = DATE_FORMAT(zw_archive.create_time, '%Y') and archive_status = '2'
        GROUP BY days.day
        ORDER BY days.day;

创建视图

Create view selectBusiness as SELECT
        t1.*
        FROM
        (
        SELECT
        t1.*,
        t2.customer_name
        FROM
        (
        SELECT
        order_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_type
            WHEN '1' THEN '上门取档'
            WHEN '2' THEN '档案自送'
            ELSE '未知状态'
        END AS order_type,
        update_time,
        create_time,
        contacts_user,
        contacts_phone,
        address
        FROM
        pzda_order
        WHERE
        del_flag = '1'
        -- AND order_type = '1'
        -- AND ( status = '4' OR status = '5' )
        AND DATE_FORMAT( operation_time, '%y%m%d' ) <= DATE_FORMAT( now( ), '%y%m%d' )
        ) t1
        LEFT JOIN pzda_customer t2 ON t2.customer_code = t1.customer_code
        UNION
        SELECT
        t1.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_name
        FROM
        (
        SELECT
        max( id ),
        outbound_id business_id,
        STATUS,
        operation_time,
        update_time,
        create_time,
        contacts,
        contacts_phone,
        address
        FROM
        pzda_transfer_outbound_return
        -- WHERE  STATUS = '1'
        GROUP BY
        outbound_id,
        STATUS,
        operation_time,
        update_time,
        create_time,
        contacts,
        contacts_phone,
        address
        ) t1
        LEFT JOIN pzda_transfer_outbound t2 ON t2.outbound_id = t1.business_id
        LEFT JOIN pzda_customer t3 ON t3.customer_code = t2.customer_code
        UNION
        SELECT
        t1.*,
        t2.customer_address address,
        t2.customer_name
        FROM
        (
        SELECT
        outbound_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_phone
        FROM
        pzda_transfer_outbound
        WHERE
        del_flag = '1'
        -- AND return_status = '1'
        AND return_type is null
        -- AND STATUS = '3'
        -- AND transfer_type = '2'
        ) t1
        LEFT JOIN pzda_customer t2 ON t2.customer_code = t1.customer_code
        ) t1 order by order_status,jd_status,return_status asc, business_id desc