mysql查询本周内每天统计量按天展示的示例代码

本周

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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)

前七天

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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)

本月

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

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`

本年按月展示

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

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

到此这篇关于mysql查询本周内每天统计量按天展示的示例代码的文章就介绍到这了,更多相关mysql统计量按天展示内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/m0_52786528/article/details/124339848