分层查询需要拉孩子，父母和兄弟姐妹 [英] Hierarchical Query Needs to Pull Children, Parents and Siblings

问题描述

现在可以提取数据，但想知道是否有更好的方法来优化大型数据集的查询.

Can now pull the data, but am wondering if there is a better way to optimize the query for large data sets.

http://sqlfiddle.com/#!4/0ef0c/5

因此，基本上，我希望能够为查询提供给定的组织ID，并使其递归拉动其父母，孩子，兄弟姐妹，姨妈和叔叔.然后拉出与该组织层次结构关联的所有活动.

So basically I want to be able to supply the query a given org id and have it recursively pull its parents, its children, its siblings and its aunts and uncles. And then pull any Activities that are associated with that org hierarchy.

Org1是顶级组织，但它的父级可以为空，也可以不为空.

Org1 is the top level org, but it may or may not have a null parent.

基本上，我在进行上下查询以拉取子代和父代，但似乎只能通过添加另一个查询来获得兄弟姐妹.终于在朋友的帮助下进行了另一个查询，但是对于大型数据集(活动4-5k)而言，查询率非常低.

Basically I was doing an up and down query to pull the children and the parent, but can only seem to get the siblings by adding another query. Finally got to another query with the help of a friend, but its very low on large data sets (4-5k of Activities).

任何见识将不胜感激.

推荐答案

做到这一点的关键是在递归"一词中.为此，请创建一个调用自身的过程.这是给父母的一个例子，但是因为它使用光标在条目之间滚动，所以如何使用它来查找孩子和其他涉及递归的关系应该很简单.

The key to doing this is in the word "recursively". To do that, create a procedure that calls itself. This is an example for parents, but because it's using a cursor to scroll through entries, it should be straightforward for how to use this to find children and other relationships involving recursion.

CREATE OR REPLACE PROCEDURE find_parents( 
  org_id NUMBER, 
  lvl NUMBER DEFAULT 1) AS 

  c_parent table1.id%TYPE;
  c_name table1.name%TYPE;
  CURSOR c_parents (c_id table1.id%TYPE) IS
    SELECT parent, name FROM table1 WHERE (id = c_id);

  BEGIN
    dbms_output.put('-');
    OPEN c_parents(org_id);
    LOOP
      FETCH c_parents INTO c_parent, c_name;
      EXIT WHEN c_parents%notfound;
      dbms_output.put_line('Level ' || lvl || ' parent: [ID: ' || c_parent || ', NAME: ' || c_name || ']');
      find_parents(c_parent, lvl + 1);
    END LOOP;
    CLOSE c_parents;
  END;

这篇关于分层查询需要拉孩子，父母和兄弟姐妹的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！