一个天真树集上的嵌套联接

Question

假设我有以下类似示例的内容：

CREATE TABLE NaiveTable
{
    id BIGINT NOT NULL, 
    parentId BIGINT NULL,
    name VARCHAR(20) NULL,
    CONSTRAINT hierarchy FOREIGN KEY (parentId) REFERENCES NaiveTable(id)
    PRIMARY KEY (id)
}

作为说明，parentId 是对 NaiveTable 的 id 的引用（如果我错过了确切的语法）。

数据大致如下。

+---------+----------+----------+
|   id    | parentId |  name    | 
+---------+----------+----------+
|   1     |   null   |  node1   |
+---------+----------+----------+
|   2     |     1    |  node2   |
+---------+----------+----------+
|   3     |     1    |  node3   |
+---------+----------+----------+
|   4     |     2    |  node4   |
+---------+----------+----------+

列名包含一些不相关的标签。我正在寻找一种在MySQL表上构建SQL查询的方法，其中所有信息都将被扁平化并按层次结构排序，如下所示：

node 1, depth 0
node 2, depth 1
node 4, depth 2
node 3, depth 1

注意：我无法以任何方式修改数据库架构。 我只能创建SQL查询。 由于MySQL不支持它，我也不能使用WITH关键字。 有没有办法制作这样的查询？ 但是，对于深度为二或以上的任何解决方案都被视为足够好。

编辑：如果你喜欢实验，这里是SQL fiddle。

- Daniel Fath

---

排序是从树的深度优先进行的吗？此外，您能否编写一个存储过程呢？我怀疑那会更容易一些。 - jmilloy

排序是深度优先。是的，我考虑过使用过程，但我更喜欢一个SQL查询。我想指出，即使是一个能够工作到三层深度的查询也将被视为有效答案。 - Daniel Fath

这个问题的答案展示了两种可能的原则：存储过程和会话变量。 - PHeiberg

Answer 1

如果数据库的架构是固定的，您无法添加/编辑任何表格，则您所能做的就是在内存中（使用某种编程语言）构建树，然后尝试在内存中计算每个节点的深度。因此，我的答案是您无法仅使用一个查询生成所需的输出！

但是，如果您可以修改数据库的架构，那么您可能需要查看this。

- Mehran

---

这是“正确”的解决方案。事后添加层次结构而不改变结构是不可能的。 - Daniel Fath

Answer 2

这是一个小的递归存储过程，可以无限深度地工作。这是我的第一个存储过程，请告诉我如何改进它。

DROP PROCEDURE IF EXISTS tree_reader;
DELIMITER $$
CREATE PROCEDURE tree_reader(IN parent INT, IN depth INT) READS SQL DATA
BEGIN

  DECLARE id_val INT;
  DECLARE name_val VARCHAR(255);
  DECLARE _table_name VARCHAR(255);

  DECLARE no_more_rows BOOLEAN;
  DECLARE cur CURSOR FOR
      SELECT id, name
      FROM tree
      WHERE IF(parent IS NULL, parentid IS NULL, parentid = parent);

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows = TRUE;

  -- create temporary table on outer call only --
  IF depth=0 THEN
    DROP TABLE IF EXISTS _tree;
    CREATE TEMPORARY TABLE _tree (id INT, name VARCHAR(255), depth INT);
  END IF;

  OPEN cur;

  -- loop with recursion --
  tree_loop: LOOP
    FETCH cur INTO id_val, name_val;
    IF no_more_rows THEN LEAVE tree_loop; END IF;
    INSERT INTO _tree VALUES (id_val, name_val, depth);
    CALL tree_reader(id_val, depth + 1);

  END LOOP tree_loop;

  CLOSE cur;

  -- output results on outer call only --
  IF depth=0 THEN
    SELECT * FROM _tree;
    DROP TABLE _tree;
  END IF;
END
$$
DELIMITER ;

一些注意事项：

• 调用过程：CALL tree_reader(NULL, 0);

• 您可以使用任何父节点ID，但第二个参数始终为0。在实践中，我可能会添加一个不带参数并提供整个树的包装器过程。

• 您必须设置递归限制才能使其工作：SET max_sp_recursion_depth = 6;（我随意选择了6）。

- jmilloy