Geek asks and answers

MYSQL output in tree format OR Adding a level (parent)

The following is what I have in my table.

MYTABLE

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

What, I want, as shown below, using MYSQL.

C1
  G1
  G2
  G3
    D1
    D2
  G4
    D3
    D4

C2
  G5
  G6
  G7
  G8

Please let me know if this is possible in MYSQL. The result looks like TREE .

Update 1

If I get a new table, as shown below, is fine too, so I can use this example .

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

NOTE: I started the level with 1 (in the example I have a level starting with 0). If I get this new table with a level starting at 0, fine too.

+3
source share
4 answers

, ... , 2 , , "C1" "C1", C2 " ... , " ", " C1 ", -" C2 ". " " . , " " . " "( script " IDMyTable", 1- , , ).

, , , , . , , , "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 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
0

MySQL RDBMS . , , .

, , , .

0

.

function fn_CalcLevel(int @ID) 
As Begin
  Declare @ParentID int
  Select @ParentID = ParentID From Table1 where ID = @ID

  IF (@ParentID IS NULL) Return 1 Else Return 1+fn_CalcLevel(@ParentID)
End

,

Select *, fn_CalcLevel(Table1.ID) as Level
From Table1
0

If you slightly modified the table, you could use something like:

  SELECT Child,CONCAT(LPAD('',Clevel,' '),Child),etc from tablename

Restructuring is that you will need a root node as a row with a parent node of 0. You can add your own order with a parent / child / C level to get the sequence as desired.

I know this happened a few years ago, but it can save someone a little more effort!

0
source

More articles:


All Articles