树格式的MYSQL输出或添加级别(父子)
以下是我的表.
我的表
++++++++++++++++++++++家长 + 孩子++++++++++++++++++++++C1+G1C1+G2C1+G3G3+D1G3+D2C1+G4G4+D3G4 + D4C2+G5C2+G6C2+G7C2+G8++++++++++++++++++++++什么，我想要的是下面使用MYSQL.
C1G1G2G3D1D2G4D3D4C2G5G6G7G8请让我知道这在 MYSQL 中是否可行.输出类似于TREE.
更新 1
如果我得到像下面这样的新表也很好，这样我就可以使用 这个例子.
++++++++++++++++++++++++++++++++++++++++父级 + 子级 + PLevel + CLevel++++++++++++++++++++++++++++++++++++++++++C1 + G1 + 1 + 2C1 + G2 + 1 + 2C1 + G3 + 1 + 2G3 + D1 + 2 + 3G3 + D2 + 2 + 3C1 + G4 + 1 + 2G4 + D3 + 2 + 3G4 + D4 + 2 + 3C2 + G5 + 1 + 2C2 + G6 + 1 + 2C2 + G7 + 1 + 2C2 + G8 + 1 + 2++++++++++++++++++++++++++++++++++++++++++注意:我从 1 开始级别(在示例中我从 0 开始级别).如果我得到这个级别从 0 开始的新表也可以.
解决方案虽然不能用单个查询做，但是用存储过程可以做... 唯一的前置要求，还需要添加2个记录到您现有的示例表以表示C1"和C2"是顶级...添加一个记录，其中父"字段为空白，子级别为C1"，另一个为C2".这将准备"最顶层的父级.对于后续的层次结构关联，否则您没有顶级层次结构的起始基础".它还需要一个主键"列(我在此脚本中将其创建为IDMyTable"，它只是 1-x 顺序，但假设您的表中有一个自动增量列可供使用).
我已经包含了所有的输出列来展示它是如何构建的，但是这个例程的前提是基于预期的列输出创建一个表，但额外的用于在构建时保存下游的分层表示.为了确保它们随着层的加深而保持正确的方向，我将ID"列连接起来——你会看到它在最终结果集中是如何工作的.
然后，在最终结果集中，我根据层次结构数据的深度预填充空间.
循环将根据在前面的结果集中找到的父记录添加任何记录，但前提是 ID 尚未添加(防止重复)...
要查看循环顺序是如何不断附加到的，您可以在没有顺序的情况下运行最后一个查询，并查看每次迭代如何限定和添加先前的层次结构级别...
-- --------------------------------------------------------------------------- 常规 DDL-- 注意:例程正文前后的注释不会被服务器存储----------------------------------------------------------------------------------分隔符 $$CREATE DEFINER=`root`@`localhost` PROCEDURE `GetHierarchy2`()开始-- 准备一个层次级别的变量设置@hierlvl := 00000;-- 为总行数准备一个变量，以便我们知道何时找不到更多行设置@lastRowCount := 0;-- 预删除临时表如果存在 MyHierarchy，则删除表；-- 现在，将其创建为您想要的第一个级别...-- 即:所有无父"条目的特定顶级-- 或者参数化函数并要求一个特定的ID".-- 添加额外的列作为下一组 ID 的标志以加载到此.创建表 MyHierarchy 作为选择t1.IDMyTable，t1.Child 作为父级，@hierlvl 作为 IDHierLevel，cast(t1.IDMyTable as char(100)) FullHierarchy从我的表 t1在哪里t1.Parent 为空或 t1.Parent = '';-- 在这个层级我们从多少行开始设置@lastRowCount := ROW_COUNT();-- 我们需要有一个主键"，否则我们的 UPDATE-- 语句会唠叨不安全的更新命令更改表 MyHierarchy 添加主键(IDMyTable)；-- 现在，继续循环直到我们没有更多记录而@lastRowCount >0 做-- 现在，加载从尚未处理的全套中找到的所有条目插入到 MyHierarchy选择t1.IDMyTable，t1.Child 作为父母，h1.IDHierLevel +1 作为 IDHierLevel，concat_ws( ',', h1.FullHierarchy, t1.IDMyTable ) 作为 FullHierarchy从我的表 t1加入 MyHierarchy h1在 t1.Parent = h1.Parent左连接我的层次结构 h2在 t1.IDMyTable = h2.IDMyTable在哪里h2.IDMyTable 为空；设置@lastRowCount := row_count();-- 现在，更新层次结构级别设置@hierLevel := @hierLevel +1;结束一段时间；-- 现在返回最终集选择*, concat( lpad( ' ', 1 + (IDHierLevel * 3 ), ' ' ), Parent ) as ShowHierarchy来自 MyHierarchy按 FullHierarchy 排序；结尾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
相关文章