mysql - 递归动态查询

Question

我有一个递归 SQL 查询，我可以在其中获取每个办公室的 ID 的层次结构(一个办公室属于更高级别的办公室等) - 受到 @leftclickben 在 How to do the Recursive SELECT query in MySQL? 中的回答的启发。 :

select @pv := o.office_id, o.display_name, (
                        select concat(concat(group_concat(@pv := t.parent_office_id order by t.parent_office_id asc SEPARATOR '.' ), '.'), t.office_id)  pivot
                        from (select * from office order by (CASE WHEN parent_office_id < office_id THEN parent_office_id  END) DESC,
                                            (CASE WHEN parent_office_id > office_id THEN parent_office_id  END) ASC) t 
                                                where t.office_id = @pv 
                                            ) 'hierarchy'
from office o
group by o.office_id
order by o.office_id asc, o.parent_office_id desc
;

要使此查询正常工作，需要验证完美的下降或上升。此属性未在我的数据中进行验证，因此我必须在子查询中利用选择性order by。

我获得了非常令人鼓舞的结果(80% 的准确率)，我想知道是否有人可以建议更多的技术来获得更好的结果？

示例:

- for some office (ID = 97), its hierarchy is 1.2.4.14.97 (accurate value);

- for another case (ID = 101), I get: 111.101 (broken hierarchy);

简而言之，所有结果都必须以 1 开头。

一个好的样本:

http://sqlfiddle.com/#!9/82f13/1

Answer 1

根据@Hart CO的建议，我用两个联合的三个查询解决了这个问题:

(select @pv := o.office_id, o.display_name, ifnull((
                        select concat(concat(group_concat(@pv := t.parent_office_id order by t.parent_office_id asc SEPARATOR '.' ), '.'), t.office_id)  pivot
                        from (select * from office order by parent_office_id desc) t 
                                                where t.office_id = @pv 
                                            ), '1.') 'hierarchy'
from office o
where office_id not in (26, 27, 28, 29, 30, 32, 33, 34, 41, 57, 58, 59, 60, 61, 62, 63, 64, 73, 74, 75, 76, 77, 79, 82, 91, 96, 101, 102, 103, 104)
group by o.office_id
order by o.parent_office_id desc)
 union
(

select @pv := o.office_id, o.display_name, (
                        select concat(concat(group_concat(@pv := t.parent_office_id order by t.parent_office_id asc SEPARATOR '.' ), '.'), t.office_id)  pivot
                        from (select * from office order by parent_office_id  DESC) t 
                                                where t.office_id = @pv 
                                            ) 'hierarchy'
from office o
where office_id in (26, 27, 28, 29, 30, 32, 33, 34, 41, 57, 58, 59, 60, 61, 62, 63, 64, 91, 96, 101, 102, 103, 104)
group by o.office_id
order by o.parent_office_id desc
)
union
(select @pv := o.office_id, o.display_name, (
                        select concat(concat(group_concat(@pv := t.parent_office_id order by (select parent_office_id from office where office_id = t.parent_office_id)  asc SEPARATOR '.' ), '.'), t.office_id)  pivot
                        from (select * from office oo order by (select parent_office_id from office where office_id = oo.parent_office_id) deSC) t 
                                                where t.office_id = @pv 
                                            ) 'hierarchy'
from office o
where office_id in (73, 74, 75, 76, 77, 79, 82)
group by o.office_id
 order by o.parent_office_id desc
);

确实，第一个查询很简单:parent_id 更小。

对于第二个查询，底层的parent_id 更大。

对于第三个查询，父查询的parent_id更大，这就是为什么我为group_concat和别名为的子查询选择了按 block 排序的子查询>t.

我终于可以继续我的 ETL 了。