MYsql 7天数据汇总统计 SQL语句 半成品

SELECT date_sub(CURDATE(), INTERVAL 6 DAY) AS col1,
date_sub(CURDATE(), INTERVAL 5 DAY) AS col2,
date_sub(CURDATE(), INTERVAL 4 DAY) AS col3,
date_sub(CURDATE(), INTERVAL 3 DAY) AS col4,
date_sub(CURDATE(), INTERVAL 2 DAY) AS col5 ,
date_sub(CURDATE(), interval 1 DAY) AS col6 ,
CURDATE() AS col7 ;

 

SELECT
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 6 DAY ),count,0)) as 'col1',
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 5 DAY ),count,0)) as 'col2',
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 4 DAY ),count,0)) AS 'col3',
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 3 DAY ),count,0)) AS 'col4',
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 2 DAY ),count,0)) AS 'col5',
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 1 DAY ),count,0)) AS 'col6',
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 0 DAY ),count,0)) AS 'col7'
FROM
(SELECT -- 查询7天的入库数据 BEGIN
DATE_FORMAT( CreationTime, '%Y-%m-%d' ) days,
count(*) count
FROM
( SELECT * FROM wh_taskinfo
WHERE DATE_SUB( CURDATE( ), INTERVAL 7 DAY ) <= date( CreationTime) AND TaskType = '1' AND WarehouseID = 'LW' AND State in ('Finish') ) as CreationTime
GROUP BY
days) A -- 查询7天的入库数据 END

UNION

SELECT
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 6 DAY ),count,0)) as 'col1',
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 5 DAY ),count,0)) as 'col2',
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 4 DAY ),count,0)) AS 'col3',
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 3 DAY ),count,0)) AS 'col4',
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 2 DAY ),count,0)) AS 'col5',
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 1 DAY ),count,0)) AS 'col6',
SUM(IF(days=DATE_SUB( CURDATE( ), INTERVAL 0 DAY ),count,0)) AS 'col7'
FROM
(SELECT -- 查询7天的出库数据 BEGIN
DATE_FORMAT( CreationTime, '%Y-%m-%d' ) days,
count(*) count
FROM
( SELECT * FROM wh_taskinfo
WHERE DATE_SUB( CURDATE( ), INTERVAL 7 DAY ) <= date( CreationTime) AND TaskType = '2' AND WarehouseID = 'LW' AND State in ('Finish') ) as CreationTime
GROUP BY
days) B -- 查询7天的出库数据 END