闲来无事，写一个排序分页加去重的逻辑

实际场景中可能需要对t2表中的字段加筛选条件，这里进行了简化。

 

-- ----------------------------
-- Table structure for t_main_group
-- ----------------------------
DROP TABLE IF EXISTS `t_main_group`;
CREATE TABLE `t_main_group`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `code` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `name` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `status` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `start_date` datetime NULL DEFAULT NULL,
  `crt_date` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_main_group
-- ----------------------------
INSERT INTO `t_main_group` VALUES ('1', '1', '1', '1', '2024-12-26 21:45:03', '2024-12-26 21:45:09');
INSERT INTO `t_main_group` VALUES ('2', '2', '2', '1', '2024-12-26 21:45:21', '2024-12-26 21:45:24');
INSERT INTO `t_main_group` VALUES ('3', '3', '3', '1', '2024-12-26 21:46:06', '2024-12-26 21:46:10');
INSERT INTO `t_main_group` VALUES ('4', '4', '4', '3', '2024-12-26 21:46:27', '2024-12-26 21:46:37');
INSERT INTO `t_main_group` VALUES ('5', '5', '5', '2', '2024-12-26 21:46:50', '2024-12-26 21:46:54');
INSERT INTO `t_main_group` VALUES ('6', '6', '6', '2', '2024-12-26 21:47:06', '2024-12-26 21:47:13');
INSERT INTO `t_main_group` VALUES ('7', '7', '7', '2', '2024-12-26 21:47:31', '2024-12-26 21:47:36');

-- ----------------------------
-- Table structure for t_sub_group
-- ----------------------------
DROP TABLE IF EXISTS `t_sub_group`;
CREATE TABLE `t_sub_group`  (
  `id` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `main_id` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `admin_user` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `stru_id` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_sub_group
-- ----------------------------
INSERT INTO `t_sub_group` VALUES ('1', '1', 'aa', '001');
INSERT INTO `t_sub_group` VALUES ('10', '2', 'aa', '008');
INSERT INTO `t_sub_group` VALUES ('11', '3', 'dd', '009');
INSERT INTO `t_sub_group` VALUES ('12', '3', 'ff', '010');
INSERT INTO `t_sub_group` VALUES ('13', '4', 'gg', '011');
INSERT INTO `t_sub_group` VALUES ('14', '4', 'gg', '012');
INSERT INTO `t_sub_group` VALUES ('15', '5', 'hh', '013');
INSERT INTO `t_sub_group` VALUES ('16', '5', 'hh', '014');
INSERT INTO `t_sub_group` VALUES ('17', '6', 'ii', '015');
INSERT INTO `t_sub_group` VALUES ('18', '6', 'jj', '016');
INSERT INTO `t_sub_group` VALUES ('19', '7', 'kk', '017');
INSERT INTO `t_sub_group` VALUES ('2', '1', 'aa', '002');
INSERT INTO `t_sub_group` VALUES ('20', '7', 'll', '018');
INSERT INTO `t_sub_group` VALUES ('21', '7', 'kk', '019');
INSERT INTO `t_sub_group` VALUES ('3', '1', 'aa', '003');
INSERT INTO `t_sub_group` VALUES ('4', '1', 'bb', '004');
INSERT INTO `t_sub_group` VALUES ('5', '1', 'cc', '005');
INSERT INTO `t_sub_group` VALUES ('6', '1', 'aa', '006');
INSERT INTO `t_sub_group` VALUES ('7', '2', 'aa', '003');
INSERT INTO `t_sub_group` VALUES ('8', '2', 'bb', '004');
INSERT INTO `t_sub_group` VALUES ('9', '2', 'cc', '007');


最后的查询sql如下：

select t1.id, max(t1.`code`) as code, max(t1.`name`) as name, max(date_format(t1.start_date, '%Y-%m-%d')), group_concat(distinct t2.admin_user separator '、')
FROM t_main_group t1
join t_sub_group t2
on t1.id = t2.main_id
group by t1.id
order by t1.`status`, date_format(t1.start_date, '%Y-%m-%d')
limit 6, 3;