二叉树在MySQL中的应用_vlambda技术博客

vlambda
2021-05-25

二叉树在MySQL中的应用

MySQL代码：

drop procedure if exists p1;DELIMITER $$create procedure p1()begin select distinct region as id, region as text, '' as parentnodeid,  1 as level, 1 as isparentflag, '' as ancester from suppliers union all select distinct concat('x',city) as id, city as text,  region as parentnodeid, 2 as level, 1 as isparentflag,  concat('',region,'#') as ancester from suppliers union all select supplierid as id, companyname as text,  concat('x',city) as parentnodeid, 3 as level, 0 as isparentflag,  concat('',region,'#','x',city) as ancester from suppliers  order by concat(trim(ancester),id);  end; $$DELIMITER ;call p1();

程序一的第一层为省份，第二层为每个省份的市区，第三层为每个市区的供应商（如果供应商没有在某个城市，那么第二层次中就不会出现该城市，如果某个省一个供应商都没有，则第一层中不会出现该省份）。

drop procedure if exists p2;DELIMITER $$create procedure p2()begin select categoryid as id, concat(categoryid,' ',CategoryName) as text,  parentnodeid,level,1 as isparentflag,ancester, categoryid as productid from categorytree union all select productid as id, concat(productid,' ',productname) as text, a.subcategoryid as parentnodeid, b.level+1 as level, 0 as isparentflag,  concat(b.ancester, a.subcategoryid) as ancester, a.productid  from products as a join categorytree as b  on a.subcategoryid=b.categoryid
 order by concat(trim(ancester),id);  end; $$DELIMITER ;call p2();

程序二的第一层为食品大类，第二层为每个食品大类的子类，第三层为每个子类下的小类，第四层为每个小类别下面的商品。

网页代码：

<body style="margin: 2px 2px 2px 2px;"><div class="easyui-layout" data-options="fit:true" style="margin:0px 0px 0px 0px;"> <div class="easyui-panal" data-options="region:'north',split:false"  style="background-color:#E0ECFF; height:36px;"> </div> <div class="easyui-panal" data-options="region:'west',split:true" style="width:400px;"> <div id="myTree1" class="easyui-tree"></div></div> <div class="easyui-panal" data-options="region:'center'">  <div id="myTree2" class="easyui-tree"></div></div>  <div class="easyui-panal" data-options="region:'south',split:true" style="height:200px;"></div> <div class="easyui-panel" data-options="region:'east',split:true" style="width:600px;"></div>  <script>$(function(){ var p={}; p.style='full'; rs=myRunTreeProcedure('p1', p); $('#myTree1').tree({ fit:true, data:rs, onSelect:function(node){ drawChartalert(node); } }); $('#myTree1').tree('collapseAll');//---------------------});$(function(){ var p={}; p.style='full'; rs=myRunTreeProcedure('p2', p); $('#myTree2').tree({ fit:true, data:rs, onSelect:function(node){ drawChartalert(node); } }); $('#myTree2').tree('collapseAll');  //---------------------});</script></body></html>