按日统计,前十天
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