sql思路分享,批量查找月最后一天

建表语句:

CREATE TABLE `p_light_device` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `pv` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '光伏电压',
  `light_power` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '光伏功率',
  `total_usage` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '总用电量',
  `year` int(11) NOT NULL COMMENT '年份',
  `quarter` int(11) NOT NULL COMMENT '季度',
  `month` int(11) NOT NULL COMMENT '月份',
  `week` int(11) NOT NULL COMMENT '周',
  `day` int(11) NOT NULL COMMENT '天',
  `create_by` varchar(64) CHARACTER SET utf8 DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_by` varchar(64) CHARACTER SET utf8 DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `light_usage` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '光伏用电量',
  `sn` varchar(70) DEFAULT NULL COMMENT '设备唯一标识',
  `data_id` varchar(50) DEFAULT NULL COMMENT '数据id',
  `country_usage` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '国家电网电量',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_createTime` (`create_time`) USING BTREE,
  KEY `idx_sn` (`sn`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=157460 DEFAULT CHARSET=utf8mb4 COMMENT='光伏设备信息表';

这个表每隔一段时间会插入一条累计的数据,现在想将每一个月的最后一天的数据查找出来

SELECT pd.*
FROM p_light_device pd
JOIN (
    SELECT year, month, day, MAX(create_time) AS max_create_time
    FROM p_light_device
    GROUP BY year, month, day
) AS max_dates
ON pd.year = max_dates.year
AND pd.month = max_dates.month
AND pd.day = max_dates.day
AND pd.create_time = max_dates.max_create_time
ORDER BY pd.create_time;