mysql 分组，获取每组前几条记录数据
最近项目中碰到一个统计最近五周不同区域用电量报表。
上面是需要，实际转为mysql中对数据进行分组，然后再获取每组的前几条记录。
功能效果如下：
下面是表结构：
表结构和表数据sql脚本如下：
DROP TABLE IF EXISTS `electrics`; CREATE TABLE `electrics` ( `eid` int(0) NOT NULL AUTO_INCREMENT COMMENT '电能id', `fid` int(0) NULL DEFAULT NULL COMMENT '工厂id', `departname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '区域名', `energy` decimal(10, 2) NULL DEFAULT NULL COMMENT '耗能', `energytime` datetime(0) NULL DEFAULT NULL COMMENT '耗能月份', `createtime` datetime(0) NULL DEFAULT NULL COMMENT '创建时间', `status` int(0) NULL DEFAULT NULL COMMENT '状态', `week` int(0) NULL DEFAULT 0 COMMENT '第几周数', PRIMARY KEY (`eid`) USING BTREE, INDEX `FK_Reference_4`(`fid`) USING BTREE, CONSTRAINT `FK_Reference_4` FOREIGN KEY (`fid`) REFERENCES `factorys` (`fid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 76 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '电能' ROW_FORMAT = Dynamic;
表数据：
INSERT INTO `electrics` VALUES (1, 1, '中央空调', 1707.18, '2021-11-01 00:00:00', '2021-12-01 16:43:42', 1, 40); INSERT INTO `electrics` VALUES (2, 1, '线装', 8873.79, '2021-11-01 00:00:00', '2021-12-01 16:46:10', 1, 40); INSERT INTO `electrics` VALUES (3, 1, '自动化', 3232.41, '2021-11-01 00:00:00', '2021-12-01 17:23:50', 1, 40);INSERT INTO `electrics` VALUES (9, 1, '办公区', 992.01, '2021-11-01 00:00:00', '2021-12-07 11:47:57', 1, 40); INSERT INTO `electrics` VALUES (10, 1, '空压机', 5121.84, '2021-11-01 00:00:00', '2021-12-07 11:48:18', 1, 40); INSERT INTO `electrics` VALUES (11, 1, '电梯', 53.06, '2021-11-01 00:00:00', '2021-12-07 11:48:43', 1, 40); INSERT INTO `electrics` VALUES (12, 1, '加工中心', 2302.69, '2021-11-01 00:00:00', '2021-12-07 11:49:37', 1, 40); INSERT INTO `electrics` VALUES (13, 1, '自动化', 4304.69, '2021-11-01 00:00:00', '2021-12-07 11:50:18', 1, 41); INSERT INTO `electrics` VALUES (14, 1, '线装', 7030.03, '2021-11-01 00:00:00', '2021-12-07 11:51:32', 1, 41); INSERT INTO `electrics` VALUES (15, 1, '办公区', 1923.07, '2021-11-01 00:00:00', '2021-12-07 11:51:52', 1, 41); INSERT INTO `electrics` VALUES (16, 1, '空压机', 5014.48, '2021-11-01 00:00:00', '2021-12-07 11:52:13', 1, 41); INSERT INTO `electrics` VALUES (17, 1, '电梯', 59.98, '2021-11-01 00:00:00', '2021-12-07 11:53:21', 1, 41); INSERT INTO `electrics` VALUES (18, 1, '中央空调', 230.70, '2021-11-01 00:00:00', '2021-12-07 11:53:43', 1, 41); INSERT INTO `electrics` VALUES (19, 1, '加工中心', 2749.01, '2021-11-01 00:00:00', '2021-12-07 11:54:01', 1, 41); INSERT INTO `electrics` VALUES (20, 1, '自动化', 2063.53, '2021-11-01 00:00:00', '2021-12-07 11:54:42', 1, 42); INSERT INTO `electrics` VALUES (21, 1, '线装', 6151.53, '2021-11-01 00:00:00', '2021-12-07 11:54:59', 1, 42); INSERT INTO `electrics` VALUES (22, 1, '办公区', 1016.62, '2021-11-01 00:00:00', '2021-12-07 11:55:22', 1, 42); INSERT INTO `electrics` VALUES (23, 1, '空压机', 4875.76, '2021-11-01 00:00:00', '2021-12-07 11:55:59', 1, 42); INSERT INTO `electrics` VALUES (24, 1, '电梯', 59.98, '2021-11-01 00:00:00', '2021-12-07 11:56:19', 1, 42); INSERT INTO `electrics` VALUES (25, 1, '中央空调', 92.28, '2021-11-01 00:00:00', '2021-12-07 11:56:40', 1, 42); INSERT INTO `electrics` VALUES (26, 1, '加工中心', 1857.44, '2021-11-01 00:00:00', '2021-12-07 11:57:02', 1, 42); INSERT INTO `electrics` VALUES (27, 1, '自动化', 2045.84, '2021-11-01 00:00:00', '2021-12-07 11:57:27', 1, 43); INSERT INTO `electrics` VALUES (28, 1, '线装', 8853.80, '2021-11-01 00:00:00', '2021-12-07 11:57:47', 1, 43); INSERT INTO `electrics` VALUES (29, 1, '办公区', 1045.84, '2021-11-01 00:00:00', '2021-12-07 11:58:16', 1, 43); INSERT INTO `electrics` VALUES (30, 1, '空压机', 4814.24, '2021-11-01 00:00:00', '2021-12-07 11:58:39', 1, 43); INSERT INTO `electrics` VALUES (31, 1, '电梯', 62.29, '2021-11-01 00:00:00', '2021-12-07 11:58:58', 1, 43); INSERT INTO `electrics` VALUES (32, 1, '中央空调', 138.42, '2021-11-01 00:00:00', '2021-12-07 11:59:18', 1, 43); INSERT INTO `electrics` VALUES (33, 1, '加工中心', 1766.69, '2021-11-01 00:00:00', '2021-12-07 11:59:37', 1, 43); INSERT INTO `electrics` VALUES (34, 1, '自动化', 1932.80, '2021-11-01 00:00:00', '2021-12-07 11:59:53', 1, 44); INSERT INTO `electrics` VALUES (35, 1, '线装', 9201.39, '2021-11-01 00:00:00', '2021-12-07 12:00:09', 1, 44); INSERT INTO `electrics` VALUES (36, 1, '办公区', 1088.14, '2021-11-01 00:00:00', '2021-12-07 12:00:28', 1, 44); INSERT INTO `electrics` VALUES (37, 1, '空压机', 5921.60, '2021-11-01 00:00:00', '2021-12-07 12:00:53', 1, 44); INSERT INTO `electrics` VALUES (38, 1, '电梯', 46.14, '2021-11-01 00:00:00', '2021-12-07 12:03:51', 1, 44); INSERT INTO `electrics` VALUES (39, 1, '中央空调', 322.98, '2021-11-01 00:00:00', '2021-12-07 12:04:14', 1, 44); INSERT INTO `electrics` VALUES (40, 1, '加工中心', 2039.69, '2021-11-01 00:00:00', '2021-12-07 12:04:35', 1, 44); INSERT INTO `electrics` VALUES (41, 1, '中央空调', 1807.18, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9); INSERT INTO `electrics` VALUES (42, 1, '线装', 8763.79, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9); INSERT INTO `electrics` VALUES (43, 1, '自动化', 3232.41, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9); INSERT INTO `electrics` VALUES (44, 1, '办公区', 992.01, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9); INSERT INTO `electrics` VALUES (45, 1, '空压机', 5121.84, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9); INSERT INTO `electrics` VALUES (46, 1, '电梯', 51.06, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9); INSERT INTO `electrics` VALUES (47, 1, '加工中心', 2002.69, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9); INSERT INTO `electrics` VALUES (48, 1, '中央空调', 1707.18, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10); INSERT INTO `electrics` VALUES (49, 1, '线装', 8873.79, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10); INSERT INTO `electrics` VALUES (50, 1, '自动化', 3132.41, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10); INSERT INTO `electrics` VALUES (51, 1, '办公区', 992.01, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10); INSERT INTO `electrics` VALUES (52, 1, '空压机', 5121.84, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10); INSERT INTO `electrics` VALUES (53, 1, '电梯', 50.06, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10); INSERT INTO `electrics` VALUES (54, 1, '加工中心', 2302.69, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10); INSERT INTO `electrics` VALUES (55, 1, '中央空调', 1207.18, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11); INSERT INTO `electrics` VALUES (56, 1, '线装', 8873.79, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11); INSERT INTO `electrics` VALUES (57, 1, '自动化', 3232.41, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11); INSERT INTO `electrics` VALUES (58, 1, '办公区', 982.01, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11); INSERT INTO `electrics` VALUES (59, 1, '空压机', 5121.84, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11); INSERT INTO `electrics` VALUES (60, 1, '电梯', 53.06, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11); INSERT INTO `electrics` VALUES (61, 1, '加工中心', 2202.69, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11); INSERT INTO `electrics` VALUES (62, 1, '中央空调', 1707.18, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12); INSERT INTO `electrics` VALUES (63, 1, '线装', 8873.79, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12); INSERT INTO `electrics` VALUES (64, 1, '自动化', 3232.41, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12); INSERT INTO `electrics` VALUES (65, 1, '办公区', 992.01, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12); INSERT INTO `electrics` VALUES (66, 1, '空压机', 5021.84, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12); INSERT INTO `electrics` VALUES (67, 1, '电梯', 53.06, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12); INSERT INTO `electrics` VALUES (68, 1, '加工中心', 2102.69, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12); INSERT INTO `electrics` VALUES (69, 1, '中央空调', 1707.18, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13); INSERT INTO `electrics` VALUES (70, 1, '线装', 8873.79, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13); INSERT INTO `electrics` VALUES (71, 1, '自动化', 3232.41, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13); INSERT INTO `electrics` VALUES (72, 1, '办公区', 992.01, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13); INSERT INTO `electrics` VALUES (73, 1, '空压机', 5121.84, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13); INSERT INTO `electrics` VALUES (74, 1, '电梯', 63.06, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13); INSERT INTO `electrics` VALUES (75, 1, '加工中心', 2502.69, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13);
最后sql脚本如下：
select departname, week, sum(energy) total from ( select * from electrics e where week <> 0 and 5>(select count(1) from electrics el where el.departname=e.departname and el.energytime>e.energytime) ) t group by departname,week order by departname,week asc
对上面脚本分析如下：下面的脚本是获取各个区域的最近五周的总用电量。where语句中的数量表示分组后要获取的记录条数。
select * from electrics e where week <> 0 and 5>(select count(1) from electrics el where el.departname=e.departname and el.energytime>e.energytime)
