[Original] tree-like processing of Oracle neighbor model simulation in MySQL

Databases have many processing models for hierarchies. You can design models based on your needs. Of course, the simplest and easiest model to design is the so-called adjacent model. In this regard, other databases such as Oracle

Databases have many processing models for hierarchies. You can design models based on your needs. Of course, the simplest and easiest model to design is the so-called adjacent model. In this regard, other databases such as Oracle

Databases have many processing models for hierarchies. You can design models based on your needs. Of course, the simplest and easiest model to design is the so-called adjacent model. In this regard, other databases, such as Oracle, provide the ready-made analysis method connect by, while MySQL seems a little weak in this regard. However, you can use the MySQL stored procedure to implement similar ORACLE analysis functions.

In this way, create a simple data table.

Create table country (id number (2) not null, name varchar (60) not null); create table country_relation (id number (2), parentid number (2 ));

Insert some data

-- Table country. insert into country (id, name) values (0, 'global'); 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, 'asa'); insert into country (id, name) values (6, 'afica '); 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 (); 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 (); insert into country_relation (id, parentid) values (); insert into country_relation (id, parentid) values (); insert into country_relation (id, parentid) values (); insert into country_relation (id, parentid) values );

In Oracle, these operations are relatively simple and are provided by the system.

For example, the following four situations:

1). view the depth,

Select max (level) "level" from COUNTRY_RELATION a start with. parentid is NULLconnect by PRIOR. id =. PARENTIDorder by level; level ---------- 4 used time: 00: 00: 00.03

2). View leaf nodes

Select name from (select B. name, connect_by_isleaf "isleaf" from COUNTRY_RELATION a inner join country B on (. id = B. id) start with. parentid is NULL connect by prior. id =. PARENTID) T where T. "isleaf" = 1; NAME your CanadaCentral AmericaIsland nationsalabamaalaskaarizon#kansascaliforniasouth AmericaEuropeAsiaAfricaAustralia has selected 13 rows. Used time: 00: 00: 00.01

3) view the ROOT node

Select connect_by_root B. namefrom COUNTRY_RELATION a inner join country B on (. id = B. id) start with. parentid is NULL connect by. id =. PARENTID CONNECT_BY_ROOTB.NAME -------------------------------------------------- Earth time in use: 00: 00: 00.01

4). view the path

Select sys_connect_by_path (B. name, '/') "path" from COUNTRY_RELATION a inner join country B on (. id = B. id) start with. parentid is NULL connect by prior. id =. PARENTID order by level,. id; path ----------------------------------------------/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/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 has 16 rows selected. Used time: 00: 00: 00.01

Next, let's take a look at how to implement the above four situations in MySQL:

The first three methods are relatively simple and can easily write SQL statements.