mysql8.0 使用窗口函数 分组数据 取最大,当天排序优先中午

select * from(
  select row_number() over (partition by 分组字段 order by 排序字段 desc) as rn,u.*
  from 表名 u 
) t where t.rn=1;

测试表结构

CREATE TABLE `t_panorama_image` (
  `id` char(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键',
  `device_id` bigint NOT NULL COMMENT '设备ID',
  `create_time` datetime(3) DEFAULT NULL COMMENT '新增时间',
  `total_worker_amount` int DEFAULT NULL COMMENT '施工面总人数',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `IDX_DEVICE_ID_CODE` (`device_id`,`create_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='全景图';

插入测试数据

INSERT INTO `t_panorama_image` VALUES ('1dc485bcc43d58f4f8dae33018a3709c', '2205817', '2021-07-02 06:31:08.000', '4');
INSERT INTO `t_panorama_image` VALUES ('3b22d910a65e5ba444d1563736d7b8a5', '2205817', '2021-07-02 12:46:45.000', '3');
INSERT INTO `t_panorama_image` VALUES ('693c4d6a302905e164880600ca2dab04', '2205817', '2021-07-01 06:00:31.000', '1');
INSERT INTO `t_panorama_image` VALUES ('9f73ee77d99799e042d82b4d4841dd31', '2205817', '2021-06-29 13:43:14.000', '1');
INSERT INTO `t_panorama_image` VALUES ('b04823017e216f3b7d71525f586ca2e6', '2205817', '2021-06-30 06:00:35.000', '1');
INSERT INTO `t_panorama_image` VALUES ('fb9b2ccbf4ca2493909dda8d5a03f52f', '2205817', '2021-07-01 12:32:08.000', '2');

select * from t_panorama_image ORDER BY create_time desc

分组取 total_worker_amount 最大

select * from (
select row_number() over (partition by DATE_FORMAT(create_time, "%Y-%m-%d") order by total_worker_amount desc) as rn,u.*
 from t_panorama_image u 
where device_id = 2205817
   	AND create_time >= '2021-05-25 19:13:54'
   	AND create_time < '2021-07-02 12:59:58'
) t where t.rn = 1
-- 间隔3天数据
-- and DATEDIFF( create_time , "2021-05-25") % 3 = 0

有中午优先取中午, 无中午取早上

select * from (
select row_number() over (partition by DATE_FORMAT(create_time, "%Y-%m-%d") order by DATE_FORMAT( u.create_time2, "%Y-%m-%d %H:%i:%s") asc) as rn,u.*
 from (
	SELECT *,CASE 
	WHEN DATE_FORMAT( create_time, "%H")>= 10 and DATE_FORMAT( create_time, "%H")<=18  THEN DATE_FORMAT( create_time, "%Y-%m-%d 00:00:00")
		ELSE
		create_time
	 END 
		as create_time2
	FROM t_panorama_image
where device_id = 2205817
   	AND create_time >= '2021-05-25 19:13:54'
   	AND create_time < '2021-07-02 12:59:58') u
) t where t.rn = 1
and DATEDIFF( create_time , "2021-05-25") % 3 = 0