查询不同分组下前N条数据

SQL:

DROP TABLE IF EXISTS `sys_device`;
CREATE TABLE `sys_device`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `sn` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'sn码',
  `max_volatge` float NULL DEFAULT NULL COMMENT '最大电压',
  `min_volatge` float NULL DEFAULT NULL COMMENT '最小电压',
  `status` int(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 1 COMMENT '状态',
  `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '设备采集数据表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sys_device
-- ----------------------------
INSERT INTO `sys_device` VALUES (1, 'sn001', 1, 1, 0, '2022-12-01 13:30:12');
INSERT INTO `sys_device` VALUES (2, 'sn001', 2, 2, 0, '2022-12-01 13:31:12');
INSERT INTO `sys_device` VALUES (3, 'sn001', 3, 3, 0, '2022-12-01 13:32:12');
INSERT INTO `sys_device` VALUES (4, 'sn001', 4, 4, 0, '2022-12-01 13:33:12');
INSERT INTO `sys_device` VALUES (5, 'sn001', 5, 5, 0, '2022-12-01 13:34:12');
INSERT INTO `sys_device` VALUES (6, 'sn002', 1, 1, 0, '2022-12-01 13:30:12');
INSERT INTO `sys_device` VALUES (7, 'sn002', 2, 2, 0, '2022-12-01 13:31:12');
INSERT INTO `sys_device` VALUES (8, 'sn002', 3, 3, 0, '2022-12-01 13:32:12');
INSERT INTO `sys_device` VALUES (9, 'sn002', 4, 4, 0, '2022-12-01 13:33:12');
INSERT INTO `sys_device` VALUES (10, 'sn002', 5, 5, 0, '2022-12-01 13:34:12');
INSERT INTO `sys_device` VALUES (11, 'sn003', 1, 1, 0, '2022-12-01 13:30:12');
INSERT INTO `sys_device` VALUES (12, 'sn003', 2, 2, 0, '2022-12-01 13:31:12');
INSERT INTO `sys_device` VALUES (13, 'sn003', 3, 3, 0, '2022-12-01 13:32:12');
INSERT INTO `sys_device` VALUES (14, 'sn003', 4, 4, 0, '2022-12-01 13:33:12');
INSERT INTO `sys_device` VALUES (15, 'sn003', 5, 5, 0, '2022-12-01 13:34:12');

1. 筛选出每个分组下最新1条数据

按照某一字段分组

提取分组中的1或者N条

按某个字段倒叙

SELECT 
	sn,
	`status`,
	MAX(create_time)
FROM 
	`sys_device`
GROUP BY sn

原因：
Mysql默认开启了：ONLY_FULL_GROUP_BY
SELECT中的列只能是GROUP BY中出现的列、其他字段的值是随机一条数据的值

1.1 临时表关联查询

SELECT
	t.*
FROM
	`sys_device` as t,
	(
		SELECT 
			sn,
			MAX(create_time) as create_time
		FROM 
			`sys_device`
		GROUP BY sn
	) as t1
where t.sn = t1.sn AND t.create_time = t1.create_time

1.2 子查询

SELECT
	* 
FROM
	sys_device 
WHERE
	id IN ( SELECT max( id ) FROM sys_device GROUP BY sn );

1.3 right join

right join方式：可以减少外层的数据集

where sn is not null：可以使group by sn时使用索引

SELECT
	a.* 
FROM
	sys_device AS a
RIGHT JOIN 
	( 
		SELECT 
			sn, 
			max( create_time ) AS max_create_time 
		FROM 
			sys_device 
		WHERE sn IS NOT NULL
		GROUP BY sn 
	) AS b ON a.sn = b.sn AND a.create_time = b.max_create_time;

1.4 group by

SELECT
	id,
	sn,
	`status`,
	create_time 
FROM
	sys_device AS t 
GROUP BY
	id,
	sn,
	`status`,
	create_time 
HAVING
	create_time =(
	SELECT
		max( create_time ) 
	FROM
		sys_device 
	WHERE
		sn = t.sn 
	);

1.5 JOIN + COUNT(*)

查询每个sn最新的3条数据，3变成n：查询每个sn最新的那条数据

SELECT
	* 
FROM
	sys_device AS a 
WHERE
	( 
		SELECT 
			count(*) 
		FROM 
			sys_device AS b 
		WHERE a.sn = b.sn 
		AND a.create_time < b.create_time 
	) < 3;