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

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