mysql层级级联查询示例

已于 2023-12-25 09:14:49 修改
阅读量823 收藏 9
点赞数 8
文章标签： mysql 数据库 java
于 2023-12-04 20:57:56 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44943313/article/details/134793278
版权

准备表----费用类型
fee_type
建表语句

CREATE TABLE `fee_type`  (
  `id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '记录的主键',
  `created` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `created_by` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '创建人',
  `last_upd` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '最后更新时间',
  `last_upd_by` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '最后更新人',
  `org_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '组织id',
  `postn_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '职位id',
  `row_version` bigint(19) NOT NULL DEFAULT 1 COMMENT '版本id',
  `cost_type_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '费用类型编号',
  `cost_type_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '费用类型名称',
  `cost_type_level` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '费用类型级别',
  `parent_cost_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '父费用类型编号',
  `effectived` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '是否有效',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `LNK_FEE_TYPE_N1`(`cost_type_code`) USING BTREE,
  INDEX `LNK_FEE_TYPE_N4`(`effectived`) USING BTREE,
  INDEX `LNK_FEE_TYPE_N5`(`cost_type_level`, `effectived`, `parent_cost_code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '费用类型、活动类型共用' ROW_FORMAT = Dynamic;

插入数据测试

INSERT INTO `fee_type` VALUES ('278487513579263763', '2020-12-08 11:29:57', 'W-30DB903', '2021-04-14 17:12:35', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100001', '渠道建设', '1', NULL, 'Y');
INSERT INTO `fee_type` VALUES ('278487513868670740', '2020-12-08 11:29:57', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100002', '消费者建设', '1', NULL, 'Y');
INSERT INTO `fee_type` VALUES ('278487514174723596', '2020-12-08 11:29:57', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100003', '团队建设', '1', NULL, 'Y');
INSERT INTO `fee_type` VALUES ('278487514468324877', '2020-12-08 11:29:57', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100004', '品牌推广', '1', NULL, 'Y');
INSERT INTO `fee_type` VALUES ('278487514762057493', '2020-12-08 11:29:57', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100005', '特殊支持', '1', NULL, 'Y');
INSERT INTO `fee_type` VALUES ('278487515055658774', '2020-12-08 11:29:57', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100006', '销售奖励', '2', '100001', 'Y');
INSERT INTO `fee_type` VALUES ('278576284291043763', '2020-12-08 17:22:41', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100007', '渠道专项支持', '2', '100001', 'Y');
INSERT INTO `fee_type` VALUES ('278576284567998864', '2020-12-08 17:22:41', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100008', '包装物回收', '2', '100001', 'Y');
INSERT INTO `fee_type` VALUES ('278576284840628625', '2020-12-08 17:22:42', 'W-30DB903', '2021-08-16 17:52:27', '313639993816126100', 'W-5GJLF', 'W-5GJLE', 1, '100009', '进场陈列', '2', '100001', 'Y');
INSERT INTO `fee_type` VALUES ('278576285125710260', '2020-12-08 17:22:42', 'W-30DB903', '2021-07-26 14:03:25', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100010', '渠道工作会议', '2', '100001', 'Y');
INSERT INTO `fee_type` VALUES ('278576285402534325', '2020-12-08 17:22:42', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100011', '渠道客情维护', '2', '100001', 'Y');
INSERT INTO `fee_type` VALUES ('278576285675295122', '2020-12-08 17:22:42', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100012', '渠道运营补贴费用', '2', '100001', 'Y');
INSERT INTO `fee_type` VALUES ('278576285960507795', '2020-12-08 17:22:42', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100013', '消费者客情维护', '2', '100002', 'Y');
INSERT INTO `fee_type` VALUES ('278576286245589430', '2020-12-08 17:22:42', 'W-30DB903', '2021-06-11 10:05:52', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100014', '参观旅游', '2', '100002', 'Y');
INSERT INTO `fee_type` VALUES ('278576286530802103', '2020-12-08 17:22:42', 'W-30DB903', '2021-06-11 10:11:53', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100015', '消费者买赠', '2', '100002', 'Y');
INSERT INTO `fee_type` VALUES ('278576286816014776', '2020-12-08 17:22:42', 'W-30DB903', '2021-06-11 10:05:52', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100016', '小众传播', '2', '100002', 'Y');
INSERT INTO `fee_type` VALUES ('278576287092969876', '2020-12-08 17:22:42', 'W-30DB903', '2021-06-11 10:05:52', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100017', '会战费用', '2', '100003', 'Y');
INSERT INTO `fee_type` VALUES ('278576287361405333', '2020-12-08 17:22:42', 'W-30DB903', '2021-06-11 10:05:52', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100018', '促销人员补贴', '2', '100003', 'Y');
INSERT INTO `fee_type` VALUES ('278576287650681273', '2020-12-08 17:22:42', 'W-30DB903', '2021-06-11 10:05:52', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100019', '销售提成及奖励', '2', '100003', 'Y');
INSERT INTO `fee_type` VALUES ('278576287927505338', '2020-12-08 17:22:42', 'W-30DB903', '2021-06-11 10:05:52', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100020', '活动类', '2', '100004', 'Y');
INSERT INTO `fee_type` VALUES ('278576288208654742', '2020-12-08 17:22:42', 'W-30DB903', '2021-08-03 16:21:55', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100021', '广宣促销品类', '2', '100004', 'Y');
INSERT INTO `fee_type` VALUES ('278576288485478807', '2020-12-08 17:22:42', 'W-30DB903', '2021-06-11 10:05:52', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100022', '终端形象建设类', '2', '100004', 'Y');
INSERT INTO `fee_type` VALUES ('278576288749588923', '2020-12-08 17:22:42', 'W-30DB903', '2021-06-11 10:05:52', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100023', '下属客户打包费用', '2', '100005', 'Y');
INSERT INTO `fee_type` VALUES ('278576289030607292', '2020-12-08 17:22:43', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100024', '其他费用投入', '2', '100005', 'Y');
INSERT INTO `fee_type` VALUES ('278576289307562392', '2020-12-08 17:22:43', 'W-30DB903', '2022-04-13 10:19:42', '390110232119742891', 'W-5GJLF', 'W-5GJLE', 1, '100025', '经销商销售奖励', '3', '100006', 'Y');
INSERT INTO `fee_type` VALUES ('278576289584386457', '2020-12-08 17:22:43', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100026', '其他销售奖励', '3', '100006', 'Y');
INSERT INTO `fee_type` VALUES ('278576289856885181', '2020-12-08 17:22:43', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100027', '招商奖励', '3', '100007', 'Y');
INSERT INTO `fee_type` VALUES ('278576290129514942', '2020-12-08 17:22:43', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100028', '经销商随货支持', '3', '100007', 'Y');
INSERT INTO `fee_type` VALUES ('278576290469384602', '2020-12-08 17:22:43', 'W-30DB903', '2021-04-14 17:12:51', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100029', '二维码扫码奖励', '3', '100007', 'Y');
INSERT INTO `fee_type` VALUES ('278576290775568795', '2020-12-08 17:22:43', 'W-30DB903', '2022-08-12 10:09:04', '496252475579495099', 'W-5GJLF', 'W-5GJLE', 1, '100030', '包装物回收费', '3', '100008', 'Y');
INSERT INTO `fee_type` VALUES ('278576291056587164', '2020-12-08 17:22:43', 'W-30DB903', '2021-08-16 17:52:23', '313639993816126100', 'W-5GJLF', 'W-5GJLE', 1, '100031', '进场陈列费', '3', '100009', 'Y');
INSERT INTO `fee_type` VALUES ('278576291333280191', '2020-12-08 17:22:43', 'W-30DB903', '2021-07-26 14:03:21', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100032', '渠道工作会议', '3', '100010', 'Y');
INSERT INTO `fee_type` VALUES ('278576291614298560', '2020-12-08 17:22:43', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100033', '渠道客情维护', '3', '100011', 'Y');
INSERT INTO `fee_type` VALUES ('278576291887059357', '2020-12-08 17:22:43', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100034', '运营补贴', '3', '100012', 'Y');
INSERT INTO `fee_type` VALUES ('278576292172272030', '2020-12-08 17:22:43', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100035', 'VIP（顾问）赠酒及赠品', '3', '100013', 'Y');
INSERT INTO `fee_type` VALUES ('278576292507685313', '2020-12-08 17:22:43', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100036', '消费者联谊活动', '3', '100013', 'Y');
INSERT INTO `fee_type` VALUES ('278576292792897986', '2020-12-08 17:22:43', 'W-30DB903', '2021-06-03 22:50:23', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100037', '赠饮及免品活动', '3', '100013', 'Y');
INSERT INTO `fee_type` VALUES ('278576293069853087', '2020-12-08 17:22:44', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100038', '泸州游-旅游费用', '3', '100014', 'Y');
INSERT INTO `fee_type` VALUES ('278576293338288544', '2020-12-08 17:22:44', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100039', '泸州游-用酒及服务费', '3', '100014', 'Y');
INSERT INTO `fee_type` VALUES ('278576293610787267', '2020-12-08 17:22:44', 'W-30DB903', '2021-06-03 22:50:23', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100040', '其他参观旅游', '3', '100014', 'Y');
INSERT INTO `fee_type` VALUES ('278576293900194244', '2020-12-08 17:22:44', 'W-30DB903', '2022-07-04 14:32:54', '365863757483873176', 'W-5GJLF', 'W-5GJLE', 1, '100041', '常规买赠', '3', '100015', 'Y');
INSERT INTO `fee_type` VALUES ('278576294181343649', '2020-12-08 17:22:44', 'W-30DB903', '2021-06-11 11:36:41', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100042', '宴席买赠', '3', '100015', 'Y');
INSERT INTO `fee_type` VALUES ('278576294458167714', '2020-12-08 17:22:44', 'W-30DB903', '2022-11-03 16:11:33', '456849390927278321', 'W-5GJLF', 'W-5GJLE', 1, '100043', '品鉴会', '3', '100016', 'Y');
INSERT INTO `fee_type` VALUES ('278576294730666437', '2020-12-08 17:22:44', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100044', '会战生活补贴', '3', '100017', 'Y');
INSERT INTO `fee_type` VALUES ('278576295032656326', '2020-12-08 17:22:44', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100045', '会战综合费用', '3', '100017', 'Y');
INSERT INTO `fee_type` VALUES ('278576295301222819', '2020-12-08 17:22:44', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100046', '经销商团队补贴', '3', '100018', 'Y');
INSERT INTO `fee_type` VALUES ('278576295569658276', '2020-12-08 17:22:44', 'W-30DB903', '2021-04-14 17:12:30', '203974814799364097', 'W-5GJLF', 'W-5GJLE', 1, '100047', '销售提成及奖励', '3', '100019', 'Y');
INSERT INTO `fee_type` VALUES ('278576295842156999', '2020-12-08 17:22:44', 'W-30DB903', '2021-07-09 16:26:18', '274312196195225712', 'W-5GJLF', 'W-5GJLE', 1, '100048', '品牌级俱乐部活动', '3', '100020', 'Y');
INSERT INTO `fee_type` VALUES ('278576296106398152', '2020-12-08 17:22:44', 'W-30DB903', '2021-06-03 22:50:22', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100049', '区域级俱乐部活动', '3', '100020', 'Y');
INSERT INTO `fee_type` VALUES ('278576296404324773', '2020-12-08 17:22:44', 'W-30DB903', '2021-06-03 22:50:22', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100050', '整合营销活动', '3', '100020', 'Y');
INSERT INTO `fee_type` VALUES ('278576296685343142', '2020-12-08 17:22:44', 'W-30DB903', '2021-06-03 22:50:22', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100051', '事件营销活动', '3', '100020', 'Y');
INSERT INTO `fee_type` VALUES ('278576296962036169', '2020-12-08 17:22:44', 'W-30DB903', '2021-08-03 16:21:55', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100052', '销售促进类广宣促销品', '3', '100021', 'Y');
INSERT INTO `fee_type` VALUES ('278576297234796967', '2020-12-08 17:22:45', 'W-30DB903', '2021-08-03 16:21:55', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100053', '客情维护类广宣促销品', '3', '100021', 'Y');
INSERT INTO `fee_type` VALUES ('278576297511621032', '2020-12-08 17:22:45', 'W-30DB903', '2021-11-04 11:27:25', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100054', '品牌形象类广宣促销品', '3', '100021', 'Y');
INSERT INTO `fee_type` VALUES ('278576297809285578', '2020-12-08 17:22:45', 'W-30DB903', '2021-06-09 18:07:20', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100055', '形象店建设', '3', '100022', 'Y');
INSERT INTO `fee_type` VALUES ('278576298086109643', '2020-12-08 17:22:45', 'W-30DB903', '2021-06-09 18:07:20', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100056', '门头店招', '3', '100022', 'Y');
INSERT INTO `fee_type` VALUES ('278576298367259049', '2020-12-08 17:22:45', 'W-30DB903', '2021-06-09 18:07:20', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100057', '终端物料及展陈', '3', '100022', 'Y');
INSERT INTO `fee_type` VALUES ('278576298635694506', '2020-12-08 17:22:45', 'W-30DB903', '2021-06-09 18:07:20', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100058', '下属客户打包费用', '3', '100023', 'Y');
INSERT INTO `fee_type` VALUES ('278576298908193228', '2020-12-08 17:22:45', 'W-30DB903', '2021-06-09 18:07:20', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100059', '样品酒费用', '3', '100024', 'Y');
INSERT INTO `fee_type` VALUES ('278576299176628685', '2020-12-08 17:22:45', 'W-30DB903', '2021-06-09 18:07:20', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100060', '渠道政策促销奖励', '2', '100001', 'Y');
INSERT INTO `fee_type` VALUES ('278576299445195179', '2020-12-08 17:22:45', 'W-30DB903', '2021-06-09 18:07:20', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100061', '渠道政策促销奖励', '3', '100060', 'Y');
INSERT INTO `fee_type` VALUES ('278576299730407852', '2020-12-08 17:22:45', 'W-30DB903', '2021-06-09 18:07:20', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100062', '促销品及促销用酒预投', '3', '100015', 'Y');
INSERT INTO `fee_type` VALUES ('406506275765882910', '2021-11-26 17:50:48', 'W-30DB903', '2022-05-17 15:37:51', '456849390927278321', 'W-5GJLF', 'W-5GJLE', 1, '100065', '线上运营建设', '1', NULL, 'Y');
INSERT INTO `fee_type` VALUES ('406506384238841882', '2021-11-26 17:51:14', 'W-30DB903', '2022-08-01 16:03:17', '496252475579495099', 'W-5GJLF', 'W-5GJLE', 1, '100066', '平台费用', '2', '100065', 'Y');
INSERT INTO `fee_type` VALUES ('406506529454559271', '2021-11-26 17:51:49', 'W-30DB903', '2021-11-26 17:51:49', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100067', '流量购买费', '2', '100065', 'Y');
INSERT INTO `fee_type` VALUES ('406506643530846239', '2021-11-26 17:52:16', 'W-30DB903', '2022-05-17 14:54:38', '456849390927278321', 'W-5GJLF', 'W-5GJLE', 1, '100068', '第三方服务费', '2', '100065', 'Y');
INSERT INTO `fee_type` VALUES ('406506790713036830', '2021-11-26 17:52:51', 'W-30DB903', '2022-08-01 16:03:47', '496252475579495099', 'W-5GJLF', 'W-5GJLE', 1, '100069', '佣金及手续费', '3', '100066', 'Y');
INSERT INTO `fee_type` VALUES ('406506929133981737', '2021-11-26 17:53:24', 'W-30DB903', '2022-08-29 15:18:14', '496252475579495099', 'W-5GJLF', 'W-5GJLE', 1, '100070', '平台工具使用费用', '3', '100066', 'Y');
INSERT INTO `fee_type` VALUES ('406507122553786399', '2021-11-26 17:54:10', 'W-30DB903', '2021-11-26 17:54:10', 'W-30DB903', 'W-5GJLF', 'W-5GJLE', 1, '100071', '营销流量推广', '3', '100067', 'Y');
INSERT INTO `fee_type` VALUES ('406507233833390122', '2021-11-26 17:54:37', 'W-30DB903', '2022-05-17 15:37:49', '456849390927278321', 'W-5GJLF', 'W-5GJLE', 1, '100072', '短信营销', '3', '100067', 'Y');
INSERT INTO `fee_type` VALUES ('406507351395012641', '2021-11-26 17:55:05', 'W-30DB903', '2022-05-17 15:57:44', '456849390927278321', 'W-5GJLF', 'W-5GJLE', 1, '100073', '第三方服务费', '3', '100068', 'Y');
INSERT INTO `fee_type` VALUES ('468801644001685507', '2022-05-17 15:30:21', '456849390927278321', '2022-08-29 15:18:08', '496252475579495099', '365628015105151465', '384761491879628917', 1, '100074', '测试费用001', '3', '100073', 'Y');
INSERT INTO `fee_type` VALUES ('496358084731664229', '2022-08-01 16:29:49', '496252475579495099', '2022-08-29 15:18:06', '496252475579495099', '369513139651088616', '390916900831105488', 1, '10075', '测试', '3', '100012', 'Y');
INSERT INTO `fee_type` VALUES ('496368858384627505', '2022-08-01 17:12:37', '496252475579495099', '2022-08-01 17:12:37', '496252475579495099', '369513139651088616', '390916900831105488', 1, '10075', 'ceshi AY', '3', '100067', 'Y');
INSERT INTO `fee_type` VALUES ('529629692048896115', '2022-11-01 11:59:18', '456849390927278321', '2022-11-01 11:59:18', '456849390927278321', '96900004282531112', '443798252648398988', 1, '123', '123', '3', '100062', 'Y');

表数据关系简介
费用大类----费用中类-----费用小类
区分字段 费用层级 cost_type_level ：大类（1），中类（2），小类（3）
大中小类父子关系关联字段 parent_cost_code

业务需求
采用层级方式展示查询费用大类及其子层级
在这里插入图片描述实现代码，xml子集映射关系
方式一

 <select id="queryTreeList" parameterType="com.model.FeeType" resultMap="feeTypeTree">
    SELECT
	t1.id AS id,
	t1.cost_type_code AS largeTypeCode,
	t1.cost_type_name AS largeTypeName,
	t1.cost_type_level AS largeTypeLevel,
	t2.middleId AS middleId,
	t2.middleTypeCode AS middleTypeCode,
	t2.middleTypeName AS middleTypeName,
	t2.middleTypeLevel AS middleTypeLevel,
	t2.smallId AS smallId,
	t2.smallTypeCode AS smallTypeCode,
	t2.smallTypeName AS smallTypeName,
	t2.smallTypelevel AS smallTypeLevel 
FROM
	fee_type t1
	LEFT JOIN (
	SELECT
		s1.id AS middleId,
		s1.cost_type_code AS middleTypeCode,
		s1.cost_type_name AS middleTypeName,
		s1.cost_type_level AS middleTypeLevel,
		s1.PARENT_COST_CODE AS parentCostCode,
		s2.id AS smallId,
		s2.costTypeCode AS smallTypeCode,
		s2.costTypeName AS smallTypeName,
		s2.costTypeLevel AS smallTypelevel 
	FROM
		fee_type s1
		LEFT JOIN (
		SELECT
			m1.id AS id,
			m1.cost_type_code AS costTypeCode,
			m1.cost_type_name AS costTypeName,
			m1.cost_type_level AS costTypeLevel,
			m1.PARENT_COST_CODE AS parentCostCode 
		FROM
			fee_type m1 
		WHERE
			m1.cost_type_level = '3' 
			AND m1.effectived = 'Y' 
		) s2 ON s1.cost_type_code = s2.parentCostCode 
	WHERE
		s1.cost_type_level = '2' 
		AND s1.effectived = 'Y' 
	) t2 ON t1.cost_type_code = t2.parentCostCode 
WHERE
	t1.cost_type_level = '1' 
	AND t1.effectived = 'Y' 
GROUP BY
	t1.id,
	t2.middleId,
	t2.smallId
    </select>

    <resultMap type="com.model.FeeType" id="feeTypeTree">
        <result column="id" property="id" jdbcType="VARCHAR" />
        <result column="largeTypeCode" property="costTypeCode" jdbcType="VARCHAR" />
        <result column="largeTypeName" property="costTypeName" jdbcType="VARCHAR" />
        <result column="largeTypeLevel" property="costTypeLevel" jdbcType="VARCHAR" />
        <collection property="children" ofType="com.model.FeeType">
            <result column="middleId" property="id" jdbcType="VARCHAR" />
            <result column="middleTypeCode" property="costTypeCode" jdbcType="VARCHAR" />
            <result column="middleTypeName" property="costTypeName" jdbcType="VARCHAR" />
            <result column="middleTypeLevel" property="costTypeLevel" jdbcType="VARCHAR" />
            <collection property="children" ofType="com.model.FeeType">
                <result column="smallId" property="id" jdbcType="VARCHAR" />
                <result column="smallTypeCode" property="costTypeCode" jdbcType="VARCHAR" />
                <result column="smallTypeName" property="costTypeName" jdbcType="VARCHAR" />
                <result column="smallTypeLevel" property="costTypeLevel" jdbcType="VARCHAR" />
            </collection>
        </collection>
    </resultMap>

方式二

<select id="queryTreeList" resultMap="costTypeTree"
            resultType="com.model.FeeType">
        SELECT
	t1.id AS id,
	t1.cost_type_code AS costLargeTypeCode,
	t1.cost_type_name AS costLargeTypeName,
	t1.cost_type_level AS largeTypeLevel,
	t2.id AS middleId,
	t2.cost_type_code AS costMiddleTypeCode,
	t2.cost_type_name AS costMiddleTypeName,
	t2.cost_type_level AS middleTypeLevel,
	t3.id AS smallId,
	t3.cost_type_code AS costTypeCode,
	t3.cost_type_name AS costTypeName,
	t3.cost_type_level AS smallTypeLevel
  FROM
	lnk_fee_type t1
	LEFT JOIN lnk_fee_type t2 ON t1.cost_type_code = t2.parent_cost_code
	LEFT JOIN lnk_fee_type t3 ON t2.cost_type_code = t3.parent_cost_code
  WHERE
	t1.cost_type_level = '1' 
	AND t1.effectived = 'Y'
    </select>

 <resultMap type="com.model.FeeType" id="costTypeTree">
      <result column="id" property="id" jdbcType="VARCHAR" />
      <result column="costLargeTypeCode" property="costTypeCode" jdbcType="VARCHAR" />
      <result column="costLargeTypeName" property="costTypeName" jdbcType="VARCHAR" />
      <result column="largeTypeLevel" property="costTypeLevel" jdbcType="VARCHAR" />
      <collection property="children" ofType="com.model.FeeType">
          <result column="middleId" property="id"  jdbcType="VARCHAR" />
          <result column="costMiddleTypeCode" property="costTypeCode"  jdbcType="VARCHAR" />
          <result column="costMiddleTypeName" property="costTypeName"  jdbcType="VARCHAR" />
          <result column="middleTypeLevel" property="costTypeLevel"  jdbcType="VARCHAR" />
          <collection property="children" ofType="com.model.FeeType">
              <result column="smallId" property="id"  jdbcType="VARCHAR" />
              <result column="costTypeCode" property="costTypeCode"  jdbcType="VARCHAR" />
              <result column="costTypeName" property="costTypeName"  jdbcType="VARCHAR" />
              <result column="smallTypeLevel" property="costTypeLevel"  jdbcType="VARCHAR" />
          </collection>
      </collection>
  </resultMap>

对应的实体类

public class FeeType {
    private String id;
    /**
     * 费用类型编号
     */
    private String costTypeCode;
    /**
     * 费用类型名称
     */
    private String costTypeName;
    /**
     * 费用类型级别
     */
    private String costTypeLevel;
    /**
     * 父费用类型编号
     */
    private String parentCostCode;
    /**
     * 是否有效
     */
    private String effectived;

    List<FeeType> children;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getCostTypeCode() {
        return costTypeCode;
    }

    public void setCostTypeCode(String costTypeCode) {
        this.costTypeCode = costTypeCode;
    }

    public String getCostTypeName() {
        return costTypeName;
    }

    public void setCostTypeName(String costTypeName) {
        this.costTypeName = costTypeName;
    }

    public String getCostTypeLevel() {
        return costTypeLevel;
    }

    public void setCostTypeLevel(String costTypeLevel) {
        this.costTypeLevel = costTypeLevel;
    }

    public String getParentCostCode() {
        return parentCostCode;
    }

    public void setParentCostCode(String parentCostCode) {
        this.parentCostCode = parentCostCode;
    }

    public String getEffectived() {
        return effectived;
    }

    public void setEffectived(String effectived) {
        this.effectived = effectived;
    }

    public List<FeeType> getChildren() {
        return children;
    }

    public void setChildren(List<FeeType> children) {
        this.children = children;
    }
}

查询层级效果
在这里插入图片描述在这里插入图片描述在这里插入图片描述在这里插入图片描述在这里插入图片描述

带码菜鸟
关注
mysql-2018中国省市县区域数据（单表级联）
02-14
mysql-2018中国省市县区域数据（单表级联），可直接导入使用。
MySQL 使用函数级联查询上级和下级
不积硅步无以至千里
05-21 2544
：）项目中遇到了类似的处理，觉得比直接写sql好用，小记一下 创建表： -- ---------------------------- -- Table structure for level -- ---------------------------- DROP TABLE IF EXISTS `level`; CREATE TABLE `level` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARAC
MYSQL单表连表查询子查询
最新发布
m0_63600788的博客
08-16 808
其语句包括动词 CREATE 和 DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。DDL 包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。需要查询的数据分散在多张表中，只有联合多张表才能查询出期望的数据。比如：emp 表存储了员工的信息，dept 表存储了部门的信息，而需求是查询出员工的编号、姓名以及对应部门的名称。分组情况1：按照性别分组，男生一组，女生一组，之后可以统计男生和女生的数量；
MySQL+Java实现父子级联查询
lonelymanontheway的博客
08-13 4684
MySQL级联查询
mysql（三）：mysql查询语句 和 多表关联查询 以及 子查询
热门推荐
逗号的博客
10-20 3万+
1.查询一张表：     select * from 表名； 2.查询指定字段：select 字段1，字段2，字段3....from 表名； 3.where条件查询：select 字段1，字段2，字段3 frome 表名 where 条件表达式； 例：select * from t_studect where id=1;        sel
mysql联表查询
weixin_44482877的博客
04-15 421
mysql联表查询基础
MySQL系列之查询具有层级数据
qq_38377525的博客
01-10 7030
MySQL查询具有层级数据
中国城市三级级联表sql文件 mysql
08-07
综上所述，`中国城市三级级联表sql文件 mysql`这个主题涉及了数据库设计中的级联表概念，MySQL的使用，以及可能的不同设计方案。这两个SQL文件为我们提供了实现城市级联功能的实例，可以结合实际需求和个人偏好选择...
jQuery ajax+PHP实现的级联下拉列表框功能示例
01-20
在本文中，我们将探讨如何使用jQuery AJAX和PHP来实现级联...这种技术可以扩展到更多级别的级联选择，例如区县、街道等，只需要在PHP端添加更多层级的数据结构，并在JavaScript中相应地修改AJAX请求和处理逻辑即可。
掌握数据完整性：深入理解MySQL中的外键
07-17
- **高性能**：MySQL以其出色的查询处理速度和整体性能而著称。它的优化技术和架构设计使其能够处理高并发请求和大量数据。 - **可靠性**：MySQL提供了多种机制来确保数据的完整性和可靠性，例如事务支持、备份和...
jQuery+PHP+MySQL实现无限级联下拉框效果
11-23
值得注意的是，这个示例使用了过时的`mysql_`系列函数，这些函数在新的PHP版本中已被弃用，建议使用`mysqli`或`PDO`扩展来处理数据库操作，以确保更好的安全性和兼容性。 此外，为了实现无限级联效果，`...
Ajax 实现级联菜单
03-06
级联菜单允许用户在多个相关选项间进行选择，如省份-城市-区县的层级关系。在数据库支持下，Ajax能够实时更新菜单选项，而无需刷新整个页面，极大地提升了用户体验。 首先，我们需要一个数据库来存储这些级联数据。...
mysql查询上(下)级部门
作为一个长者,有必要记录下人生经验
05-09 4043
1.利用祖级列表(ancestors)字段： 1.1准备 CREATE TABLE `my_dept` ( `dept_id` int NOT NULL AUTO_INCREMENT COMMENT 'id值', `parent_id` int DEFAULT NULL COMMENT '上级部门', `ancestors` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '祖级列表',
Mysql带层级(父子级)的递归查询案例
pscool的博客
01-11 6242
需求 组织之间存在层级关系，人员可对应多个组织，对应的多个组织也可以存在上下级关系。 查询某个人的组织的时候，需要把这个人对应的所有组织，和对应的每一个组织的所有上级都按序查询出来。 PersonListDto public class PersonListDTO { private Long id; private String personCode; private String name; private String position; .
mysql中DQL查询语句的使用级联操作
weixin_40228600的博客
11-06 830
排序查询、聚合查询、分组查询、分页查询、约束、多表之间关系聚合函数：将一列数据作为一个整体，进行纵向的计算。
mysql或者oracle递归查询部门层级
吃黄瓜不蘸酱的博客
12-19 1515
mysql递归查询部门层级
mysql数据库之SQL :连接查询使用
qwzeng123的博客
05-17 6943
开头：本人总结，大神勿喷，欢迎指教。谢谢 一：join 和 inner join 与等值连接: 1:SELECT st.s_name,co.c_name FROMrelevance re, student st, course co WHERE re.s_id = st.s_id AND re.c_id = co.t_id(内连接) 2:selectst.s_name...
MySQL（二）高性能优化及规范
folyh
03-22 1136
MySQL（二）高性能优化规范
mysql 递归遍历查询无限层级
qq_55990384的博客
06-28 327
1.建立依赖表，以商品分类表为例，每个分类都会有自己的下级分类。2.以这些数据为基础，查询每个分类的下级(包括下级，下下级)2.添加数据（category）
mysql同表级联查询
09-05
在MySQL中，同表级联查询是通过外键约束来实现的。当一个表的外键与另一个表的主键相关联时，我们可以使用同表级联查询来实现联接查询。 在给出的例子中，emp表的dept_id列与dept表的dept_id列相关联，形成了外键...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值