尽管您无法使用单个查询进行操作，但是可以使用存储过程进行操作…唯一的先决条件是，您需要在现有示例表中再添加2条记录，以表示“ C1”和“ C2”
ARE顶层…添加一条记录，其中“父级”字段为空白，子级为“ C1”，另一级为“
C2”。这将“准备”最上层的父级。进行后续层次结构关联，否则您将没有顶层层次结构的起始“基础”。它还需要一个“主键”列（我在此脚本中将其创建为“
IDMyTable”，它只是1-x顺序的，但是假设您在表上有一个自动增量列可以代替使用）。
我已经包括了所有输出列以显示其构建方式，但是此例程的前提是根据预期的列输出创建表，但还要额外保留一些层次表示形式，以供构建时使用。为了确保它们在层变得更深时保持正确的方向，我在“
ID”列中加上了“ ID”列-您将看到它在最终结果集中的工作方式。
然后，在最终结果集中，我将根据层次结构数据的深度填充空白。
循环将基于在上一个结果集中找到的父记录添加任何记录，但前提是尚未添加ID（防止重复记录）…
要查看循环顺序是如何不断添加的，您可以运行不带该顺序的最后一个查询，并查看每个迭代如何限定条件并添加上一个层次结构级别…
-- ---------------------------------------------------------------------------------- Routine DDL-- Note: comments before and after the routine body will not be stored by the server-- --------------------------------------------------------------------------------DELIMITER $$CREATE DEFINER=`root`@`localhost` PROCEDURE `GetHierarchy2`()BEGIN -- prepare a hierarchy level variable set @hierlvl := 00000; -- prepare a variable for total rows so we know when no more rows found set @lastRowCount := 0; -- pre-drop temp table drop table if exists MyHierarchy; -- now, create it as the first level you want... -- ie: a specific top level of all "no parent" entries -- or parameterize the function and ask for a specific "ID". -- add extra column as flag for next set of ID's to load into this. create table MyHierarchy as select t1.IDMyTable, t1.Child AS Parent, @hierlvl as IDHierLevel, cast( t1.IDMyTable as char(100)) FullHierarchy from MyTable t1 where t1.Parent is null OR t1.Parent = ''; -- how many rows are we starting with at this tier level set @lastRowCount := ROW_COUNT(); -- we need to have a "primary key", otherwise our UPDATe -- statement will nag about an unsafe update command alter table MyHierarchy add primary key (IDMyTable); -- NOW, keep cycling through until we get no more records while @lastRowCount > 0 do -- NOW, load in all entries found from full-set NOT already processed insert into MyHierarchy select t1.IDMyTable, t1.Child as Parent, h1.IDHierLevel +1 as IDHierLevel, concat_ws( ',', h1.FullHierarchy, t1.IDMyTable ) as FullHierarchy from MyTable t1 join MyHierarchy h1 on t1.Parent = h1.Parent left join MyHierarchy h2 on t1.IDMyTable = h2.IDMyTable where h2.IDMyTable is null; set @lastRowCount := row_count(); -- now, update the hierarchy level set @hierLevel := @hierLevel +1; end while; -- return the final set now select *, concat( lpad( ' ', 1 + (IDHierLevel * 3 ), ' ' ), Parent ) as ShowHierarchy from MyHierarchy order by FullHierarchy;END
