【SQL】查询表中最新的数据

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