MYSQL分组查询获取每组前n的数据
1、需求说明
比如:从一张数据表里面,根据时间按月分组,获取每个月排名前6的数据
2、样表
CREATE TABLE `electrics` (
`eid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '电能id',
`fid` INT(11) NULL DEFAULT NULL COMMENT '工厂id',
`departname` VARCHAR(50) NULL DEFAULT NULL COMMENT '区域名' COLLATE 'utf8mb3_general_ci',
`energy` DECIMAL(10,2) NULL DEFAULT NULL COMMENT '耗能',
`energytime` DATETIME NULL DEFAULT NULL COMMENT '耗能月份',
`createtime` DATETIME NULL DEFAULT NULL COMMENT '创建时间',
`status` INT(11) NULL DEFAULT NULL COMMENT '状态',
`week` INT(11) NULL DEFAULT '0' COMMENT '第几周数',
PRIMARY KEY (`eid`) USING BTREE,
INDEX `FK_Reference_4` (`fid`) USING BTREE
)
COMMENT='电能'
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=76
;
样例数据
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (1, 1, '中央空调', 1707.18, '2021-11-01 00:00:00', '2021-12-01 16:43:42', 1, 40);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (2, 1, '线装', 8873.79, '2021-11-01 00:00:00', '2021-12-01 16:46:10', 1, 40);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (3, 1, '自动化', 3232.41, '2021-11-01 00:00:00', '2021-12-01 17:23:50', 1, 40);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (9, 1, '办公区', 992.01, '2021-11-01 00:00:00', '2021-12-07 11:47:57', 1, 40);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (10, 1, '空压机', 5121.84, '2021-11-01 00:00:00', '2021-12-07 11:48:18', 1, 40);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (11, 1, '电梯', 53.06, '2021-11-01 00:00:00', '2021-12-07 11:48:43', 1, 40);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (12, 1, '加工中心', 2302.69, '2021-11-01 00:00:00', '2021-12-07 11:49:37', 1, 40);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (13, 1, '自动化', 4304.69, '2021-11-01 00:00:00', '2021-12-07 11:50:18', 1, 41);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (14, 1, '线装', 7030.03, '2021-11-01 00:00:00', '2021-12-07 11:51:32', 1, 41);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (15, 1, '办公区', 1923.07, '2021-11-01 00:00:00', '2021-12-07 11:51:52', 1, 41);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (16, 1, '空压机', 5014.48, '2021-11-01 00:00:00', '2021-12-07 11:52:13', 1, 41);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (17, 1, '电梯', 59.98, '2021-11-01 00:00:00', '2021-12-07 11:53:21', 1, 41);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (18, 1, '中央空调', 230.70, '2021-11-01 00:00:00', '2021-12-07 11:53:43', 1, 41);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (19, 1, '加工中心', 2749.01, '2021-11-01 00:00:00', '2021-12-07 11:54:01', 1, 41);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (20, 1, '自动化', 2063.53, '2021-11-01 00:00:00', '2021-12-07 11:54:42', 1, 42);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (21, 1, '线装', 6151.53, '2021-11-01 00:00:00', '2021-12-07 11:54:59', 1, 42);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (22, 1, '办公区', 1016.62, '2021-11-01 00:00:00', '2021-12-07 11:55:22', 1, 42);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (23, 1, '空压机', 4875.76, '2021-11-01 00:00:00', '2021-12-07 11:55:59', 1, 42);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (24, 1, '电梯', 59.98, '2021-11-01 00:00:00', '2021-12-07 11:56:19', 1, 42);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (25, 1, '中央空调', 92.28, '2021-11-01 00:00:00', '2021-12-07 11:56:40', 1, 42);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (26, 1, '加工中心', 1857.44, '2021-11-01 00:00:00', '2021-12-07 11:57:02', 1, 42);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (27, 1, '自动化', 2045.84, '2021-11-01 00:00:00', '2021-12-07 11:57:27', 1, 43);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (28, 1, '线装', 8853.80, '2021-11-01 00:00:00', '2021-12-07 11:57:47', 1, 43);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (29, 1, '办公区', 1045.84, '2021-11-01 00:00:00', '2021-12-07 11:58:16', 1, 43);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (30, 1, '空压机', 4814.24, '2021-11-01 00:00:00', '2021-12-07 11:58:39', 1, 43);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (31, 1, '电梯', 62.29, '2021-11-01 00:00:00', '2021-12-07 11:58:58', 1, 43);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (32, 1, '中央空调', 138.42, '2021-11-01 00:00:00', '2021-12-07 11:59:18', 1, 43);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (33, 1, '加工中心', 1766.69, '2021-11-01 00:00:00', '2021-12-07 11:59:37', 1, 43);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (34, 1, '自动化', 1932.80, '2021-11-01 00:00:00', '2021-12-07 11:59:53', 1, 44);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (35, 1, '线装', 9201.39, '2021-11-01 00:00:00', '2021-12-07 12:00:09', 1, 44);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (36, 1, '办公区', 1088.14, '2021-11-01 00:00:00', '2021-12-07 12:00:28', 1, 44);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (37, 1, '空压机', 5921.60, '2021-11-01 00:00:00', '2021-12-07 12:00:53', 1, 44);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (38, 1, '电梯', 46.14, '2021-11-01 00:00:00', '2021-12-07 12:03:51', 1, 44);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (39, 1, '中央空调', 322.98, '2021-11-01 00:00:00', '2021-12-07 12:04:14', 1, 44);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (40, 1, '加工中心', 2039.69, '2021-11-01 00:00:00', '2021-12-07 12:04:35', 1, 44);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (41, 1, '中央空调', 1807.18, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (42, 1, '线装', 8763.79, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (43, 1, '自动化', 3232.41, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (44, 1, '办公区', 992.01, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (45, 1, '空压机', 5121.84, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (46, 1, '电梯', 51.06, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (47, 1, '加工中心', 2002.69, '2022-02-28 00:00:00', '2022-03-31 16:57:48', 1, 9);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (48, 1, '中央空调', 1707.18, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (49, 1, '线装', 8873.79, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (50, 1, '自动化', 3132.41, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (51, 1, '办公区', 992.01, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (52, 1, '空压机', 5121.84, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (53, 1, '电梯', 50.06, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (54, 1, '加工中心', 2302.69, '2022-03-01 00:00:00', '2022-03-31 16:59:17', 1, 10);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (55, 1, '中央空调', 1207.18, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (56, 1, '线装', 8873.79, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (57, 1, '自动化', 3232.41, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (58, 1, '办公区', 982.01, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (59, 1, '空压机', 5121.84, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (60, 1, '电梯', 53.06, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (61, 1, '加工中心', 2202.69, '2022-03-14 00:00:00', '2022-03-31 16:59:34', 1, 11);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (62, 1, '中央空调', 1707.18, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (63, 1, '线装', 8873.79, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (64, 1, '自动化', 3232.41, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (65, 1, '办公区', 992.01, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (66, 1, '空压机', 5021.84, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (67, 1, '电梯', 53.06, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (68, 1, '加工中心', 2102.69, '2022-03-21 00:00:00', '2022-03-31 16:59:46', 1, 12);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (69, 1, '中央空调', 1707.18, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (70, 1, '线装', 8873.79, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (71, 1, '自动化', 3232.41, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (72, 1, '办公区', 992.01, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (73, 1, '空压机', 5121.84, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (74, 1, '电梯', 63.06, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13);
INSERT INTO `electrics` (`eid`, `fid`, `departname`, `energy`, `energytime`, `createtime`, `status`, `week`) VALUES (75, 1, '加工中心', 2502.69, '2022-03-28 00:00:00', '2022-03-31 16:59:56', 1, 13);
4、执行语句
SELECT
eid,
departname,
energy,
DATE_FORMAT(energytime,'%Y-%m'),
rn
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(energytime,'%Y-%m') ORDER BY energy DESC) as rn
FROM electrics
WHERE energytime > '2021-01-01 00:00:00'
) ranked_data
WHERE rn <= 6
5、语句解析
1、ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(energytime,'%Y-%m') ORDER BY energy DESC)
MySQL5.0以上排序函数,
DATE_FORMAT(energytime,'%Y-%m'),分组字段【这里是月份】
energy排序字段,根据耗能进行排序
整体:获取每月耗能前6的工厂