oracle 邻接模型,【原创】MySQL 模拟Oracle邻接模型树形处理

数据库对层次结构的处理模型有好多种，可以根据自己的需求来设计模型，当然最简单的也是最容易设计的模型就是所谓的邻接模型。在这方面，其他数据库比如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 NULL

connect by PRIOR a.id = a.PARENTID

order by level;

level

----------

4

已用时间: 00: 00: 00.03

2). 查看叶子节点select name from

(

select b.name, connect_by_isleaf "isleaf"

from 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

--------------------------------------------------

Canada

Central America

Island Nations

Alabama

Alaska

Arizona

Arkansas

California

South America

Europe

Asia

Africa

Australia

已选择13行。

已用时间: 00: 00: 00.01

3) 查看ROOT节点select connect_by_root b.name

from COUNTRY_RELATION a inner join country b on (a.id = b.id)

start with a.parentid is NULL connect by a.id = a.PARENTID

CONNECT_BY_ROOTB.NAME

--------------------------------------------------

Earth

已用时间: 00: 00: 00.01

4). 查看路径select sys_connect_by_path(b.name,'/') "path"

from 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

order 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中文网，转载请注明出处，感谢您的尊重！