8步快速搭建个人网站视频,c2c平台购物流程,网站建设技术中心,优化大师官方网站数据库对层次结构的处理模型有好多种#xff0c;可以根据自己的需求来设计模型#xff0c;当然最简单的也是最容易设计的模型就是所谓的邻接模型。在这方面#xff0c;其他数据库比如Oracle提数据库对层次结构的处理模型有好多种#xff0c;可以根据自己的需求来设计模型可以根据自己的需求来设计模型当然最简单的也是最容易设计的模型就是所谓的邻接模型。在这方面其他数据库比如Oracle提数据库对层次结构的处理模型有好多种可以根据自己的需求来设计模型当然最简单的也是最容易设计的模型就是所谓的邻接模型。在这方面其他数据库比如Oracle 提供了现成的分析方法 connect by而MySQL在这方面就显得有些薄弱了。 不过可以用MySQL的存储过程实现ORACLE类似的分析功能这样先来创建一个简单的数表。create table country ( id number(2) not null, name varchar(60) not null);create table country_relation (id number(2), parentid number(2));插入一些数据-- Table country.insert into country (id,name) values (0,Earth);insert into country (id,name) values (2,North America);insert into country (id,name) values (3,South America);insert into country (id,name) values (4,Europe);insert into country (id,name) values (5,Asia);insert into country (id,name) values (6,Africa);insert into country (id,name) values (7,Australia);insert into country (id,name) values (8,Canada);insert into country (id,name) values (9,Central America);insert into country (id,name) values (10,Island Nations);insert into country (id,name) values (11,United States);insert into country (id,name) values (12,Alabama);insert into country (id,name) values (13,Alaska);insert into country (id,name) values (14,Arizona);insert into country (id,name) values (15,Arkansas);insert into country (id,name) values (16,California);-- Table country_relation.insert into country_relation (id,parentid) values (0,NULL);insert into country_relation (id,parentid) values (2,0);insert into country_relation (id,parentid) values (3,0);insert into country_relation (id,parentid) values (4,0);insert into country_relation (id,parentid) values (5,0);insert into country_relation (id,parentid) values (6,0);insert into country_relation (id,parentid) values (7,0);insert into country_relation (id,parentid) values (8,2);insert into country_relation (id,parentid) values (9,2);insert into country_relation (id,parentid) values (10,2);insert into country_relation (id,parentid) values (11,2);insert into country_relation (id,parentid) values (12,11);insert into country_relation (id,parentid) values (13,11);insert into country_relation (id,parentid) values (14,11);insert into country_relation (id,parentid) values (15,11);insert into country_relation (id,parentid) values (16,11);在Oracle 里面对这些操作就比较简单了都是系统提供的。比如下面四种情形1). 查看深度select max(level) level from COUNTRY_RELATION a start with a.parentid is NULLconnect by PRIOR a.id a.PARENTIDorder by level;level----------4已用时间: 00: 00: 00.032). 查看叶子节点select name from(select b.name, connect_by_isleaf isleaffrom COUNTRY_RELATION a inner join country b on (a.id b.id)start with a.parentid is NULL connect by prior a.id a.PARENTID) T where T.isleaf 1;NAME--------------------------------------------------CanadaCentral AmericaIsland NationsAlabamaAlaskaArizonaArkansasCaliforniaSouth AmericaEuropeAsiaAfricaAustralia已选择13行。已用时间: 00: 00: 00.013) 查看ROOT节点select connect_by_root b.namefrom COUNTRY_RELATION a inner join country b on (a.id b.id)start with a.parentid is NULL connect by a.id a.PARENTIDCONNECT_BY_ROOTB.NAME--------------------------------------------------Earth已用时间: 00: 00: 00.014). 查看路径select sys_connect_by_path(b.name,/) pathfrom COUNTRY_RELATION a inner join country b on (a.id b.id)start with a.parentid is NULL connect by prior a.id a.PARENTIDorder by level,a.id;path--------------------------------------------------/Earth/Earth/North America/Earth/South America/Earth/Europe/Earth/Asia/Earth/Africa/Earth/Australia/Earth/North America/Canada/Earth/North America/Central America/Earth/North America/Island Nations/Earth/North America/United States/Earth/North America/United States/Alabama/Earth/North America/United States/Alaska/Earth/North America/United States/Arizona/Earth/North America/United States/Arkansas/Earth/North America/United States/California已选择16行。已用时间: 00: 00: 00.01接下来我们看看在MySQL 里面如何实现上面四种情形前三种都比较简单可以很容易写出SQL。本文原创发布php中文网转载请注明出处感谢您的尊重
