服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - mysql查询本周内每天统计量按天展示的示例代码

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

2022-10-31 14:34良田7 Mysql

本文主要介绍了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

  • mysql
  • 统计
  • 查询

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1864 　 1862 　 1146 　 1054 　 2006 　 1615 　 1215 　 1175 　 10048 　 2003 　 1366 　 28000 　 1577 　 1135 　 1093 　 1786 　 1067 　 1698 　 2002 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图