SQL无限分级表 递归查询

转载至: https://www.cnblogs.com/liuxiaoji/p/11152094.html

CREATE TABLE `function`  (
  `Id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Id',
  `CreateTime` datetime(0) NOT NULL COMMENT '创建时间',
  `ModifyTime` timestamp(0) NOT NULL ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  `Name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '名称',
  `ParentId` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '上级功能Id',
  `Icon` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '图标',
  `Type` int(11) NOT NULL COMMENT '功能类型',
  `Sort` int(11) NOT NULL COMMENT '类型',
  `Path` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '路径',
  `Describe` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '描述',
  `Flag` bigint(20) NOT NULL COMMENT '标记',
  `Classify` int(10) NOT NULL COMMENT '所属系统分类',
  PRIMARY KEY (`Id`) USING BTREE,
  UNIQUE INDEX `Index_Function_PathClassify`(`Path`, `Classify`) USING BTREE COMMENT '每个系统路径是唯一的'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

INSERT INTO `function` VALUES ('1', '2019-07-08 16:03:29', '2019-07-08 16:03:31', '人力', '0', '/', 1, 1, '/a', '1', 0, 0);
INSERT INTO `function` VALUES ('10', '2019-07-08 16:22:54', '2019-07-08 16:22:55', '企业资源', '3', '/qi', 1, 1, '/qi', NULL, 0, 0);
INSERT INTO `function` VALUES ('2', '2019-07-08 16:06:55', '2019-07-08 16:06:57', '员工', '1', '/', 1, 1, '/c', '1', 0, 0);
INSERT INTO `function` VALUES ('3', '2019-07-08 16:07:48', '2019-07-08 16:07:50', '组织', '1', '/', 1, 1, '/d', '1', 0, 0);
INSERT INTO `function` VALUES ('4', '2019-07-08 16:08:28', '2019-07-08 16:08:31', '考勤', '1', '？', 1, 1, '/dsa', '1', 0, 0);
INSERT INTO `function` VALUES ('5', '2019-07-08 16:08:53', '2019-07-08 16:09:27', '设备', '4', '/ss', 1, 1, '/3', '1', 0, 0);
INSERT INTO `function` VALUES ('6', '2019-07-08 16:18:04', '2019-07-08 16:18:06', '打野', NULL, '/da', 1, 12, '/daa', NULL, 0, 1);
INSERT INTO `function` VALUES ('7', '2019-07-08 16:19:52', '2019-07-08 16:19:55', '员工成员', '2', '/a', 1, 14, '/aa', '1', 0, 0);
INSERT INTO `function` VALUES ('8', '2019-07-08 16:21:29', '2019-07-08 16:21:32', '员工家庭', '2', '/j', 1, 2, '/jt', '', 0, 0);
INSERT INTO `function` VALUES ('9', '2019-07-08 16:22:18', '2019-07-08 16:22:20', '员工微信部落', '2', '/wx', 1, 1, '/wx', NULL, 0, 0);

根据父级Id递归查询所有子节点（查询父级Id为0 并且分类为0的所有子节点）

WITH RECURSIVE _children AS
(
 SELECT fun.* FROM `function` fun WHERE fun.ParentId='0' AND Classify = 0
    UNION ALL
 SELECT fun.* FROM _children,`function` fun WHERE fun.ParentId=_children.Id
)
SELECT * FROM _children;

根据子节点查询所有父亲节点（查询子节点Id为9的所有父节点）

WITH RECURSIVE _parent AS
(
 SELECT fun.* FROM `function` fun WHEREfun.Id='9' 
    UNION ALL
 SELECT fun.* FROM _parent,`function` fun WHERE fun.Id=_parent.ParentId
)
SELECT * FROM _parent;