// 对每个分层结构进行求和
如我搜系统的deptid 结果集的则是三个分行的 数据以及三个分行下线的列和
我进入分行则显示分行下的
层级是full node id
整理到这儿实在不会了
下面是表结构数据
本来看网上学了用with r 递归的方式,奈何数据库不是8 的版本
-- Table structure for tmp_matain_stat
DROP TABLE IF EXISTS tmp_matain_stat
;
CREATE TABLE tmp_matain_stat
(
Dept_ID
int(11) NULL DEFAULT NULL,
Dept_Name
varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
Dept_Type
varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
Full_Node_ID
varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
Terminal_Quantity
int(11) NULL DEFAULT NULL,
Patrol_Frequency
int(11) NULL DEFAULT NULL,
Patrolled_Frequency
int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- Records of tmp_matain_stat
INSERT INTO tmp_matain_stat
VALUES (1000047, '系统', '05', '0000|0047', 0, 0, 0);
INSERT INTO tmp_matain_stat
VALUES (1000048, '光光分行', '06', '0000|0047|0048', 0, 0, 0);
INSERT INTO tmp_matain_stat
VALUES (1000052, '光光分行营业部', '06', '0000|0047|0048|0052', 6, 18, 1);
INSERT INTO tmp_matain_stat
VALUES (1000053, '光光分行运营管理部', '06', '0000|0047|0048|0053', 1, 3, 0);
INSERT INTO tmp_matain_stat
VALUES (1000054, '光光12支行', '06', '0000|0047|0048|0054', 3, 9, 0);
INSERT INTO tmp_matain_stat
VALUES (1000055, '光光13支行', '06', '0000|0047|0048|0055', 3, 9, 0);
INSERT INTO tmp_matain_stat
VALUES (1000056, '光光14支行', '06', '0000|0047|0048|0056', 3, 9, 0);
INSERT INTO tmp_matain_stat
VALUES (1000057, '光光15支行', '06', '0000|0047|0048|0057', 4, 12, 9);
INSERT INTO tmp_matain_stat
VALUES (1000058, '光光16支行', '06', '0000|0047|0048|0058', 2, 6, 0);
INSERT INTO tmp_matain_stat
VALUES (1000059, '光光17支行', '06', '0000|0047|0048|0059', 3, 9, 0);
INSERT INTO tmp_matain_stat
VALUES (1000060, '光光18支行', '06', '0000|0047|0048|0060', 3, 9, 0);
INSERT INTO tmp_matain_stat
VALUES (1000061, '光光19支行', '06', '0000|0047|0048|0061', 3, 9, 0);
INSERT INTO tmp_matain_stat
VALUES (1000062, '光光20支行', '06', '0000|0047|0048|0062', 3, 9, 0);
INSERT INTO tmp_matain_stat
VALUES (1000063, '光光21支行', '06', '0000|0047|0048|0063', 3, 9, 0);
INSERT INTO tmp_matain_stat
VALUES (1000064, '光光22支行', '06', '0000|0047|0048|0064', 3, 9, 0);
INSERT INTO tmp_matain_stat
VALUES (1000065, '光光23支行', '06', '0000|0047|0048|0065', 3, 9, 0);
INSERT INTO tmp_matain_stat
VALUES (1000066, '光光24支行', '06', '0000|0047|0048|0066', 3, 9, 0);
INSERT INTO tmp_matain_stat
VALUES (1000067, '阳阳分行', '06', '0000|0047|0067', 0, 0, 0);
INSERT INTO tmp_matain_stat
VALUES (1000068, '光光25支行', '06', '0000|0047|0048|0068', 1, 3, 0);
INSERT INTO tmp_matain_stat
VALUES (1000069, '光光26支行', '06', '0000|0047|0048|0069', 2, 6, 0);
INSERT INTO tmp_matain_stat
VALUES (1000070, '阳阳分行1部', '06', '0000|0047|0067|0070', 3, 9, 0);
INSERT INTO tmp_matain_stat
VALUES (1000071, '阳阳分行2部', '06', '0000|0047|0067|0071', 0, 0, 0);
INSERT INTO tmp_matain_stat
VALUES (1000072, '嘿嘿分行', '06', '0000|0047|0072', 0, 0, 0);
INSERT INTO tmp_matain_stat
VALUES (1000073, '嘿嘿分行1部', '06', '0000|0047|0072|0073', 5, 15, 0);
INSERT INTO tmp_matain_stat
VALUES (1000074, '嘿嘿分行2部', '06', '0000|0047|0072|0074', 0, 0, 0);