欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

无限层级mysql数据表结构

程序员文章站 2021-11-28 11:23:08
...
# 无限层级mysql数据表结构
# 创建表
-- auto-generated definition
create table target_resource
(
  id               int not null
    primary key,
  resource_id      int null,
  resource_team_id int null
);
# 创建关系表
-- auto-generated definition
create table agent_tree_path
(
  parents_team_id int             not null,
  son_team_id     int             not null,
  path_length     int default '0' null,
  primary key (parents_team_id, son_team_id),
  constraint agent_tree_path_ibfk_1
  foreign key (son_team_id) references target_resource (id)
);

create index son_team_id
  on agent_tree_path (son_team_id);

# 插入到关系表
INSERT INTO agent_tree_path (parents_team_id, son_team_id, path_length)
SELECT t.parents_team_id, 7, t.path_length + 1
FROM agent_tree_path AS t
WHERE t.son_team_id = 6
UNION ALL
SELECT 7, 7, 0;
# 删除子节点
DELETE
FROM agent_tree_path
WHERE son_team_id = 7;
# 要删除一颗完整的子树,parents_team_id  和他的后代
DELETE
FROM agent_tree_path
WHERE son_team_id IN (SELECT son_team_id FROM agent_tree_path WHERE parents_team_id = 4);
# 查询子节点的所有父节点
SELECT t.*
FROM target_resource AS t
       INNER JOIN agent_tree_path a on t.id = a.parents_team_id
WHERE a.son_team_id = 6;
# 查询父节点所有的子节点
SELECT t.*
FROM target_resource AS t
       INNER JOIN agent_tree_path a on t.id = a.son_team_id
WHERE a.parents_team_id = 6
相关标签: mysql