oracle使用
select *
from (select st.*
from (select t.*
from bas_basic_tree t
connect by prior t.pid = t.id
start with t.dataid in
(select orgid
from bas_basic_organization_user
where userid = #{userid})) st)
mysql使用递归函数
通用的是表中增加一个treecode字段用于记录节点信息:
一级节点:001
二级节点:001001,001002
三级节点:001001001,001002001
sql : select substr(concat(1,treecode)+1,2) from bas_model_tree
where pid=#{pid} order by treecode desc limit 1
查询子节点是根据treecode模糊查询先查询出id,再根据id查询
查询父节点根据treecode
String str = treecode;
String con="(";
for(int i=0;i<str.length();i+=3) {
con=con+str.substring(i)+",";
}
con=con.substring(0, con.length()-1)+")";
根据con使用in查询出id,再根据id查询