1. Hierarchical Queries 通过CONNECT BY 我们可以方便的查了所有当前节点下的所有子节点。但很遗憾,在MySQL的目前版本中还没有对应的功能。 在MySQL中如果是有限的层次,比如我们事先如果可以确定这个树的最大深度是4, 那么所有节点为根的树的深度均不会超过4,则我们可以直接通过left join 来实现。
例如:
sql = "select organ,totalSum,parentId,yybmc,showindex from (" +
"select yybdm as organ,nvl(totalSum,0) as totalSum,ldepat as parentId,yybmc as yybmc,showindex from YYBDM left JOIN (select yybdm as organ,to_char(sum(nvl(txtTotalSum1,0))+sum(nvl(txtTotalSum2,0))+sum(nvl(txtTotalSum3,0))+sum(nvl(txtTotalSum4,0))+sum(nvl(txtTotalSum5,0))) as totalSum from YYBDM LEFT JOIN appofcsupp on ddlorgan = yybdm"
+ " group by yybdm) on yybdm = organ where dvaldflg = '1'"
+ ") temp connect by prior organ=parentid start with parentid ='-1' ORDER siblings BY sho