oracle 层次化查询（行政区划三级级联）

针对“oracle 层次化查询（行政区划三级级联）”，我提供以下的攻略：

1. 数据库设计

在进行层次化查询前，首先需要确保我们的数据库表格设计符合查询要求。假设我们要查询的是中国的行政区划，这里我们以省、市、县三级行政区划作为例子建表。对于一张行政区划的表格，我们至少需要以下几个字段：

• id：区划id，一般为自增主键；
• name：区划名称，如“山东省”、“济南市”、“历下区”等；
• parent_id：父级区划id，省级区划的parent_id为0，市级区划的parent_id为对应的省id，县级区划的parent_id为对应的市id。

根据这种设计，我们就可以通过parent_id进行级联查询了。

2. 数据库表格初始化

为了让示例更加直观，我们可以插入一些测试数据来进行示范。这里以一个较简单的示例进行说明，假设有如下三张表：

province 表格

CREATE TABLE `province` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `parent_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `province` (`id`, `name`, `parent_id`)
VALUES
    (1, '山东省', 0),
    (2, '北京市', 0),
    (3, '河南省', 0)

city 表格

CREATE TABLE `city` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `parent_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `city` (`id`, `name`, `parent_id`)
VALUES
    (1, '济南市', 1),
    (2, '青岛市', 1),
    (3, '郑州市', 3)

district 表格

CREATE TABLE `district` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `parent_id` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `district` (`id`, `name`, `parent_id`)
VALUES
    (1, '历下区', 1),
    (2, '市北区', 2),
    (3, '中原区', 3),
    (4, '温县', 3)

3. 层次化查询

有了上述的准备工作，我们就可以开始进行层次化查询了。以查询山东省为例，我们可以使用如下的SQL语句进行级联查询：

SELECT
    d.id as district_id,
    c.id as city_id,
    p.id as province_id,
    d.name as district_name,
    c.name as city_name,
    p.name as province_name
FROM district d
    INNER JOIN city c ON d.parent_id = c.id
    INNER JOIN province p ON c.parent_id = p.id
WHERE p.id = 1

该语句的具体解读如下：

• 使用INNER JOIN连接district、city和province表格，连接条件为district.parent_id = city.id和city.parent_id = province.id；
• 选择需要查询的字段，包括区划id、区划名称和父级区划id；
• 使用WHERE子句限制查询条件，这里限制省级区划的id为1，也就是查询山东省的结果。

通过这条SQL语句，我们可以得到如下的查询结果：

+--------------+----------+-------------+--------------+-----------+--------------+
| district_id  | city_id | province_id | district_name | city_name | province_name |
+--------------+----------+-------------+--------------+-----------+--------------+
| 1            | 1       | 1          | 历下区       | 济南市    | 山东省        |
| 2            | 2       | 1          | 市北区       | 青岛市    | 山东省        |
+--------------+----------+-------------+--------------+-----------+--------------+

有了这条语句，我们可以很方便地查询到山东省下面的所有市、县名称及其对应的id，进而进行更复杂的操作。

4. 示例1：展示层级列表

我们来看一个比较简单的示例，假设我们需要展示一个层级列表，包括省、市、县的名称和id。我们可以使用如下的SQL语句：

SELECT
    CASE WHEN d.id is not null THEN 3
        WHEN c.id is not null THEN 2
        WHEN p.id is not null THEN 1
    END as level,
    COALESCE(d.id, c.id, p.id) as id,
    COALESCE(d.name, c.name, p.name) as name,
    CASE WHEN d.id is not null THEN c.id
        WHEN c.id is not null THEN p.id
    END as parent_id
FROM
    province p
    LEFT JOIN city c ON p.id = c.parent_id
    LEFT JOIN district d ON c.id = d.parent_id
ORDER BY level, id

该语句具体解释如下：

• 使用LEFT JOIN连接province、city和district表格，连接条件为p.id = c.parent_id和c.id = d.parent_id；
• 通过CASE WHEN和COALESCE函数进行数据处理，当查询的是省级区划时，显示level为1，id为该省id，名称为该省名称，parent_id为空；当查询的是市级区划时，level为2，id为该市id，名称为该市名称，parent_id为该市所在的省id；当查询的是县级区划时，level为3，id为该县id，名称为该县名称，parent_id为该县所在的市id；
• 使用ORDER BY对结果进行排序，按level和id进行排序。

最终的查询结果如下：

+-------+----+--------+-----------+
| level | id | name   | parent_id |
+-------+----+--------+-----------+
| 1     | 1  | 山东省 | NULL      |
| 1     | 2  | 北京市 | NULL      |
| 1     | 3  | 河南省 | NULL      |
| 2     | 1  | 济南市 | 1         |
| 2     | 2  | 青岛市 | 1         |
| 2     | 3  | 郑州市 | 3         |
| 3     | 1  | 历下区 | 1         |
| 3     | 2  | 市北区 | 2         |
| 3     | 3  | 中原区 | 3         |
| 3     | 4  | 温县   | 3         |
+-------+----+--------+-----------+

有了这样的结果，我们就可以根据level来决定不同的展示方式，比如使用缩进或者图标来标识每个区划的层次。

5. 示例2：根据子节点查询父节点

接下来我们来看一个比较具有实用价值的示例，假设我们已知了某一县级区划的id，现在需要查询该区划所在的省市名称和id。我们可以使用如下的SQL语句：

SELECT
    p.id as province_id,
    p.name as province_name,
    c.id as city_id,
    c.name as city_name
FROM
    district d
    LEFT JOIN city c ON d.parent_id = c.id
    LEFT JOIN province p ON c.parent_id = p.id
WHERE
    d.id = :districtId

该语句具体解释如下：

• 使用LEFT JOIN连接district、city和province表格，连接条件为district.parent_id = city.id和city.parent_id = province.id；
• 选择需要查询的字段，包括省市的id和名称；
• 使用WHERE语句限制查询条件，这里限制了查询的区划id为传入参数:districtId。

最终的查询结果如下：

+--------------+--------------+----------+----------+
| province_id  | province_name | city_id | city_name |
+--------------+--------------+----------+----------+
| 3            | 河南省       | 3       | 郑州市   |
+--------------+--------------+----------+----------+

通过这样的查询，我们就可以快速地查询出一个县级区划所在的省市名称和id，方便我们进行管理和展示。

以上就是针对“oracle 层次化查询（行政区划三级级联）”的完整攻略，希望能对您有所帮助。