station_log的结构和数据
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for station_log
-- ----------------------------
DROP TABLE IF EXISTS `station_log`;
CREATE TABLE `station_log` (
`id` int(0) NOT NULL,
`station_id` int(0) NULL DEFAULT NULL,
`update_time` datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of station_log
-- ----------------------------
INSERT INTO `station_log` VALUES (1, 1, '2023-01-01 00:00:00');
INSERT INTO `station_log` VALUES (2, 2, '2023-01-02 00:00:00');
INSERT INTO `station_log` VALUES (3, 1, '2023-01-02 00:00:00');
INSERT INTO `station_log` VALUES (4, 3, '2023-01-03 00:00:00');
SET FOREIGN_KEY_CHECKS = 1;
每个站点都有若干日志,现在要查询表中时间最新的日志信息
1.查询每个站点的最新数据
使用exists
SELECT
*
FROM
station_log c
WHERE
NOT EXISTS (
SELECT
*
FROM station_log
WHERE station_log.station_id = c.station_id
AND station_log.update_time > c.UPDATE_time
)
运行SQL文件时,外查询找到id为1的日志信息,然后内循环发现存在where条件的数据,即存在station_id为1且时间更新的数据,于是not exists为false,该条数据不显示。
当外循环找到id为3的日志,发现内循环不存在where条件的数据,于是not exists为true,该条数据显示。
2.查询某个站点的最新数据
嵌套查询
SELECT
*
FROM(
SELECT
*
FROM
station_log c
WHERE
NOT EXISTS (
SELECT
*
FROM station_log
WHERE station_log.station_id = c.station_id
AND station_log.update_time > c.UPDATE_time
)
) AS TEMPTABLE
WHERE
TEMPTABLE.station_id = 1
觉得本篇对您有帮助的,请留下一个大大的👍🏼赞,这对我真的很重要!