数据库-闭包表

闭包表： 解决树形数据结构设计，企业组织结构表设计

     总公司（1） -
             -财务部（2）
             -研发部（2）
                    -测试（3）
                    -开发（3）
             -人事部（4）

1. 创建主表

  CREATE TABLE nodeInfo (
	node_id INT NOT NULL AUTO_INCREMENT,
	node_name VARCHAR (255),
	PRIMARY KEY (`node_id`)
) DEFAULT CHARSET = utf8; 

2. 创建关系表

CREATE TABLE nodeRelationship (
	ancestor INT NOT NULL,
	descendant INT NOT NULL,
	distance INT NOT NULL,
	PRIMARY KEY (ancestor, descendant)
) DEFAULT CHARSET = utf8;

其中
Ancestor代表祖先节点
Descendant代表后代节点
Distance 祖先距离后代的距离

添加数据（创建存储过程）

CREATE DEFINER = `root`@`localhost` PROCEDURE `AddNode`(`_parent_name` varchar(255),`_node_name` varchar(255))
BEGIN
	DECLARE _ancestor INT;
	DECLARE _descendant INT;
	DECLARE _parent INT;
	IF NOT EXISTS(SELECT node_id From nodeinfo WHERE node_name = _node_name)
	THEN
		INSERT INTO nodeinfo (node_name) VALUES(_node_name);
		SET _descendant = (SELECT node_id FROM nodeinfo WHERE node_name = _node_name);
		INSERT INTO noderelationship (ancestor,descendant,distance) VALUES(_descendant,_descendant,0);
		IF EXISTS (SELECT node_id FROM nodeinfo WHERE node_name = _parent_name)
		THEN
			SET _parent = (SELECT node_id FROM nodeinfo WHERE node_name = _parent_name);
			INSERT INTO noderelationship (ancestor,descendant,distance) SELECT ancestor,_descendant,distance+1 from noderelationship where descendant = _parent;
		END IF;
	END IF;
END;

查询 ‘name’ 下所有的子节点：

SELECT
	n3.node_name
FROM
	nodeinfo n1
INNER JOIN noderelationship n2 ON n1.node_id = n2.ancestor
INNER JOIN nodeinfo n3 ON n2.descendant = n3.node_id
WHERE
	n1.node_name = 'name'
AND n2.distance != 0

查询’name’下直属子节点：

SELECT
	n3.node_name
FROM
	nodeinfo n1
INNER JOIN noderelationship n2 ON n1.node_id = n2.ancestor
INNER JOIN nodeinfo n3 ON n2.descendant = n3.node_id
WHERE
	n1.node_name = 'Fruit'
AND n2.distance = 1

# 删除节点闭包表相关数据

delete from noderelationship where ancestor in (3,6)  #in 该节点与他所有后代节点集合
or  descendant in  (3,6);

变更父节点: 将B节点移到C节点上(见图5)

1).待删除的边：

select * from noderelationship where ancestor in (1)  #B的所有父节点
and descendant in (2,4,5);  #B所有节点包括自己
delete from noderelationship where ancestor in (1) 
and descendant in (2,4,5); 

INSERT INTO noderelationship (ancestor,descendant,distance)
SELECT
 super.ancestor ancestor,
 sub.descendant descendant,
 (super.distance+sub.distance+1) distance
FROM
    noderelationship super
    CROSS JOIN
    noderelationship sub
WHERE
super.descendant=3 AND sub.ancestor=2

select * from noderelationship where ancestor in (1,3)  #B的所有父节点
	and descendant in (6);  #B所有节点包括自己

转自https://www.biaodianfu.com/closure-table.html

子父级表查询 查询下级所有

mysql

CREATE DEFINER=`ymt_dev`@`%` FUNCTION `factoryInfoParent`(`pId` decimal(20)) RETURNS varchar(2000) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
    DETERMINISTIC
BEGIN
	#Routine body goes here... 下级所有包含自己
 DECLARE sChildList VARCHAR(1000);
 DECLARE sChildTemp VARCHAR(1000);
 
 SET sChildTemp =CAST(pId AS CHAR);
 WHILE sChildTemp IS NOT NULL DO
 IF (sChildList IS NOT NULL) THEN
  SET sChildList = CONCAT(sChildList,',',sChildTemp);
 ELSE
 SET sChildList = CONCAT(sChildTemp);
 END IF;
 SELECT GROUP_CONCAT(P_ID) INTO sChildTemp FROM JC_FACTORY_INFO WHERE IS_ENABLE = 1 AND FIND_IN_SET(PARENT_ID,sChildTemp)>0;
 END WHILE;
	RETURN sChildList;
END

   SELECT
   jc*
    FROM JC_FACTORY_INFO jc
    where jc.IS_ENABLE = 1
	and  FIND_IN_SET(P_ID,factoryInfoParent(#{pId}))

oracle

  SELECT
    jc.P_ID pId,
    jc.FACTORY_CODE factoryCode,
    jc.FACTORY_NAME factoryName,
    jc.PARENT_ID parentId,
    jc.TYPE type,
    jc.IS_ENABLE isEnable
    FROM JC_FACTORY_INFO jc
    where jc.IS_ENABLE = 1
			start with jc.P_ID= #{pId}
    connect by prior  jc.P_ID=jc.PARENT_ID