树格式的 MYSQL 输出或添加级别(父子) [英] MYSQL output in tree format OR Adding level (Parent-Child)

问题描述

下面是我桌子上的东西.

Below is what I have in my table.

++++++++++++++++++++
Parent   +  Child
++++++++++++++++++++
  C1     +    G1
  C1     +    G2
  C1     +    G3
  G3     +    D1
  G3     +    D2
  C1     +    G4
  G4     +    D3
  G4     +    D4
  C2     +    G5
  C2     +    G6
  C2     +    G7
  C2     +    G8
 ++++++++++++++++++++

什么，我想要如下使用MYSQL.

What, I want is as below using MYSQL.

C1
  G1
  G2
  G3
    D1
    D2
  G4
    D3
    D4

C2
  G5
  G6
  G7
  G8

如果这在 MYSQL 中可行，请告诉我.输出类似于 TREE.

Please let me know if this is possible in MYSQL. The output is something like TREE.

如果我得到如下所示的新表也可以，这样我就可以使用 这个例子.

++++++++++++++++++++++++++++++++++++++++
Parent   +  Child   + PLevel  + CLevel
++++++++++++++++++++++++++++++++++++++++
  C1     +    G1    +   1    +   2
  C1     +    G2    +   1    +   2
  C1     +    G3    +   1    +   2
  G3     +    D1    +   2    +   3
  G3     +    D2    +   2    +   3
  C1     +    G4    +   1    +   2
  G4     +    D3    +   2    +   3
  G4     +    D4    +   2    +   3
  C2     +    G5    +   1    +   2
  C2     +    G6    +   1    +   2
  C2     +    G7    +   1    +   2
  C2     +    G8    +   1    +   2
++++++++++++++++++++++++++++++++++++++++

注意:我从 1 开始级别(例如，我从 0 开始级别).如果我得到这个级别从 0 开始的新表也可以.

NOTE : I have started level with 1 (in example I have level starting from 0). If I get this new table with level starting from 0 is also fine.

推荐答案

虽然不能用单个查询，但是可以用存储过程...唯一的前置要求，需要多加2个记录到您现有的示例表中，以表示C1"和C2"是顶级...添加一条记录，其中父"字段为空白，子级别为C1"，另一个为C2".这将准备"最顶层的父级.对于后续的层次关联，否则你没有顶层层次的起始基础".它还需要一个主键"列(我在此脚本中将其创建为IDMyTable"，它只是 1-x 连续的，但假设您的表上有一个自动增量列可供使用).

Although you can't do with a single query, you can do with a stored procedure... The only pre-requirement, you need to add 2 more records to your existing sample table to represent that "C1" and "C2" ARE the top level... Add a record where the "Parent" field is blank, and the child level is "C1" and another for "C2". This will "prepare" the top-most parent level. for subsequent hierarchy association, otherwise you have no starting "basis" of the top-level hierarchy. It also requires a "primary key" column (which I've created in this script as "IDMyTable" which is just 1-x sequential, but would assume you have an auto-increment column on your table to use instead).

我已经包含了所有输出列来显示它是如何构建的，但是这个例程的前提是根据预期的列输出创建一个表，但在构建时还需要在下游保存分层表示.为了确保它们在层变深时保持正确的方向，我连接了ID"列——你会看到它在最终结果集中是如何工作的.

I've included all the output columns to show HOW it's built, but the premise of this routine is to create a table based on the expected column outputs, yet extra to hold the hierarchical representation downstream as it's being built. To MAKE SURE they retain the correct orientation as the layers get deeper, I'm concatinating the "ID" column -- you'll see how it works in the final result set.

然后，在最终结果集中，我根据层次结构数据的深度预先填充空格.

Then, in the final result set, I am pre-padding spaces based on however deep the hierarchy data is.

循环将根据在前面结果集中找到的父记录添加任何记录，但前提是尚未添加 ID(防止重复)...

The loop will add any records based on their parent being found in the preceding result set, but only if the ID has not already been added (prevent duplicates)...

要查看循环顺序是如何不断附加的，您可以运行最后一个查询而不使用 order by，然后查看每次迭代如何限定并添加上一个层次结构级别...

To see how the cyclical order was constantly appended to, you can run the last query WITHOUT the order by and see how each iteration qualified and added the previous hierarchy level was applied...

-- --------------------------------------------------------------------------------
-- 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

这篇关于树格式的 MYSQL 输出或添加级别(父子)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！