【mysql 统计表里面6个月的数据】

已于 2022-08-05 16:09:18 修改
阅读量2.2k 收藏
点赞数
文章标签： mysql 数据库 java
于 2022-08-04 15:06:11 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_37029026/article/details/126159747
版权

mysql 统计表里面6个月的数据

1：表格数据

SELECT
	'serial_1' as '表名',
	MAX(IF(c.month = '2022-03', c.count, NULL)) AS '2022-03',
	MAX(IF(c.month = '2022-04', c.count, NULL)) AS '2022-04',
	MAX(IF(c.month = '2022-05', c.count, NULL)) AS '2022-05',
	MAX(IF(c.month = '2022-06', c.count, NULL)) AS '2022-06',
	MAX(IF(c.month = '2022-07', c.count, NULL)) AS '2022-07',
	MAX(IF(c.month = '2022-08', c.count, NULL)) AS '2022-08'
FROM
	(
	select
		DATE_FORMAT(event.create_date, '%Y-%m') as month,
		count(1) as count
	from
		serial_1 event
	where
		ISNULL(event.item_code)
	GROUP BY
		DATE_FORMAT(event.create_date, '%Y-%m')
	order by
		DATE_FORMAT(event.create_date, '%Y-%m') desc
	limit 6 ) c
union all
SELECT
	'container_1' as '表名',
	MAX(IF(c.month = '2022-03', c.count, NULL)) AS '2022-03',
	MAX(IF(c.month = '2022-04', c.count, NULL)) AS '2022-04',
	MAX(IF(c.month = '2022-05', c.count, NULL)) AS '2022-05',
	MAX(IF(c.month = '2022-06', c.count, NULL)) AS '2022-06',
	MAX(IF(c.month = '2022-07', c.count, NULL)) AS '2022-07',
	MAX(IF(c.month = '2022-08', c.count, NULL)) AS '2022-08'
FROM
	(
	select
		DATE_FORMAT(event.create_date, '%Y-%m') as month,
		count(1) as count
	from
		container_1 event
	where
		ISNULL(event.item_code)
	GROUP BY
		DATE_FORMAT(event.create_date, '%Y-%m')
	order by
		DATE_FORMAT(event.create_date, '%Y-%m') desc
	limit 6 ) c
union all
SELECT
	'reel_1' as '表名',
	MAX(IF(c.month = '2022-03', c.count, NULL)) AS '2022-03',
	MAX(IF(c.month = '2022-04', c.count, NULL)) AS '2022-04',
	MAX(IF(c.month = '2022-05', c.count, NULL)) AS '2022-05',
	MAX(IF(c.month = '2022-06', c.count, NULL)) AS '2022-06',
	MAX(IF(c.month = '2022-07', c.count, NULL)) AS '2022-07',
	MAX(IF(c.month = '2022-08', c.count, NULL)) AS '2022-08'
FROM
	(
	select
		DATE_FORMAT(event.create_date, '%Y-%m') as month,
		count(1) as count
	from
		reel_1 event
	where
		ISNULL(event.item_code)
	GROUP BY
		DATE_FORMAT(event.create_date, '%Y-%m')
	order by
		DATE_FORMAT(event.create_date, '%Y-%m') desc
	limit 6 ) c
union all
SELECT
	'batch_1' as '表名',
	MAX(IF(c.month = '2022-03', c.count, NULL)) AS '2022-03',
	MAX(IF(c.month = '2022-04', c.count, NULL)) AS '2022-04',
	MAX(IF(c.month = '2022-05', c.count, NULL)) AS '2022-05',
	MAX(IF(c.month = '2022-06', c.count, NULL)) AS '2022-06',
	MAX(IF(c.month = '2022-07', c.count, NULL)) AS '2022-07',
	MAX(IF(c.month = '2022-08', c.count, NULL)) AS '2022-08'
FROM
	(
	select
		DATE_FORMAT(event.create_date, '%Y-%m') as month,
		count(1) as count
	from
		batch_1 event
	where
		ISNULL(event.item_code)
	GROUP BY
		DATE_FORMAT(event.create_date, '%Y-%m')
	order by
		DATE_FORMAT(event.create_date, '%Y-%m') desc
	limit 6 ) c

