将部门层次结构打印到表中

Question

我有一个部门的关系表如下：

+---------------+----------------+
|     Dept.     | superior Dept. |
+---------------+----------------+
| "00-01"       | "00"           |
| "00-02"       | "00"           |
| "00-01-01"    | "00-01"        |
| "00-01-02"    | "00-01"        |
| "00-01-03"    | "00-01"        |
| "00-02-01"    | "00-02"        |
| "00-02-03"    | "00-02"        |
| "00-02-03-01" | "00-02-03"     |
+---------------+----------------+

现在，我想按它们的层次列出如下：

+-----------+--------------+--------------+--------------+
| Top Dept. | 2-tier Dept. | 3-tire Dept. | 4-tier Dept. |
+-----------+--------------+--------------+--------------+
|        00 |              |              |              |
|           | 00-01        |              |              |
|           |              | 00-01-01     |              |
|           |              | 00-01-02     |              |
|           | 00-02        |              |              |
|           |              | 00-02-01     |              |
|           |              | 00-02-03     |              |
|           |              |              | 00-02-03-01  |
+-----------+--------------+--------------+--------------+

我可以使用下面的代码构建关系树：

TreeNode.java

import java.util.LinkedList;
import java.util.List;

public class TreeNode {
  public String value;
  public List children = new LinkedList();

  public TreeNode(String rootValue) {
    value = rootValue;
  }

}

PairsToTree.java

import java.util.*;

public class PairsToTree {

  public static void main(String[] args) throws Exception {
    // Create the child to parent hash map
    Map <String, String> childParentMap = new HashMap<String, String>(8);
    childParentMap.put("00-01", "00");
    childParentMap.put("00-02", "00");
    childParentMap.put("00-01-01", "00-01");
    childParentMap.put("00-01-02", "00-01");
    childParentMap.put("00-01-03", "00-01");
    childParentMap.put("00-02-01", "00-02");
    childParentMap.put("00-02-03", "00-02");
    childParentMap.put("00-02-03-01", "00-02-03");

    // All children in the tree
    Collection<String> children = childParentMap.keySet();

    // All parents in the tree
    Collection<String> values = childParentMap.values();

    // Using extra space here as any changes made to values will
    // directly affect the map
    Collection<String> clonedValues = new HashSet();
    for (String value : values) {
      clonedValues.add(value);
    }

    // Find parent which is not a child to any node. It is the
    // root node
    clonedValues.removeAll(children);

    // Some error handling
    if (clonedValues.size() != 1) {
      throw new Exception("More than one root found or no roots found");
    }

    String rootValue = clonedValues.iterator().next();
    TreeNode root = new TreeNode(rootValue);

    HashMap<String, TreeNode> valueNodeMap = new HashMap();
    // Put the root node into value map as it will not be present
    // in the list of children
    valueNodeMap.put(root.value, root);

    // Populate all children into valueNode map
    for (String child : children) {
      TreeNode valueNode = new TreeNode(child);
      valueNodeMap.put(child, valueNode);
    }

    // Now the map contains all nodes in the tree. Iterate through
    // all the children and
    // associate them with their parents
    for (String child : children) {
      TreeNode childNode = valueNodeMap.get(child);
      String parent = childParentMap.get(child);
      TreeNode parentNode = valueNodeMap.get(parent);
      parentNode.children.add(childNode);
    }

    // Traverse tree in level order to see the output. Pretty
    // printing the tree would be very
    // long to fit here.
    Queue q1 = new ArrayDeque();
    Queue q2 = new ArrayDeque();
    q1.add(root);
    Queue<TreeNode> toEmpty = null;
    Queue toFill = null;
    while (true) {
      if (false == q1.isEmpty()) {
        toEmpty = q1;
        toFill = q2;
      } else if (false == q2.isEmpty()) {
        toEmpty = q2;
        toFill = q1;
      } else {
        break;
      }
      while (false == toEmpty.isEmpty()) {
        TreeNode node = toEmpty.poll();
        System.out.print(node.value + ", ");
        toFill.addAll(node.children);
      }
      System.out.println("");
    }
  }

}

但无法解决如何格式化输出，使其类似于表。或者是否有sql语句/存储过程(如这个问题)来执行此操作？

编辑：部门。名称只是一个示例，为了方便起见，它可以是任意字符串。

Answer 1

下面是一个基于数据的测试用例，它以表格的方式打印部门，试着运行它，看看会发生什么。

package test;

