做网站许昌,网站做关键词库的作用,网页制作中怎么添加图片,网页设计基础知识点总结一般树形多级关系数据库设计#xff0c;比较普遍的就是四种方法#xff1a;#xff08;具体见 SQL Anti-patterns这本书#xff09;
Adjacency List#xff1a;每一条记录存parent_id
Path Enumerations#xff1a;每一条记录存整个tree path经过的node枚举#xff08…一般树形多级关系数据库设计比较普遍的就是四种方法具体见 SQL Anti-patterns这本书
Adjacency List每一条记录存parent_id
Path Enumerations每一条记录存整个tree path经过的node枚举适合深度较浅且固定的业务字段超长互后将无法命中索引
Nested Sets每一条记录存 nleft 和 nright
Closure Table维护一个表所有的tree path作为记录进行保存。
本文将以Adjacency List作为原始记录Closure Table存储上下级关系的方式来解决查询困难问题。
本文限定删除节点时仅可删除最末节点不可从中间删除否则树不能称之为树。
一通过parent_id方式保存原始记录
1如图现有以下关系 以数据库保存关系如下 仅以当前表结构如果仅仅是查询上下级关系将非常简单如
-- 查询西湖区的下级select * from t_area where parent_id 330106;-- 查询西湖区的上级select * from t_area where id ( select parent_id from t_arem where id 330106 )
但是如果需要查询杭州市的所有下级单位必须使用递归查询 如果此时需求是查询西溪街道与杭州市是否是上下级关系 那么每次查询都需要递归关系吗
此时需要引入Closure Table模式创建上下级关系。
二创建ClosureTable关系表
结构如下 数据如下 此时如果查询杭州市所有下级单位SQL如下 西溪街道与杭州市是否是上下级关系 三 ClosureTable详细使用方法
1新增数据 如上图在下城区新增望江街道。
代码如下
-- 1新增父子关系
INSERT INTO t_area(id,name,parent_id) VALUES(330102010,望江街道,330102);-- 2新增上下级关系
-- 2.1 插入自身
INSERT INTO t_area_closure(ancestor,descendant,level) VALUES(330102010,330102010,0);-- 2.2 为 330102010 的父级 330102 所有上级包含330102自身增加下级330102010血缘关系。
INSERT INTO t_area_closure(ancestor,descendant,level)
select ancestor,330102010 as descendant,(level1) as level from t_area_closure where descendant 330102;此时查询330102010的上级关系如果不包含自己则筛选level0即可 2删除数据 如上图删除杭州市与江干区的关系。
代码如下
-- 1删除父子关系(也可以增加逻辑字段进行软删除)
DELETE FROM t_area where id 330104;-- 2删除关系表。因为是末端删除只要删除指定数据的父级关系即可DELETE FROM t_area_closure where descendant 330104;
此时关系表中已没有330104数据的关系数据 3变更父子关系 如上图将西湖区转移到望江街道下面。
简单做法
删除老的关系。将西湖区、北山街道、西溪街道按删除数据处理删除所有关系。
新增关系。在望江街道下面新增西湖区、北山街道、西溪街道关系。 复杂做法
1删除老关系。
西湖区、北山街道、西溪街道与杭州市、浙江省的关系将会变更所以先删除西湖区、北山街道、西溪街道与杭州市、浙江省的上级关系。即西湖区及下属区域删除西湖区父级以上区域的关系。
-- 1.1 查找指定区域的父级及以上关系
SELECT ancestor from t_area_closure where descendant 330106 and level 0
-- 1.2 查找区域自己和自己的下级
SELECT descendant from t_area_closure where ancestor 330106
-- 1.3删除自己和自己的下级 与 自己父级以上区域的关系
delete from t_area_closure where ancestor in ( select ancestor from (SELECT ancestor from t_area_closure where descendant 330106 and level 0) t1 )
and descendant in ( select descendant from (SELECT descendant from t_area_closure where ancestor 330106) t2 )
此时删除完成数据后西湖区自己与下属北山街道、西溪街道的关系依然存在。
2添加新关系。
2.1 查询新父级的上级关系包含新父级 2.2 查找区域自己和自己的下级 2.3 为指定区域自己及下级增加新父级的上级关系。
INSERT INTO t_area_closure(ancestor,descendant,level)
SELECT t1.ancestor,t2.descendant,(t1.levelt2.level 1) as level from t_area_closure t1,t_area_closure t2
where t1.descendant 330102010 and t2.ancestor 330106;
此时查询北山街道所有上级 数据正确。
