MYSQL分组查询获取每组前n的数据

已于 2024-08-02 17:47:34 修改
阅读量165 收藏
点赞数 1
分类专栏： 笔记 文章标签： java spring mysql
于 2024-08-02 17:46:55 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Yanghuoqingliang/article/details/140876742
版权

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的工厂
熊孩子的烦恼
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值