如何使用mysql循环将mysql查询写入按父-子分组的顺序列表？

Question

我有一个User表，它有以下列：

• id
• 名字
• parent_id
• 级别

我想订购这个表作为祖先(https://github.com/stefankroes/ancestry)。在用户表中不添加祖先列的情况下，是否可以在Mysql中创建临时祖先列。

更新

我想列出mysql中按临时列祖先排序的用户，它应该根据每个用户层次结构的“父_id”具有值。

Answer 1

看看下面这篇文章..。我想这正是你想要的：

http://explainextended.com/2009/03/19/hierarchical-queries-in-mysql-adding-ancestry-chains/

这描述了如何进行/使用继承查询来获取树结构中节点的祖先。

更新

由于您的级别有限，下面这样的查询将完成这项工作。下面的查询是针对7个级别的，尽管它需要优化，因为它是一个工作的粗略/草案。

select
SUBSTRING_INDEX(SUBSTRING(ancestry, 1, CHAR_LENGTH(ancestry) - 1), ',', -1) as ID,
SUBSTRING(ancestry, 1, CHAR_LENGTH(ancestry) - 1) as Ancestry
from
(
  select 
  concat(
  IF(NOT u1.id IS NULL,concat(u1.id,','),''),
  IF(NOT u2.id IS NULL,concat(u2.id,','),''),
  IF(NOT u3.id IS NULL,concat(u3.id,','),''),
  IF(NOT u4.id IS NULL,concat(u4.id,','),''),
  IF(NOT u5.id IS NULL,concat(u5.id,','),''),
  IF(NOT u6.id IS NULL,concat(u6.id,','),''),
  IF(NOT u7.id IS NULL,concat(u7.id,','),''),
  IF(NOT u8.id IS NULL,concat(u8.id,','),'')
  ) as ancestry
  from User u1 
  left join User u2 on u1.id = u2.parent_id
  left join User u3 on u2.id = u3.parent_id
  left join User u4 on u3.id = u4.parent_id
  left join User u5 on u4.id = u5.parent_id
  left join User u6 on u5.id = u6.parent_id
  left join User u7 on u6.id = u7.parent_id
  left join User u8 on u7.id = u8.parent_id
)
as ancestryTable;

SQLFiddle：http://sqlfiddle.com/#!2/93c1f/46

更新-优化版本和更正

select ID, ancestry
from
(
  select 
  u8.ID as ID,  
    concat(
    IF(u1.id IS NULL,'', IF(u2.id IS NULL,u1.id,concat(u1.id,','))),
    IF(u2.id IS NULL,'', IF(u3.id IS NULL,u2.id,concat(u2.id,','))),
    IF(u3.id IS NULL,'', IF(u4.id IS NULL,u3.id,concat(u3.id,','))),
    IF(u4.id IS NULL,'', IF(u5.id IS NULL,u4.id,concat(u4.id,','))),
    IF(u5.id IS NULL,'', IF(u6.id IS NULL,u5.id,concat(u5.id,','))),
    IF(u6.id IS NULL,'', IF(u7.id IS NULL,u6.id,concat(u6.id,','))),
    IF(u7.id IS NULL,'', IF(u8.id IS NULL,u7.id,concat(u7.id,','))),
    IF(u8.id IS NULL,'',u8.id)
    )as ancestry, 
     u1.id as a1,  u2.id as a2,  u3.id as a3,  u4.id as a4,
     u5.id as a5,  u6.id as a6,  u7.id as a7,  u8.id as a8
    from User u1 
    right join User u2 on  u2.parent_id = u1.id
    right join User u3 on  u3.parent_id = u2.id
    right join User u4 on  u4.parent_id = u3.id
    right join User u5 on  u5.parent_id = u4.id
    right join User u6 on  u6.parent_id = u5.id
    right join User u7 on  u7.parent_id = u6.id
    right join User u8 on  u8.parent_id = u7.id       
) as Ancestry
order by ancestry.ancestry

JSFiddle：http://sqlfiddle.com/#!2/93c1f/105