2022-05-21 10:33:55 来源 : 软件开发网
本周
前七天
本月
本年按月展示
本周SELECT b.item,IFNULL(a.COUNT,0) AS VALUEFROM ( 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) bLEFT 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 VALUEFROM ( 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) bLEFT 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统计量按天展示内容请搜索软件开发网以前的文章或继续浏览下面的相关文章希望大家以后多多支持软件开发网！
关于我们 加入我们 广告服务 网站地图
All Rights Reserved, Copyright 2004-2020 www.830036.com
如有意见请与我们联系 邮箱：435 227 67@qq.com
营业执照公示信息