mysql查询本周内每天统计量按天展示
本周
SELECT
b. item, IFNULL( a. COUNT, 0 ) AS VALUE
FROM (
SELECT DATE ( subdate( curdate( ) , date_format( curdate( ) , '%w' ) - 1 ) ) as item
union all
SELECT DATE ( DATE_ADD( subdate( curdate( ) , date_format( curdate( ) , '%w' ) - 1 ) , interval 1 day ) ) as item
union all
SELECT DATE ( DATE_ADD( subdate( curdate( ) , date_format( curdate( ) , '%w' ) - 1 ) , interval 2 day ) ) as item
union all
SELECT DATE ( DATE_ADD( subdate( curdate( ) , date_format( curdate( ) , '%w' ) - 1 ) , interval 3 day ) ) as item
union all
SELECT DATE ( DATE_ADD( subdate( curdate( ) , date_format( curdate( ) , '%w' ) - 1 ) , interval 4 day ) ) as item
union all
SELECT DATE ( DATE_ADD( subdate( curdate( ) , date_format( curdate( ) , '%w' ) - 1 ) , interval 5 day ) ) as item
union all
SELECT DATE ( DATE_ADD( subdate( curdate( ) , date_format( curdate( ) , '%w' ) - 1 ) , interval 6 day ) ) as item
) b
LEFT JOIN
(
SELECT DATE_FORMAT( create_time, '%Y-%m-%d' ) days, COUNT ( * ) COUNT
FROM ( SELECT * FROM ` table` WHERE DATE_SUB( CURDATE( ) , INTERVAL 7 DAY ) <= DATE ( create_time) ) as c
GROUP BY days
) AS a
ON ( b. item = a. days)
前七天
SELECT
b. item, IFNULL( a. COUNT, 0 ) AS VALUE
FROM (
SELECT CURDATE( ) AS item
UNION ALL
SELECT DATE_SUB( CURDATE( ) , INTERVAL 1 DAY ) AS item
UNION ALL
SELECT DATE_SUB( CURDATE( ) , INTERVAL 2 DAY ) AS item
UNION ALL
SELECT DATE_SUB( CURDATE( ) , INTERVAL 3 DAY ) AS item
UNION ALL
SELECT DATE_SUB( CURDATE( ) , INTERVAL 4 DAY ) AS item
UNION ALL
SELECT DATE_SUB( CURDATE( ) , INTERVAL 5 DAY ) AS item
UNION ALL
SELECT DATE_SUB( CURDATE( ) , INTERVAL 6 DAY ) AS item
) b
LEFT JOIN
(
SELECT DATE_FORMAT( create_time, '%Y-%m-%d' ) days, COUNT ( * ) COUNT
FROM ( SELECT * FROM ` table` WHERE DATE_SUB( CURDATE( ) , INTERVAL 7 DAY ) <= DATE ( create_time) ) as c
GROUP BY days
) AS a
ON ( b. item = a. days)
本月
SELECT
` type` ,
max ( ` count` ) AS ` count`
FROM
(
SELECT
count ( * ) AS ` count` ,
DATE_FORMAT( create_time, '%Y-%m-%d' ) AS ` type`
FROM
` table` a
WHERE
DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
GROUP BY
` type` UNION ALL
SELECT
0 AS ` copunt` ,
@cdate := date_add( @cdate , INTERVAL - 1 DAY ) ` type`
FROM
( SELECT @cdate := date_add( last_day( curdate( ) ) , INTERVAL + 1 DAY ) FROM ` table` ) t1
WHERE
@cdate > (
date_add( curdate( ) , INTERVAL - DAY ( curdate( ) ) + 1 DAY ) )
) _tmpAllTable
GROUP BY
` type`
本年按月展示
SELECT
CONCAT(
YEAR ( click_date ) ,
'-' ,
MONTH ( click_date ) ) AS ` type` ,
IFNULL( b. con, 0 ) AS ` count`
FROM
(
SELECT
STR_TO_DATE( CONCAT( YEAR ( CURDATE( ) ) , '-' , 1 , '-' , 1 ) , '%Y-%m-%d' ) AS click_date UNION ALL
SELECT
DATE_ADD( STR_TO_DATE( CONCAT( YEAR ( CURDATE( ) ) , '-' , 1 , '-' , 1 ) , '%Y-%m-%d' ) , INTERVAL 1 MONTH ) AS click_date UNION ALL
SELECT
DATE_ADD( STR_TO_DATE( CONCAT( YEAR ( CURDATE( ) ) , '-' , 1 , '-' , 1 ) , '%Y-%m-%d' ) , INTERVAL 2 MONTH ) AS click_date UNION ALL
SELECT
DATE_ADD( STR_TO_DATE( CONCAT( YEAR ( CURDATE( ) ) , '-' , 1 , '-' , 1 ) , '%Y-%m-%d' ) , INTERVAL 3 MONTH ) AS click_date UNION ALL
SELECT
DATE_ADD( STR_TO_DATE( CONCAT( YEAR ( CURDATE( ) ) , '-' , 1 , '-' , 1 ) , '%Y-%m-%d' ) , INTERVAL 4 MONTH ) AS click_date UNION ALL
SELECT
DATE_ADD( STR_TO_DATE( CONCAT( YEAR ( CURDATE( ) ) , '-' , 1 , '-' , 1 ) , '%Y-%m-%d' ) , INTERVAL 5 MONTH ) AS click_date UNION ALL
SELECT
DATE_ADD( STR_TO_DATE( CONCAT( YEAR ( CURDATE( ) ) , '-' , 1 , '-' , 1 ) , '%Y-%m-%d' ) , INTERVAL 6 MONTH ) AS click_date UNION ALL
SELECT
DATE_ADD( STR_TO_DATE( CONCAT( YEAR ( CURDATE( ) ) , '-' , 1 , '-' , 1 ) , '%Y-%m-%d' ) , INTERVAL 7 MONTH ) AS click_date UNION ALL
SELECT
DATE_ADD( STR_TO_DATE( CONCAT( YEAR ( CURDATE( ) ) , '-' , 1 , '-' , 1 ) , '%Y-%m-%d' ) , INTERVAL 8 MONTH ) AS click_date UNION ALL
SELECT
DATE_ADD( STR_TO_DATE( CONCAT( YEAR ( CURDATE( ) ) , '-' , 1 , '-' , 1 ) , '%Y-%m-%d' ) , INTERVAL 9 MONTH ) AS click_date UNION ALL
SELECT
DATE_ADD( STR_TO_DATE( CONCAT( YEAR ( CURDATE( ) ) , '-' , 1 , '-' , 1 ) , '%Y-%m-%d' ) , INTERVAL 10 MONTH ) AS click_date UNION ALL
SELECT
DATE_ADD( STR_TO_DATE( CONCAT( YEAR ( CURDATE( ) ) , '-' , 1 , '-' , 1 ) , '%Y-%m-%d' ) , INTERVAL 11 MONTH ) AS click_date
) a
LEFT JOIN ( SELECT COUNT ( * ) AS con, CONCAT( YEAR ( REPORTDATE ) , '-' , MONTH ( REPORTDATE ) ) AS mon FROM ` ls172_workorder` GROUP BY mon ) b ON CONCAT(
YEAR ( click_date ) ,
'-' ,
MONTH ( click_date ) ) = b. mon