sql - Coldfusion & SQL 创建递归树

Question

更新

我有一个非常独特的案例，我从我的 SQL 数据库中得到了这个。

+------+-------+-------+-------+-------+
| LVL  | LVL_1 | LVL_2 | LVL_3 | LVL_4 |
+------+-------+-------+-------+-------+
| PHIL | NULL  | NULL  | NULL  | NULL  |
| PHIL | BOB   | NULL  | NULL  | NULL  |
| PHIL | BOB   | BILL  | NULL  | NULL  |
| PHIL | BOB   | BILL  | JEN   | NULL  |
| PHIL | BOB   | BILL  | JEN   | JOE   |
+------+-------+-------+-------+-------+

最后一个包含名字的 LVL 列代表这个人。

比如这个代表PHIL

| PHIL | NULL  | NULL  | NULL  | NULL  |

这代表珍

| PHIL | BOB   | BILL  | JEN   | NULL  |

这代表 JOE(因为他是最后一级)

| PHIL | BOB   | BILL  | JEN   | JOE  |

我的最终目标是在查询“PHIL”时将此数据从 ColdFusion 返回到 JSON 树结构中:

{
  name: 'PHIL',
  parent: NULL,
  level: 0,
  groups: [
    {
      name: 'BOB',
      parent: 'PHIL',
      level: 1,
      groups: [
        {
          name: 'BILL',
          parent: 'BOB',
          level: 2,
          groups: [
            {
              name: 'JEN',
              parent: 'BILL',
              level: 3,
              groups: [
                {
                  name: 'JOE',
                  parent: 'JEN',
                  level: 4,
                  groups: []
                }
              ]
            }
          ]
        }
      ]
    }
  ]
}

如果我查询'BILL'，我只能看到他下面的树数据，如下所示:

    {
      name: 'BILL',
      parent: 'BOB',
      level: 2,
      groups: [
        {
          name: 'JEN',
          parent: 'BILL',
          level: 3,
          groups: [
            {
              name: 'JOE',
              parent: 'JEN',
              level: 4,
              groups: []
            }
          ]
        }
      ]
    }

我想编写一些能够生成此数据的树结构的 SQL 命令。如果不可能，我想至少将原始数据重新格式化(使用 SQL 命令)为:

+------+--------+
| NAME | PARENT |
+------+--------+
| PHIL | NULL   |
| BOB  | PHIL   |
| BILL | BOB    |
| JEN  | BILL   |
| JOE  | JEN    |
+------+--------+

所以我或许可以按照本教程使用 ColdFusion 将其重组为树数据 http://www.bennadel.com/blog/1069-ask-ben-simple-recursion-example.htm

这可能吗？有人可以帮我解决这个问题吗？

Answer 1

<cfscript>
q = queryNew("LTM,LTM_1,LTM_2,LTM_3,LTM_4");

queryAddRow(q);
QuerySetCell(q, "LTM", "OSTAPOWER");
QuerySetCell(q, "LTM_1", "VENKAT");
QuerySetCell(q, "LTM_2", "LYNN");
QuerySetCell(q, "LTM_3", "SMITH");
QuerySetCell(q, "LTM_4", "HARTLEY");

queryAddRow(q);
QuerySetCell(q, "LTM", "OSTAPOWER");
QuerySetCell(q, "LTM_1", "VENKAT");
QuerySetCell(q, "LTM_2", "LYNN");
QuerySetCell(q, "LTM_3", "SMITH");
QuerySetCell(q, "LTM_4", "SHREVE");

function collect(q) {
    var data = {};
    for (var row in q)
    {
        var varName = "data";
        for (var i = 0; i <= 4; i++)
        {
            var col = i == 0 ? "LTM" : "LTM_#i#";
            var name = row[col];
            if (len(name))
                varName = listAppend(varName, name, ".");
            else
                break;
        }
        setVariable(varName, {});
    }

    return data;
}

function transform(tree, nodeName, level=0, parent="") 
{
    if (structIsEmpty(tree))
        return "";

    var node = {
        'name': nodeName,
        'parent': len(parent) ? parent : javacast("null",""),
        'level': javacast("int", level),
        'groups': []
    };

    var branch = tree[nodeName];

    for (var child in branch)
        arrayAppend(node.groups, transform(branch, child, level+1, nodeName));

    return node;
}

c=collect(q);

writeDump(transform(c,'OSTAPOWER'));
</cfscript>

运行它:http://www.trycf.com/scratch-pad/pastebin?id=c8YMvGXG

然后只是 serializeJSON() 从 transform() 返回的结果。