MySQL中向下查询_MySQL/Oracle与向上查询/向下查询

最近项目涉及到数据库向上查询和向下查询。举例来说，以中国行政区划为例，中国行政区分分为：省-市-区县-乡镇，行政区划和行政区划之间通过parent_id形成上下级关系。

以下是一个典型的标准行政区划表，整个中国的行政区划，是一个完整的树形结构：

我们可以通过任意一条记录的parent_id，查询到它的上级行政区划。

现在就有两个不同的业务需求：

1. 通过不知道层级的areaId，查询到它的所有上级；——向上查询

2. 根据一个不知道层级的areaId，查询它的所有子级。——向下查询

一、向上查询

1.1 MySQL实现

SELECT T2.area_id,T2.area_name

FROM (

SELECT

@r AS _id,

(SELECT @r := parent_id FROM base_area WHERE area_id = _id) AS parent_id,

@l := @l + 1 AS lvl

FROM

(SELECT @r := '130102', @l := "0") vars,

base_area h

WHERE @r <> "100000") T1

LEFT JOIN base_area T2

ON T1._id = T2.area_id

查询结果：

扩展

根据任意级别的行政区划ID，查询这个行政区划的全路径。比如我传入130102，这是长安区的行政区划ID，我能获得一个：河北省 石家庄市 长安区。

SELECT area_id as areaId,GROUP_CONCAT(area_name order by area_level separator ' ') as areaFullName

FROM (

SELECT

@r AS _id,

(SELECT @r := parent_id FROM base_area WHERE area_id = _id) AS p_id,

@l := @l + 1 AS lvl

FROM

(SELECT @r := '130102', @l := 0) vars,

base_area h

WHERE @r != 100000

) T1

JOIN base_area T2

ON T1._id = T2.area_id

查询结果：

1.2 Oracle实现

SELECT DISTINCT

area_id,area_name

FROM

base_area

where area_id != '100000'

START WITH area_id = '130102' connect BY prior parent_id = area_id

查询结果：

扩展

根据任意级别的行政区划ID，查询这个行政区划的全路径。比如我传入130102，这是长安区的行政区划ID，我能获得一个：河北省 石家庄市 长安区。

select area_id,area_name

from(

select

area_id, replace(wm_concat(area_name) over(order by area_level),',',' ') area_name

from(

select area_id,parent_id,area_name,area_level

from base_area

where area_id != 100000

start with area_id = '130102'

connect by prior parent_id = area_id

)

order by area_id desc

) where rownum = 1;

查询结果：

二、向下查询

1.1 MySQL实现

SELECT

area_id,area_name

FROM

(

SELECT

@ids AS _ids,

( SELECT @ids := GROUP_CONCAT( area_id ) FROM base_area WHERE FIND_IN_SET( parent_id, @ids ) ) AS cids,

@l := @l + 1 AS LEVEL

FROM

base_area,

( SELECT @ids := '130000', @l := 0 ) b

WHERE @ids IS NOT NULL ) id,

base_area DATA

WHERE

FIND_IN_SET(

DATA.area_id,

ID._ids)

查询结果：

1.2 Oracle实现

select

area_id,area_name

from

base_area

start with

parent_id = '130000'

connect by prior

area_id = parent_id