从每组数据中查出特定的一条数据,比如最新时间,时间差等等

从每组数据中查出特定的一条数据,比如最新时间,时间差等等

1 建表语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `assetId` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test
-- ----------------------------
BEGIN;
INSERT INTO `test` VALUES (1, '11111', 'test1', 'aaaa', '2020-06-23 16:48:08');
INSERT INTO `test` VALUES (2, '11111', 'test1', 'bbbc', '2020-06-24 16:49:38');
INSERT INTO `test` VALUES (3, '11111', 'test1', 'cccc', '2020-06-27 16:49:47');
INSERT INTO `test` VALUES (4, '2222', 'test2', 'AA', '2020-06-02 16:50:14');
INSERT INTO `test` VALUES (5, '2222', 'test2', 'BB', '2020-06-03 16:50:57');
INSERT INTO `test` VALUES (6, '2222', 'test2', 'CC', '2020-06-30 16:51:05');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

2 目标

根据时间点查询距离时间点最近的那条资产状态

assetId		name	  createTime     value

1111		status1     6.10          1.0
1111		status1     6.11          1.1
1111		status1     6.16          1.2
1111		status1     6.18          1.3

2222		status2     6.7           2.0
2222		status2     6.10          2.1
2222		status2     6.19          2.2
2222		status2     6.28          2.3

根据时间点,6.12查离该时间点最近的value,结果

1111		status1     6.11          1.1
2222		status2     6.10       2.1

3 语句

 SELECT
 * 
FROM
 test 
WHERE
 abs( DATE_FORMAT( time, '%Y%c%d' ) - DATE_FORMAT( '2020-06-25', '%Y%c%d' ) ) IN ( SELECT min( abs( DATE_FORMAT( time, '%Y%c%d' ) - DATE_FORMAT( '2020-06-25', '%Y%c%d' ) ) ) FROM test GROUP BY `name`,'assetId')
 

总结

包括查出每组数据中最新时间的数据,都可使用该思想