时间戳

首页 > 解决方案 > mysql将值与前一行进行比较

mysql - mysql将值与前一行进行比较

问题描述

我有一个下表mysql。

CREATE TABLE `mdc_meters_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `msn` varchar(100) DEFAULT NULL,
  `cust_id` varchar(100) DEFAULT NULL,
  `p_id` varchar(100) DEFAULT NULL,
  `device_id` varchar(100) DEFAULT NULL,
  `voltage_p1` varchar(100) DEFAULT NULL,
  `voltage_p2` varchar(100) DEFAULT NULL,
  `voltage_p3` varchar(100) DEFAULT NULL,
  `current_p1` varchar(100) DEFAULT NULL,
  `current_p2` varchar(100) DEFAULT NULL,
  `current_p3` varchar(100) DEFAULT NULL,
  `pow_fact_t` varchar(100) DEFAULT NULL,
  `pow_fact_t1` varchar(100) DEFAULT NULL,
  `pow_fact_t2` varchar(100) DEFAULT NULL,
  `pow_fact_t3` varchar(100) DEFAULT NULL,
  `grid_freq` varchar(100) DEFAULT NULL,
  `act_pow_t` varchar(100) DEFAULT NULL,
  `act_pow_t1` varchar(100) DEFAULT NULL,
  `act_pow_t2` varchar(100) DEFAULT NULL,
  `act_pow_t3` varchar(100) DEFAULT NULL,
  `react_pow_t` varchar(100) DEFAULT NULL,
  `react_pow_t1` varchar(100) DEFAULT NULL,
  `react_pow_t2` varchar(100) DEFAULT NULL,
  `react_pow_t3` varchar(100) DEFAULT NULL,
  `apprt_pow_t` varchar(100) DEFAULT NULL,
  `apprt_pow_t1` varchar(100) DEFAULT NULL,
  `apprt_pow_t2` varchar(100) DEFAULT NULL,
  `apprt_pow_t3` varchar(100) DEFAULT NULL,
  `kwh_t` varchar(100) DEFAULT NULL,
  `kwh_t1` varchar(100) DEFAULT NULL,
  `kwh_t2` varchar(100) DEFAULT NULL,
  `Kwh_t3` varchar(100) DEFAULT NULL,
  `fwd_act_enrg_t` varchar(100) DEFAULT NULL,
  `fwd_act_enrg_t1` varchar(100) DEFAULT NULL,
  `fwd_act_enrg_t2` varchar(100) DEFAULT NULL,
  `fwd_act_enrg_t3` varchar(100) DEFAULT NULL,
  `rev_act_enrg_t` varchar(100) DEFAULT NULL,
  `rev_act_enrg_t1` varchar(100) DEFAULT NULL,
  `rev_act_enrg_t2` varchar(100) DEFAULT NULL,
  `rev_act_enrg_t3` varchar(100) DEFAULT NULL,
  `react_enrg_t` varchar(100) DEFAULT NULL,
  `react_enrg_t1` varchar(100) DEFAULT NULL,
  `react_enrg_t2` varchar(100) DEFAULT NULL,
  `react_enrg_t3` varchar(100) DEFAULT NULL,
  `fwd_react_enrg_t` varchar(100) DEFAULT NULL,
  `fwd_react_enrg_t1` varchar(100) DEFAULT NULL,
  `fwd_react_enrg_t2` varchar(100) DEFAULT NULL,
  `fwd_react_enrg_t3` varchar(100) DEFAULT NULL,
  `rev_react_enrg_t` varchar(100) DEFAULT NULL,
  `rev_react_enrg_t1` varchar(100) DEFAULT NULL,
  `rev_react_enrg_t2` varchar(100) DEFAULT NULL,
  `rev_react_enrg_t3` varchar(100) DEFAULT NULL,
  `d_type` varchar(100) DEFAULT NULL,
  `data_date_time` datetime DEFAULT NULL,
  `s_type` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18787 DEFAULT CHARSET=latin1;

/*Data for the table `mdc_meters_data` */
 insert  into `mdc_meters_data`(`id`,`msn`,`cust_id`,`p_id`,`device_id`,`voltage_p1`,`voltage_p2`,`voltage_p3`,`current_p1`,`current_p2`,`current_p3`,`pow_fact_t`,`pow_fact_t1`,`pow_fact_t2`,`pow_fact_t3`,`grid_freq`,`act_pow_t`,`act_pow_t1`,`act_pow_t2`,`act_pow_t3`,`react_pow_t`,`react_pow_t1`,`react_pow_t2`,`react_pow_t3`,`apprt_pow_t`,`apprt_pow_t1`,`apprt_pow_t2`,`apprt_pow_t3`,`kwh_t`,`kwh_t1`,`kwh_t2`,`Kwh_t3`,`fwd_act_enrg_t`,`fwd_act_enrg_t1`,`fwd_act_enrg_t2`,`fwd_act_enrg_t3`,`rev_act_enrg_t`,`rev_act_enrg_t1`,`rev_act_enrg_t2`,`rev_act_enrg_t3`,`react_enrg_t`,`react_enrg_t1`,`react_enrg_t2`,`react_enrg_t3`,`fwd_react_enrg_t`,`fwd_react_enrg_t1`,`fwd_react_enrg_t2`,`fwd_react_enrg_t3`,`rev_react_enrg_t`,`rev_react_enrg_t1`,`rev_react_enrg_t2`,`rev_react_enrg_t3`,`d_type`,`data_date_time`,`s_type`) values (18751,'00209703','','01','03','220','0','0','3.02','0','0','0.85','0.85','0','0','49.9','1.072','1.072','0','0','0.153','0.153','0','0','0.620','0.620','0','0','378.01','378.01','0',NULL,'378.01','378.01','0','0','0','0','0','0','15.11','15.11','0','0','378.01','378.01','0','0','12.12','12.12','0','0','300','2020-08-31 14:00:00','WAPDA'),(18752,'00209703','','01','03','220','0','0','3.04','0','0','0.85','0.85','0','0','49.9','1.072','1.072','0','0','0.153','0.153','0','0','0.621','0.621','0','0','380','380','0',NULL,'380','240','0','0','0','0','0','0','15.15','15.15','0','0','380','380','0','0','12.13','12.13','0','0','300','2020-08-31 14:10:00','WAPDA'),(18753,'00209703','','01','03','220','0','0','3.06','0','0','0.85','0.85','0','0','49.9','1.370','1.370','0','0','0.155','0.155','0','0','0.860','0.860','0','0','382','382','0',NULL,'382','382','0','0','0','0','0','0','15.17','15.17','0','0','382','382','0','0','12.15','12.15','0','0','300','2020-08-31 14:20:00','WAPDA'),(18754,'00209703','','01','03','220','0','0','3.07','0','0','0.85','0.85','0','0','49.9','1.372','1.372','0','0','0.157','0.157','0','0','0.863','0.863','0','0','384','384','0',NULL,'384','384','0','0','0','0','0','0','15.19','15.19','0','0','384','384','0','0','12.17','12.17','0','0','300','2020-08-31 14:30:00','WAPDA'),(18755,'00209704','','01','03','220','0','0','3.09','0','0','0.85','0.85','0','0','49.9','1.375','1.375','0','0','0.161','0.161','0','0','0.865','0.865','0','0','2','2','0',NULL,'2','2','0','0','0','0','0','0','15.21','15.21','0','0','386\r\n','386\r\n','0','0','12.19','12.19','0','0','300','2020-08-31 14:30:00','Generator'),(18756,'00209704','','01','03','220','0','0','3.11','0','0','0.85','0.85','0','0','49.9','1.377','1.377','0','0','0.163','0.163','0','0','0.867','0.867','0','0','4','4','0',NULL,'4','4','0','0','0','0','0','0','15.23','15.23','0','0','388\r\n','388\r\n','0','0','12.21','12.21','0','0','300','2020-08-31 14:40:00','Generator'),(18757,'00209704','','01','03','220','0','0','3.13','0','0','0.85','0.85','0','0','49.9','1.379','1.379','0','0','0.165','0.165','0','0','0.869','0.869','0','0','6','6','0',NULL,'6','6','0','0','0','0','0','0','15.25','15.25','0','0','390\r\n','390\r\n','0','0','12.23','12.23','0','0','300','2020-08-31 14:50:00','Generator'),(18758,'00209704','','01','03','220','0','0','3.15','0','0','0.85','0.85','0','0','49.9','1.381','1.381','0','0','0.167','0.167','0','0','0.871','0.871','0','0','8','8','0',NULL,'8','8','0','0','0','0','0','0','15.27','15.27','0','0','392\r\n','392\r\n','0','0','12.25','12.25','0','0','300','2020-08-31 15:00:00','Generator'),(18759,'00209706','37010114713','01','06','220','0','0','2.01','0','0','0.85','0.85','0','0','49.9','0.418','0.418','0','0','0.025','0.025','0','0','0.419','0.419','0','0','278.01','278.01','0',NULL,'278.01','278.01','0','0','0','0','0','0','8.56','8.56','0','0','278.01','278.01','0','0','6.65','6.65','0','0','300','2020-08-31 14:00:00','Sync Meter'),(18760,'00209706','37010114713','01','06','220','0','0','2.02','0','0','0.85','0.85','0','0','49.9','0.418','0.418','0','0','0.025','0.025','0','0','0.419','0.419','0','0','279','279','0',NULL,'279','279','0','0','0','0','0','0','8.56','8.56','0','0','279','279','0','0','6.65','6.65','0','0','300','2020-08-31 14:10:00','Sync Meter'),(18761,'00209706','37010114713','01','06','220','0','0','2.03','0','0','0.85','0.85','0','0','49.9','0.715','0.715','0','0','0.026','0.026','0','0','0.657','0.657','0','0','280','280','0',NULL,'280','280','0','0','0','0','0','0','8.56','8.56','0','0','280','280','0','0','6.66','6.66','0','0','300','2020-08-31 14:20:00','Sync Meter'),(18762,'00209706','37010114713','01','06','220','0','0','2.03','0','0','0.85','0.85','0','0','49.9','0.716','0.716','0','0','0.027','0.027','0','0','0.659','0.659','0','0','281','281','0',NULL,'281','281','0','0','0','0','0','0','8.57','8.57','0','0','281','281','0','0','6.67','6.67','0','0','300','2020-08-31 14:30:00','Sync Meter'),(18763,'00209707','37010114710','01','07','220','0','0','1.01','0','0','0.85','0.85','0','0','49.9','0.654','0.654','0','0','0.128','0.128','0','0','0.201','0.201','0','0','100','100','0',NULL,'100','100','0','0','0','0','0','0','6.55','6.55','0','0','100','100','0','0','5.47','5.47','0','0','300','2020-08-31 14:00:00','Sync Meter'),(18764,'00209707','37010114710','01','07','220','0','0','1.02','0','0','0.85','0.85','0','0','49.9','0.654','0.654','0','0','0.128','0.128','0','0','0.202','0.202','0','0','101','101','0',NULL,'101','101','0','0','0','0','0','0','6.59','6.59','0','0','101','101','0','0','5.48','5.48','0','0','300','2020-08-31 14:10:00','Sync Meter'),(18765,'00209707','37010114710','01','07','220','0','0','1.03','0','0','0.85','0.85','0','0','49.9','0.655','0.655','0','0','0.129','0.129','0','0','0.203','0.203','0','0','102','102','0',NULL,'102','102','0','0','0','0','0','0','6.61','6.61','0','0','102','102','0','0','5.49','5.49','0','0','300','2020-08-31 14:20:00','Sync Meter'),(18766,'00209707','37010114710','01','07','220','0','0','1.04','0','0','0.85','0.85','0','0','49.9','0.656','0.656','0','0','0.130','0.130','0','0','0.204','0.204','0','0','103','103','0',NULL,'103','103','0','0','0','0','0','0','6.62','6.62','0','0','103','103','0','0','5.50','5.50','0','0','300','2020-08-31 14:30:00','Sync Meter'),(18767,'00209706','37010114713','01','06','220','0','0','2.04','0','0','0.85','0.85','0','0','49.9','0.718','0.718','0','0','0.030','0.030','0','0','0.660','0.660','0','0','282','282','0',NULL,'282','282','0','0','0','0','0','0','8.58','8.58','0','0','282','282','0','0','6.68','6.68','0','0','300','2020-08-31 14:30:00','Sync Meter'),(18768,'00209706','37010114713','01','06','220','0','0','2.05','0','0','0.85','0.85','0','0','49.9','0.719','0.719','0','0','0.031','0.031','0','0','0.661','0.662','0','0','283','283','0',NULL,'283','283','0','0','0','0','0','0','8.59','8.59','0','0','283','283','0','0','6.69','6.69','0','0','300','2020-08-31 14:40:00','Sync Meter'),(18769,'00209706','37010114713','01','06','220','0','0','2.06','0','0','0.85','0.85','0','0','49.9','0.720','0.720','0','0','0.032','0.032','0','0','0.662','0.662','0','0','284','284','0',NULL,'284','284','0','0','0','0','0','0','8.60','8.60','0','0','284','284','0','0','6.70','6.70','0','0','300','2020-08-31 14:50:00','Sync Meter'),(18770,'00209706','37010114713','01','06','220','0','0','2.07','0','0','0.85','0.85','0','0','49.9','0.721','0.721','0','0','0.033','0.033','0','0','0.663','0.663','0','0','285','285','0',NULL,'285','285','0','0','0','0','0','0','8.61','8.61','0','0','285','285','0','0','6.71','6.71','0','0','300','2020-08-31 15:00:00','Sync Meter'),(18771,'00209706','37010114713','01','06','220','0','0','2.08','0','0','0.85','0.85','0','0','49.9','0.722','0.722','0','0','0.034','0.034','0','0','0.664','0.664','0','0','286','286','0',NULL,'286','286','0','0','0','0','0','0','8.62','8.62','0','0','286','286','0','0','6.72','6.72','0','0','300','2020-08-31 15:00:00','Sync Meter'),(18772,'00209706','37010114713','01','06','220','0','0','2.09','0','0','0.85','0.85','0','0','49.9','0.723','0.723','0','0','0.035','0.035','0','0','0.665','0.665','0','0','287','287','0',NULL,'287','287','0','0','0','0','0','0','8.63','8.63','0','0','287','287','0','0','6.73','6.73','0','0','300','2020-08-31 15:10:00','Sync Meter'),(18773,'00209706','37010114713','01','06','220','0','0','2.10','0','0','0.85','0.85','0','0','49.9','0.724','0.724','0','0','0.036','0.036','0','0','0.666','0.666','0','0','288','288','0',NULL,'288','288','0','0','0','0','0','0','8.64','8.64','0','0','288','288','0','0','6.74','6.74','0','0','300','2020-08-31 15:20:00','Sync Meter'),(18774,'00209706','37010114713','01','06','220','0','0','2.11','0','0','0.85','0.85','0','0','49.9','0.724','0.724','0','0','0.037','0.037','0','0','0.667','0.667','0','0','289','289','0',NULL,'289','289','0','0','0','0','0','0','8.65','8.65','0','0','289','289','0','0','6.75','6.75','0','0','300','2020-08-31 15:30:00','Sync Meter'),(18775,'00209707','37010114710','01','07','220','0','0','1.05','0','0','0.85','0.85','0','0','49.9','0.657','0.657','0','0','0.131','0.131','0','0','0.205','0.205','0','0','104','104','0',NULL,'104','104','0','0','0','0','0','0','6.63','6.63','0','0','104','104','0','0','5.51','5.51','0','0','300','2020-08-31 14:30:00','Sync Meter'),(18776,'00209707','37010114710','01','07','220','0','0','1.06','0','0','0.85','0.85','0','0','49.9','0.658','0.658','0','0','0.132','0.132','0','0','0.206','0.206','0','0','105','105','0',NULL,'105','105','0','0','0','0','0','0','6.64','6.64','0','0','105','105','0','0','5.52','5.52','0','0','300','2020-08-31 14:40:00','Sync Meter'),(18777,'00209707','37010114710','01','07','220','0','0','1.07','0','0','0.85','0.85','0','0','49.9','0.659','0.659','0','0','0.133','0.133','0','0','0.207','0.207','0','0','106','106','0',NULL,'106','106','0','0','0','0','0','0','6.65','6.65','0','0','106','106','0','0','5.53','5.53','0','0','300','2020-08-31 14:50:00','Sync Meter'),(18778,'00209707','37010114710','01','07','220','0','0','1.08','0','0','0.85','0.85','0','0','49.9','0.660','0.660','0','0','0.134','0.134','0','0','0.208','0.208','0','0','107','107','0',NULL,'107','107','0','0','0','0','0','0','6.66','6.66','0','0','107','107','0','0','5.54','5.54','0','0','300','2020-08-31 15:00:00','Sync Meter'),(18779,'00209707','37010114710','01','07','220','0','0','1.09','0','0','0.85','0.85','0','0','49.9','0.661','0.661','0','0','0.135','0.135','0','0','0.209','0.209','0','0','108','108','0',NULL,'108','108','0','0','0','0','0','0','6.67','6.67','0','0','108','108','0','0','5.55','5.55','0','0','300','2020-08-31 15:00:00','Sync Meter'),(18780,'00209707','37010114710','01','07','220','0','0','1.10','0','0','0.85','0.85','0','0','49.9','0.662','0.662','0','0','0.136','0.136','0','0','0.210','0.210','0','0','109','109','0',NULL,'109','109','0','0','0','0','0','0','6.68','6.68','0','0','109','109','0','0','5.56','5.56','0','0','300','2020-08-31 15:10:00','Sync Meter'),(18781,'00209707','37010114710','01','07','220','0','0','1.11','0','0','0.85','0.85','0','0','49.9','0.663','0.663','0','0','0.137','0.137','0','0','0.211','0.211','0','0','110','110','0',NULL,'110','110','0','0','0','0','0','0','6.69','6.69','0','0','110','110','0','0','5.57','5.57','0','0','300','2020-08-31 15:20:00','Sync Meter'),(18782,'00209707','37010114710','01','07','220','0','0','1.12','0','0','0.85','0.85','0','0','49.9','0.664','0.664','0','0','0.138','0.138','0','0','0.212','0.212','0','0','111','111','0',NULL,'111','111','0','0','0','0','0','0','6.70','6.70','0','0','111','111','0','0','5.58','5.58','0','0','300','2020-08-31 15:30:00','Sync Meter'),(18783,'00209705',NULL,'01','05','220','0','0','3.17','0','0','0.85','0.85','0','0','49.9','1.383','1.383','0','0','0.169','0.169','0','0','0.873','0.873','0','0','2','2','0',NULL,'2','2','0','0','0','0','0','0','15.29','15.29','0','0','394','394','0','0','12.27','12.27','0','0','300','2020-08-31 15:00:00','Solar'),(18784,'00209705',NULL,'01','05','220','0','0','3.19','0','0','0.85','0.85','0','0','49.9','1.385','1.385','0','0','0.171','0.171','0','0','0.875','0.875','0','0','4','4','0',NULL,'4','4','0','0','0','0','0','0','15.31','15.31','0','0','396','396','0','0','12.29','12.29','0','0','300','2020-08-31 15:10:00','Solar'),(18785,'00209705',NULL,'01','05','220','0','0','3.21','0','0','0.85','0.85','0','0','49.9','1.387','1.387','0','0','0.173','0.173','0','0','0.877','0.877','0','0','6','6','0',NULL,'6','6','0','0','0','0','0','0','15.33','15.33','0','0','398','398','0','0','12.31','12.31','0','0','300','2020-08-31 15:20:00','Solar'),(18786,'00209705',NULL,'01','05','220','0','0','3.23','0','0','0.85','0.85','0','0','49.9','1.388','1.388','0','0','0.175','0.175','0','0','0.879','0.879','0','0','8','8','0',NULL,'8','8','0','0','0','0','0','0','15.35','15.35','0','0','400','400','0','0','12.33','12.33','0','0','300','2020-08-31 15:30:00','Solar'),(18787,'00209703',NULL,'01','03','220','0','0','3.11','0','0','0.85','0.85','0','0','49.9',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'388','388','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2020-08-31 15:30:00','WAPDA'),(18788,'00209703',NULL,'01','03','220','0','0','3.15','0','0','0.85','0.85','0','0','49.9',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'392','392','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2020-08-31 15:40:00','WAPDA'),(18789,'00209703',NULL,'01','03','220','0','0','3.19','0','0','0.85','0.85','0','0','49.9',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'396','396','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2020-08-31 15:50:00','WAPDA'),(18790,'00209703',NULL,'01','03','220','0','0','3.23','0','0','0.85','0.85','0','0','49.9',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'400','400','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2020-08-31 16:00:00','WAPDA'),(18791,'00209706','37010114713','01','06','220','0','0','2.13','0','0','0.85','0.85','0','0','49.9',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'291','291','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2020-08-31 15:30:00','Sync Meter'),(18792,'00209706','37010114713','01','06','220','0','0','2.15','0','0','0.85','0.85','0','0','49.9',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'293','293','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2020-08-31 15:40:00','Sync Meter'),(18793,'00209706','37010114713','01','06','220','0','0','2.17','0','0','0.85','0.85','0','0','49.9',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'295','295','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2020-08-31 15:50:00','Sync Meter'),(18794,'00209706','37010114713','01','06','220','0','0','2.19','0','0','0.85','0.85','0','0','49.9',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'297','297','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2020-08-31 16:00:00','Sync Meter'),(18795,'00209707','37010114710','01','07','220','0','0','1.14','0','0','0.85','0.85','0','0','49.9',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'113','113','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2020-08-31 15:30:00','Sync Meter'),(18796,'00209707','37010114710','01','07','220','0','0','1.16','0','0','0.85','0.85','0','0','49.9',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'115','115','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2020-08-31 15:40:00','Sync Meter'),(18797,'00209707','37010114710','01','07','220','0','0','1.18','0','0','0.85','0.85','0','0','49.9',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'117','117','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2020-08-31 15:50:00','Sync Meter'),(18798,'00209707','37010114710','01','07','220','0','0','1.20','0','0','0.85','0.85','0','0','49.9',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'119','119','0',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2020-08-31 16:00:00','Sync Meter');

在SQL Fiddle中也是如此

在其中我有 4 种类型的源字段名称s_type。WAPDA, Solar, Generator是我的主要来源，并Sync Meter与客户联系在一起。

我想做的事？

每个来源都有一个字段kwh_t。的值kwh_t每10分钟针对特定源增加一次，增加的值是随机的。在 24 小时跨度内，我想检查从kwht_t值开始到特定源的增量停止。例如，我有一个源s_type = WAPDA，并且在几分钟后插入2020-09-05 08:00:00该值，该值递增，因此新的日期时间将在几分钟后再次递增，因此下一个日期时间将是，现在源停止提供数据，我得到了开始和结束日期时间，这是我的记录kwh_t10kwh_t12020-09-05 08:10:00102020-09-05 08:20:002020-09-05 08:00:002020-09-05 08:20:001. 假设一小时后，同一来源再次开始提供数据，直到 30 分钟，所以2nd记录会给我开始2020-09-05 09:20:00和结束2020-09-05 09:50:00等等。所以我想要的输出如下所示

msn     | MAX(kwh_t)| data_date_time(start) | data_date_time(end) | source
==========================================================================
00209703| 300       | 2020-09-05 08:00:00   | 2020-09-05 08:20:00 | WAPDA
00209703| 310       | 2020-09-05 09:20:00   | 2020-09-05 09:50:00 | WAPDA

....

我试过的

我尝试了以下查询

SELECT d.`msn`, MAX(d.`kwh_t`) AS 'Previous KWH', MAX(d2.`kwh_t`) AS 'New  KwH', MIN(d.`data_date_time`), MAX(d.`data_date_time`) FROM `mdc_meters_data` d 
INNER JOIN `mdc_meters_data` d2 ON d2.`id` = d.`id` +1
WHERE d2.`kwh_t` > d.`kwh_t` AND d.`s_type` = 'WAPDA' AND d.`data_date_time` >='2020-09-05 00:00:00' AND d.`data_date_time`<='2020-09-05 23:59:59'
GROUP BY d.`data_date_time`, d2.`data_date_time`

结果是

在此处输入图像描述

结果不是我想要的

注意：值增量是指每10分钟后将插入一条新记录

我该怎么做？

任何帮助将不胜感激。

标签： mysqlselectmax

解决方案

您似乎希望每个 MSN 和每小时一行，因此按以下两个分组：

select 
  msn, 
  max(kwh_t), 
  min(data_date_time), 
  max(data_date_time) 
from mdc_meters_data
where s_type = 'WAPDA' 
and data_date_time >= date '2020-09-05' 
and data_date_time < date '2020-09-06'
group by msn, date(data_date_time), hour(data_date_time)
order by msn, date(data_date_time), hour(data_date_time);

推荐阅读