2：统计最近6个月的数据，基数是前6个月之前数据之合，后6个月的数据就是每月的数据+基数


select d.month , sum(num) as num from (
   SELECT
	a.month,
	IFNULL(b.num + (select count(1) from batch_1 where ISNULL(item_code) and create_date < DATE_SUB(CURDATE(), INTERVAL 5 MONTH)), 0 + (select count(1) from batch_1 where ISNULL(item_code) and create_date < DATE_SUB(CURDATE(), INTERVAL 5 MONTH))) AS num
FROM
	(
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(CURDATE(), '%Y-%m') AS month ) a
LEFT JOIN (
	SELECT
		DATE_FORMAT(create_date, '%Y-%m') AS DATETIME,
		count(1) AS num
	FROM
		batch_1
	WHERE
		ISNULL(item_code)
	GROUP BY
		DATE_FORMAT(create_date, '%Y-%m') ) b ON
	a.month = b.datetime
UNION ALL
SELECT
	a.month,
	IFNULL(b.num + (select count(1) from reel_1 where ISNULL(item_code) and create_date < DATE_SUB(CURDATE(), INTERVAL 5 MONTH)), 0 +(select count(1) from reel_1 where ISNULL(item_code) and create_date < DATE_SUB(CURDATE(), INTERVAL 5 MONTH))) AS num
FROM
	(
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(CURDATE(), '%Y-%m') AS month ) a
LEFT JOIN (
	SELECT
		DATE_FORMAT(create_date, '%Y-%m') AS DATETIME,
		count(1) AS num
	FROM
		reel_1
	WHERE
		ISNULL(item_code)
	GROUP BY
		DATE_FORMAT(create_date, '%Y-%m') ) b ON
	a.month = b.datetime
UNION ALL
SELECT
	a.month,
	IFNULL(b.num + (select count(1) from container_1 where ISNULL(item_code) and create_date < DATE_SUB(CURDATE(), INTERVAL 5 MONTH)), 0 +(select count(1) from container_1 where ISNULL(item_code) and create_date < DATE_SUB(CURDATE(), INTERVAL 5 MONTH))) AS num
FROM
	(
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(CURDATE(), '%Y-%m') AS month ) a
LEFT JOIN (
	SELECT
		DATE_FORMAT(create_date, '%Y-%m') AS DATETIME,
		count(1) AS num
	FROM
		container_1
	WHERE
		ISNULL(item_code)
	GROUP BY
		DATE_FORMAT(create_date, '%Y-%m') ) b ON
	a.month = b.datetime
	UNION ALL
SELECT
	a.month,
	IFNULL(b.num + (select count(1) from serial_1 where ISNULL(item_code) and create_date < DATE_SUB(CURDATE(), INTERVAL 5 MONTH)), 0 +(select count(1) from serial_1 where ISNULL(item_code) and create_date < DATE_SUB(CURDATE(), INTERVAL 5 MONTH))) AS num
FROM
	(
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m') AS month
UNION ALL
	SELECT
		DATE_FORMAT(CURDATE(), '%Y-%m') AS month ) a
LEFT JOIN (
	SELECT
		DATE_FORMAT(create_date, '%Y-%m') AS DATETIME,
		count(1) AS num
	FROM
		serial_1
	WHERE
		ISNULL(item_code)
	GROUP BY
		DATE_FORMAT(create_date, '%Y-%m') ) b ON
	a.month = b.datetime
	) d
	GROUP BY d.month

3：

 SELECT 
    'batch_1' as 'table',
    a.month,IFNULL(b.num,0) AS num
    FROM ( 
	SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH),'%Y-%m') AS month 
    UNION ALL
	SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH),'%Y-%m') AS month 
    UNION ALL
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH),'%Y-%m') AS month 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH),'%Y-%m') AS month 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m') AS month 
    UNION ALL 
    SELECT DATE_FORMAT(CURDATE(),'%Y-%m') AS month 
    ) a  LEFT JOIN ( 
    SELECT DATE_FORMAT(create_date,'%Y-%m')  AS DATETIME, count(1) AS num
    FROM batch_1
    WHERE ISNULL(item_code)
    GROUP BY DATE_FORMAT(create_date,'%Y-%m') 
    ) b ON a.month = b.datetime
    
    UNION ALL 
    
    SELECT 
    'reel_1' as 'table',
    a.month,IFNULL(b.num,0) AS num
    FROM ( 
	SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH),'%Y-%m') AS month 
    UNION ALL
	SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH),'%Y-%m') AS month 
    UNION ALL
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH),'%Y-%m') AS month 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH),'%Y-%m') AS month 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m') AS month 
    UNION ALL 
    SELECT DATE_FORMAT(CURDATE(),'%Y-%m') AS month 
    ) a  LEFT JOIN ( 
    SELECT DATE_FORMAT(create_date,'%Y-%m')  AS DATETIME, count(1) AS num
    FROM reel_1
    WHERE ISNULL(item_code)
    GROUP BY DATE_FORMAT(create_date,'%Y-%m') 
    ) b ON a.month = b.datetime
那年夏天我们十八
关注
mysql查询6个月的数据_mysql查询当天数据（上周、本月、上个月、距离当前现在6个月的数据）...
weixin_35035699的博客
01-20 3402
下面的ew在实际项目中我因为框架的原因我用了o,所以按具体情况而定， 如果查询昨天的数据的话就减一，如：$sql .= " AND date(o.date_added) = curdate()-1";--------------查"当天"的数据-----------------SELECT ew.orderid, ew.carid, ew.userid, ew.`status`, ew.reser...
MySQL该如何将月增上亿条数据的单表处理方案优雅落地？
Java技术攻略的博客
03-14 1474
看到这里，对于库内分表篇的内容也接近了尾声，有小伙伴也许会疑惑：那如果我每月的数据量更大怎么办呢？比如前面的例子中，如果再投入了多批机器怎么办？每月的数据量达到甚至上亿怎么办？如若你存在这块的顾虑，其实大可不必担心，因为咱们既然可以按月分表，那能否按半月为周期分表呢？能否按星期分表呢？能否以三天、一天为一个维度分表呢？答案显然是可以的，所以数据量无论有多大，都可能按不同的周期来划分表。
Mysql查询最近6个月每月入驻的企业数量
gzkahjl的博客
01-10 1517
public List&lt;Object[]&gt; countForSix() throws DaoException{         String sql = "SELECT\r\n" +                 "    six_month.`month` 'month',\r\n" +                 "    IFNULL(COUNT(t.userId), 0...
MYSQL查询近半年每个月数据，无数据时为0
最新发布
Ccccceno的博客
09-02 437
MYSQL查询近半年每个月数据，无数据时展示为0
mysql查询近六个月的数据
weixin_41066675的博客
06-30 4377
一、查询近六个月 二、绑定业务数据
mysql 查询最近6个月的数据（每个月的数据，没有数据的为0）
weixin_47962813的博客
11-28 3943
mysql 查询最近6个月的数据（每个月的数据，没有数据的为0）
mysql 获取近6个月全量数据
weixin_35729243的博客
07-29 134
MySQL 获取近6个月全量数据 在数据库管理中，我们经常需要对数据进行时间范围的筛选，例如获取近6个月的全量数据。本文将介绍如何使用MySQL查询语句来实现这一需求，并提供相应的代码示例。 流程图 首先，我们通过流程图来概述整个查询过程： #render_12_4281652044-svg{font-family:"tr...
mysql输出近6个月日期列表
chuan9966的专栏
06-01 447
【代码】【无标题】mysql输出近6个月日期列表。
MySQL根据时间统计每日每月每年的数据
shaoyangzhuanyong的博客
07-22 2563
1.表里面的字段是时间格式, 查询用户每月数据 SELECT DATE_FORMAT(starttime, '%Y-%m') AS starts, COUNT(*) FROM ceshi GROUP BY starts; 2.表里面的字段是时间戳, 查询用户每月数据 SELECT FROM_UNIXTIME(createtime,'%Y-%m') AS starts, COUNT(*) FROM ceshi GROUP BY starts; 3.表里面的字段是时间戳, 添加...
mysql按月统计充值提现_MySQL数据篇（五）–SQL对数据进行按月统计，或对数据进行按星期统计...
weixin_32429413的博客
02-03 550
MySQL数据篇(五)–SQL对数据进行按月统计，或对数据进行按星期统计对于所有的需求，当你不知道怎么处理的时候，你就先用最简单的方法，或者说的明白一点，用最原始的方法，先实现业务需求再说。一、对提现队列数据表“ims_checkout_task”进行汇总统计，按月汇总统计每个月的提现总额，提现总次数。1、SQL操作如下：SELECT id,SUM(case when FROM_UNIXTIME(...
MySQL连表查询时一条数据对应多条数据
lslsllsl的博客
11-10 3328
select a.* from user a left join school b on a.id=b.userId 一个用户对应多个学校查出多条数据，把用户提出来，多个学校变成数组查询出来： 例：用户： 用户名：， 用户id：， 用户密码：， schoolChild：{ 学校名： ， 学校地点： }， { 学校名： ，
MySQL数据库在服务器断电后进行数据恢复的过程说明
06-25
### MySQL数据库在服务器断电后的数据恢复过程 #### 背景介绍 在服务器遭遇突发断电等非正常关机情况下，MySQL数据库可能会受到不同程度的影响，导致数据损坏或丢失。为了确保数据的安全性和完整性，及时有效地进行...
mysql 输出最近三个月份值_在mysql中获取最近6个月的值
weixin_42298100的博客
01-19 659
I need to get last six month values from the database. Here is my Fiddle. I need to get the values dynamically. Now February. So I need August to January values from database. If March then the va...
mysql获取近7日（日期）、近6个月和近一年（月份）
xiri_的博客
03-23 4888
mysql获取近7日（日期）、近6个月和近一年（月份） 概述： ​ 在日常的java开发中，我们可能会遇到根据时间统计数据做报表的情况，虽然可以通过java语言来实现报表的功能，但是为了优化代码，我们尽可能将该操作在数据库中实现。 ​ 本篇笔记主要实现的知识点有： 检索近7日的日期、近6个月和近一年的月份 联合数据表实现报表统计，（无数据的日期或月份自动补0） mysql实现检索近7日的日期、近6个月和近一年的月份 1、检索近7日的日期数据 SELECT @s :=@s + 1 as `index`
sql根据日期查询近6月数据(包括当前月往前推五个月)
weixin_56911379的博客
05-10 1087
DATE_FORMAT( create_time, '%Y年%m月' ) AS months,
Mysql查询近半年每个月有多少天 某月有多少天
chuan9966的专栏
07-11 315
【代码】Mysql查询近半年每个月有多少天。
Mysql Sql语句查询 最近一天，一周，一个月，三个月，六个月，六个月以后的数据
Angzush的博客
05-07 3933
Mysql Sql语句查询 最近一天，一周，一个月，三个月，六个月，六个月以后的数据 SELECT count(主键id),qxxz FROM `t_qx_qxjl` where 日期>DATE_SUB(CURDATE(), INTERVAL 1 MONTH) GROUP BY qxxz ORDER BY fxrq //近一个月的 SELECT count(主键id),qxxz F...
mysql中获取当前时间到前六个月的sql语句
m0_59996245的博客
05-26 1268
mysql中获取当前时间到前六个月的sql语句
Mysql查询当前日期前六个月
XLX2339635744的博客
11-18 5007
SELECT DATE_FORMAT( CURDATE(), '%Y-%m' ) AS MONTH UNION SELECT DATE_FORMAT(( CURDATE() - INTERVAL 1 MONTH ), '%Y-%m' ) AS MONTH UNION SELECT DATE_FORMAT(( CURDATE() - INTERVAL 2 MONTH ), '%Y-%m' ) AS MONTH UNION SELECT DATE_FORMAT(( CURDATE() -
mysql查询一张表里面重复的数据，并且将重复的数据显示
05-19
可以使用以下SQL语句查询表中重复的数据： ``` SELECT column1, column2, ..., columnN, COUNT(*) as count FROM table_name GROUP BY column1, column2, ..., columnN HAVING COUNT(*) > 1; ``` 其中，`column...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值