MYSQL以树形格式输出或者添加级别（Parent-Child） [英] MYSQL output in tree format OR Adding level (Parent-Child)
问题描述
myTable
++++++++++++++++++++
父母+孩子
+++++++++++++++++ +++
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。
C1
G1
G2
G3
D1
D2
G4
D3
D4
C2
G5
G6
G7
G8
请让我知道这是否可能在MYSQL中。输出类似于 TREE 。
更新1
If我得到像下面的新表也很好，这样我就可以使用 this例如。
++++++++++++++++ ++++++++++++++++++++++++
父+子+ 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级开始的新表也是好的。
尽管你不能用单个查询，你可以用一个存储过程来做...唯一的先决条件是，你需要添加2个记录到你现有的示例表中，以表示C1和C2是最高级别的...添加一条记录父字段为空白，子级为C1，另一个为C2。这将准备最高的父母级别。对于后续的层次结构关联，否则您没有顶层结构的起始基础。它还需要一个主键列（我在这个脚本中创建了IDMyTable，它只是1-x顺序的，但是假设你在表上有一个自动增加的列来替代） p>
我已经包含了所有的输出列来展示它的构建方式，但是这个例程的前提是基于预期的列输出创建一个表格，下游的分层表示正在建立。为了让他们保持正确的方向，因为图层越来越深，我正在协调ID列 - 你会看到它是如何在最终的结果集。
然后，在最终的结果集中，我是基于层次结构数据的深层预填充空间。
循环将根据它们父母被发现在前面的结果集，但只有当ID尚未被添加（防止重复）...
为了看看周期性的顺序是如何不断追加到，你可以运行最后一个查询没有顺序，看看每个迭代合格和添加以前的层次结构级别应用... ...
pre $ code> - --------------------------------------------- -----------------------------------
- 例程DDL
- 注意：例程正文之前和之后的注释将不会被服务器存储
- ----------------------------- -------------------------------------------------- -
DELIMI TER $$
CREATE DEFINER =`root` @`localhost` PROCEDURE`GetHierarchy2`（）
BEGIN
- 准备一个层级变量
set @hierlvl ：= 00000;
- 为总行准备一个变量，以便我们知道何时找不到更多行
set @lastRowCount：= 0;
- pre-drop临时表
drop table if exists MyHierarchy;
- 现在，将其创建为您想要的第一个级别...
- 即：所有无父级条目的特定顶级
- 或参数化该功能并要求一个特定的ID。
- 为下一组ID添加额外的列作为标志加载到此。
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 ='';
- 我们从这个层级开始有多少行
set @lastRowCount：= ROW_COUNT（）;
- 我们需要有一个主键，否则我们的UPDATE
语句将不安全的更新命令
alter table MyHierarchy添加主键（IDMyTable） ;
- 现在，继续循环，直到我们没有更多记录
，而@lastRowCount> 0 do
- 现在，加载所有从全集中找到的条目，而不是已经处理的
插入到MyHierarchy中
选择
t1.IDMyTable，
t1.Child作为父，
h1.IDHierLevel +1作为IDHierLevel，
concat_ws（'，'，h1.FullHierarchy，t1.IDMyTable）作为FullHierarchy
from
MyTable t1
加入MyHierarchy h1
on t1.Parent = h1.Parent
left join
t1.IDMyTable上的MyHierarchy h2
IDMyTable = h2.IDMyTable
其中
h2.IDMyTable为null;
set @lastRowCount：= row_count（）;
- 现在，更新层级
set @hierLevel：= @hierLevel +1;
结束;
- 现在返回最终集合
选择
*，concat（lpad（''，1 +（IDHierLevel * 3），''），Parent ）作为ShowHierarchy
从MyHierarchy
按FullHierarchy排序;
END
Below 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 is as 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 output is something like TREE.
Update 1
If I get new table like below is also fine so that 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 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.
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).
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.
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)...
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以树形格式输出或者添加级别（Parent-Child）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
