Oracle sqlserver2005,Mysql 树结构
ORCLE ===========================================
/树型表结构的递归操作
树型结构的表设计方案:
××××××××××××××××××××××××××××××××××××××××××××××××××
【节点ID,主键】
ID varchar(10) not null primarykey
【节点名称】
name varchar(20) not null,
【父节点ID】
parentID varchar(10) not null,
【节点级别,根节点为0级,子节点为1级,依次类推】
nodeLevel number(4,0) not null,
【节点在所处级别的索引,从0开始,排序用】
nodeIndex number(4,0) not null,
【节点类型,根据业务需要用户可以自己定义,比如01代表部门、02代表角色、03代表用户等等】
type char(2)
××××××××××××××××××××××××××××××××××××××××××××××××××
递归查询ID为7及下级所有节点记录(往下遍历),SQL语句如下:
select * from TreeNode connect by prior id=parentID start with id=7
递归查询ID为7以及到根节点的所有节点记录(往上遍历),SQL语句如下:
select * from TreeNode connect by prior parentID=id start with id=7
现在想要递归删除ID=7及下级所有结点记录,SQL语句如下:
delete from TreeNode where id in (select id from TreeNode connect by prior id=parentID start with id=7)
计算各级节点的个数,SQL语句如下:
select count(id) from TreeNode group by nodelevel order by nodelevel asc
递归列出所有树节点【不用start with...connect by语句,因为只有oracle支持此语法,mysql等其他语法不支持】
select * from TreeNode order by nodelevel asc, nodeIndex asc
递归查询ID为7及下级所有节点记录(往下遍历)【不用start with...connect by语句,因为只有oracle支持此语法,mysql等其他语法不支持】,SQL语句如下:
select * from TreeNode where id=7 or level>(select nodelevel from TreeNode where id=7) order by nodelevel asc, nodeIndex asc
Mysql Sqlservler(2000,2005,2008)=================================
1;从上往下
;with hgo as
(
select *,0 as rank from EntEquipmentDir where IDcode='8'
union all
select h.*,h1.rank+1 from EntEquipmentDir h join hgo h1 on h.fidparent=h1.IDcode
)
select * from hgo
2:从下往上
;with hgo as
(
select IDcode,StrName,StrMemo,fidparent,0 as rank from EntEquipmentDir where IDcode='29'
union all
select h.IDcode,h.StrName,h.StrMemo,h.fidparent,h1.rank+1 from EntEquipmentDir h join hgo h1 on h.IDcode=h1.fidparent
)
select hgo.IDcode,hgo.StrName,hgo.StrMemo,hgo.fidparent from hgo order by dcode