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

来源：https://blog.csdn.net/m0_52786528/article/details/124339848