mysql查詢本周內(nèi)每天統(tǒng)計(jì)量按天展示的示例代碼

更新時(shí)間：2022年05月20日 15:52:47 作者：良田7

本文主要介紹了mysql查詢本周內(nèi)每天統(tǒng)計(jì)量按天展示的示例代碼，文中通過示例代碼介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下

本周

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

到此這篇關(guān)于mysql查詢本周內(nèi)每天統(tǒng)計(jì)量按天展示的示例代碼的文章就介紹到這了,更多相關(guān)mysql統(tǒng)計(jì)量按天展示內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: