mysql获取最近30天的数据sql

最新推荐文章于 2023-12-21 09:00:00 发布

weixin_45059337

最新推荐文章于 2023-12-21 09:00:00 发布

阅读量2k

点赞数

文章标签： mysql java

本文链接：https://blog.csdn.net/weixin_45059337/article/details/102539162

版权

#获取最近30天的数据
SELECT DATE_FORMAT(create_time,"%Y-%m-%d") AS `date`,COUNT(*) AS `value`
FROM t_alarm_info 
WHERE `status` = 'ALARMING' AND MONTH(create_time) > MONTH(NOW())-30
GROUP BY DAY(create_time)

#获取最近30天的数据，为了Java代码做准备
SELECT create_time AS `date`,COUNT(*) AS `value` 
FROM `t_alarm_info`
WHERE `status` = 'ALARMING'
AND  create_time>='2019-09-08'   #注意：代码层应该设置为08:23:59:59秒
AND create_time <= '2019-10-13'  #注意：代码层应该设置为13:23:59:59秒
GROUP BY DAY(create_time)        #可以不要分组
order by DAY(create_time)

#mysql数据库表
CREATE TABLE `t_alarm_info` (
  `id` varchar(36) NOT NULL COMMENT '主键',
  `store_id` varchar(36) NOT NULL DEFAULT '' COMMENT '仓库id',
  `store_name` varchar(36) NOT NULL DEFAULT '' COMMENT '仓库名称',
  `alarm_type` varchar(36) NOT NULL DEFAULT '' COMMENT '告警类型',
  `status` varchar(36) NOT NULL DEFAULT '' COMMENT '告警状态',
  `company_id` varchar(36) NOT NULL DEFAULT '' COMMENT '企业ID',
  `company_name` varchar(36) NOT NULL DEFAULT '' COMMENT '企业名称',
  `stock_id` varchar(36) NOT NULL DEFAULT '' COMMENT '库存ID',
  `stock_type` varchar(36) NOT NULL DEFAULT '' COMMENT '库存类型',
  `area_id` varchar(36) NOT NULL DEFAULT '' COMMENT '区域id',
  `device_id` varchar(36) NOT NULL DEFAULT '' COMMENT '设备ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='告警信息表';

/*Data for the table `t_alarm_info` */

insert  into `t_alarm_info`(`id`,`store_id`,`store_name`,`alarm_type`,`status`,`company_id`,`company_name`,`stock_id`,`stock_type`,`area_id`,`device_id`,`create_time`,`update_time`) values ('1','1','上海仓储服务','PLEDGE','MIS_REPORT','1','上海钢厂','1','钢铁','1','xxxxS_203233e22f69','2019-09-24 14:26:42','2019-09-30 10:05:25'),('10','1','上海仓储服务','PLEDGE','DONE','1','上海钢厂','1','钢铁','1','xxxxS_203233e22f69','2019-09-24 14:26:42','2019-10-08 14:21:17'),('11','1','上海仓储服务','PLEDGE','ALARMING','1','上海钢厂','1','钢铁','2','xxxxS_203233e22f69','2019-09-24 14:26:42','2019-10-08 14:21:18')