import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public class DeptHierachy {
    public Map<String,List<String>> deptMap= new HashMap<>();
    public static void main(String[] args) {
        TreeNode tn=new TreeNode("00");
        tn.addChildTo("00-01", "00");
        tn.addChildTo("00-02", "00");
        tn.addChildTo("00-01-01", "00-01");
        tn.addChildTo("00-01-02", "00-01");
        tn.addChildTo("00-01-03", "00-01");
        tn.addChildTo("00-02-01", "00-02");
        tn.addChildTo("00-02-03", "00-02");
        tn.addChildTo("00-02-03-01", "00-02-03");
        tn.print();
        //System.out.println("max height=" + tn.maxHeigth());
    }



    public static class TreeNode {
          public String value;
          public List<TreeNode> children = new LinkedList<>();

          public TreeNode(String rootValue) {
            value = rootValue;
          }

          public boolean addChildTo(String childName, String parentName) {
              if (parentName.equals(value)) {
                  for (TreeNode child:children) {
                      if (child.getValue().equals(childName)) {
                          throw new IllegalArgumentException(parentName + " already has a child named " + childName);
                      }
                  }
                  children.add(new TreeNode(childName));
                  return true;
              } else {
                  for (TreeNode child:children) {
                      if (child.addChildTo(childName, parentName)) {
                          return true;
                      }
                  }
              }
              return false;
          }

        public String getValue() {
            return value;
        }

        public void print() {
            int maxHeight=maxHeigth();
            System.out.printf("|%-20.20s",value);

            for (int i=0;i<maxHeight-1;i++) {
                System.out.printf("|%-20.20s","");
            }
            System.out.println("|");
            for (TreeNode child:children) {
                child.print(1,maxHeight);
            }
        }

        public void print(int level, int maxHeight) {
            for (int i=0;i<level;i++) {
                System.out.printf("|%-20.20s","");
            }
            System.out.printf("|%-20.20s",value);
            for (int i=level;i<maxHeight-1;i++) {
                System.out.printf("|%-20.20s","");
            }
            System.out.println("|");
            for (TreeNode child:children) {
                child.print(level+1,maxHeight);
            }
        }

        public int maxHeigth() {
            int localMaxHeight=0;
            for (TreeNode child:children) {
                int childHeight = child.maxHeigth();
                if (localMaxHeight < childHeight) {
                    localMaxHeight = childHeight;
                }
            }
            return localMaxHeight+1;
        }
    }   

}

Answer 2

您没有注意到您使用的是什么关系数据库管理系统，但是如果它恰好是MS Server或某些版本的Oracle(请参阅下面的编辑)，并且仅仅作为我们中使用this并对此问题感兴趣的人的一个服务，您可以使用递归CTE在Server中完成这一任务。

编辑: Oracle (我很少有经验)似乎也支持递归，包括自11g发行版2. 有关更多信息，请参见此问题。以来的递归CTE。

在这种情况下：

CREATE TABLE hierarchy
    ([Dept] varchar(13), [superiorDept] varchar(12))
;

INSERT INTO hierarchy
    ([Dept], [superiorDept])
VALUES
    ('00',NULL),
    ('00-01', '00'),
    ('00-02', '00'),
    ('00-01-01', '00-01'),
    ('00-01-02', '00-01'),
    ('00-01-03', '00-01'),
    ('00-02-01', '00-02'),
    ('00-02-03', '00-02'),
    ('00-02-03-01', '00-02-03')
;

这个递归CTE：

WITH cte AS (
SELECT 
  Dept,
  superiorDept,
  0 AS depth,
  CAST(Dept AS NVARCHAR(MAX)) AS sort
FROM hierarchy h
WHERE superiorDept IS NULL

UNION ALL

SELECT 
  h2.Dept,
  h2.superiorDept,
  cte.depth + 1 AS depth,
  CAST(cte.sort + h2.Dept AS NVARCHAR(MAX)) AS sort
FROM hierarchy h2
INNER JOIN cte ON h2.superiorDept = cte.Dept
)

SELECT 
  CASE WHEN depth = 0 THEN Dept END AS TopTier,
  CASE WHEN depth = 1 THEN Dept END AS Tier2,
  CASE WHEN depth = 2 THEN Dept END AS Tier3,
  CASE WHEN depth = 3 THEN Dept END AS Tier4
FROM cte
ORDER BY sort

将呈现所需的输出：

 TopTier    Tier2   Tier3     Tier4
 00 
            00-01   
                    00-01-01
                    00-01-02
                    00-01-03
            00-02
                    00-02-01
                    00-02-03
                              00-02-03-01

下面是一个要演示的SQLFiddle

无论邻接列表中涉及的两列的内容或类型如何，它都应该工作。只要父值与id值匹配，CTE生成深度和排序信息就没有问题。

在SQL中完全这样做的唯一警告是，您必须手动定义一组列，但在处理SQL时几乎总是如此。您可能希望生成CTE，然后将已排序的结果传递给您的程序，并使用深度信息使列操作变得微不足道。

Answer 3

检查计数-字符的值。最大-字符计数+1是表列计数。值大小是行计数。

您可以为表定义一个矩阵，例如String[][]。

对于每个值，可以找到列(在字符串值中计算-字符)。现在，要查找行，您应该检查上一列以查找父字符串。

如果找到了，只需在找到的父行之后插入一个新行，向下移动所有rest行。

如果找不到父字符串，请先插入父字符串(基于childParentMap)。

如果父级为null，只需在末尾插入一个新行即可。