mysql实战(一)——数据表分组查询最新的数据

系列文章目录

---

---

背景

因为业务的需要，我需要查询位置表中最近一分钟内所有定位器的信息。

---

具体实现过程

1.数据表的建表语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for bbls_location
-- ----------------------------
DROP TABLE IF EXISTS `bbls_location`;
CREATE TABLE `bbls_location`  (
  `id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '编号',
  `tracker_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '定位器Code',
  `tracker_rssi` int(10) NULL DEFAULT NULL COMMENT '定位器RSSI',
  `first_beacon_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '首近信标Code',
  `first_beacon_rssi` int(10) NULL DEFAULT NULL COMMENT '首近信标的RSSI',
  `first_beacon_distance` int(10) NULL DEFAULT NULL COMMENT '首近信标的距离',
  `second_beacon_code` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '次近信标Code',
  `second_beacon_rssi` int(10) NULL DEFAULT NULL COMMENT '次近信标RSSI',
  `second_beacon_distance` int(10) NULL DEFAULT NULL COMMENT '次近信标距离',
  `is_motion` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否运动中',
  `is_sos` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '是否SOS求救',
  `tracker_electricity` decimal(10, 2) NULL DEFAULT NULL COMMENT '定位器电量',
  `location_time` datetime(0) NOT NULL COMMENT '定位时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '位置信息表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

效果图

2.思路

1.分组查询数据，并查询每组数据中最新时间
2.将1中查到的结果与需要查询的表联查，条件为时间=1中最新时间且分组条件相等
3.将查询出来的结果根据查询时间条件再次过滤

3.SQL语句

分组查询最近1分钟内的数据

SELECT
	bb.* 
FROM
	bbls_location bb,
	( SELECT a.*, max( a.location_time ) AS maxTime FROM bbls_location a GROUP BY a.tracker_code ) AS cd 
WHERE
	bb.location_time = cd.maxTime 
	AND bb.tracker_code = cd.tracker_code 
and
	bb.location_time >= DATE_SUB(NOW(),interval 5 minute)

4.SQL语句的解析

具体见下图

5.查询结果

---

总结

文章到这里就结束了，欢迎各位网友指点，点评。