mysql - Mysql-如何选择 child 列表的所有 parent ？

我有一个表，其中每个记录包含他的父亲，这个层次结构可能有多达6个级别。下面的查询检索部门“ABCD”的所有家长。这个查询工作得很好。

SELECT D2.id, D2.name, D2.id_parent
FROM (
    SELECT
        @d AS _id,
        (SELECT @d := id_parent FROM department WHERE id = _id) AS id_parent,
        @l := @l + 1 AS level
    FROM
        (SELECT
            @d := (select id from department where name = 'DEPARTMENT ABCD'),
            @l := 0
        ) initial_level,
        department D
    WHERE @d <> 0
) D1
JOIN department D2 ON D1._id = D2.id
ORDER BY D1.level DESC;

但是，如果我想同时获取多个子对象的父对象（在下面的查询中使用like），则会得到错误：“错误代码：1242。子查询返回多行。
出现错误“Subquery返回多行”的查询：

SELECT D2.id, D2.name, D2.id_parent
FROM (
    SELECT
        @d AS _id,
        (SELECT @d := id_parent FROM department WHERE id = _id) AS id_parent,
        @l := @l + 1 AS level
    FROM
        (SELECT
            @d := (select id from department where name like 'DEPARTMENT %A%'),
            @l := 0
        ) initial_level,
        department D
    WHERE @d <> 0
) D1
JOIN department D2 ON D1._id = D2.id
ORDER BY D1.level DESC;

我怎么能同时得到不止一个孩子的父母呢？
我在SQL Fiddle中添加了一个示例：http://sqlfiddle.com/#!9/f182fb/3

最佳答案

考虑到层次结构可能有6个级别，我建议将表加入6次，以获取所选部门的所有祖先：

select     distinct id, name, id_parent
from       (
            select      d6.id_parent as id6,
                        d5.id_parent as id5,
                        d4.id_parent as id4,
                        d3.id_parent as id3,
                        d2.id_parent as id2,
                        d1.id_parent as id1,
                        d1.id        as id0
            from        department d1
            left join   department d2 on d2.id = d1.id_parent
            left join   department d3 on d3.id = d2.id_parent
            left join   department d4 on d4.id = d3.id_parent
            left join   department d5 on d5.id = d4.id_parent
            left join   department d6 on d6.id = d5.id_parent
            where       d1.name like 'DEPARTMENT A%'
           ) as h
inner join department d on d.id in (id0, id1, id2, id3, id4, id5, id6)
order by   1;

SQL fiddle

关于mysql - Mysql-如何选择 child 列表的所有 parent ？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37684235/