SQL Server 树型结构查询指定节点 SQL Server 树型结构查询的排序

 

在上一次的树型节点处理里面，有人提出这样的需求。
要求有这样的一个结果：

传入一个节点的 id/名称， 得到节点所属的 一级节点 与 二级节点。 
至于是否有中间的节点，则不关心。 也不需要显示出来。

本来想通过向上递归的方式，从指定的节点，向上递归，直至递归到根节点
WITH StepCTE
AS
(
SELECT
  test_id,
  pid,
  test_val,
  1 as Lev
FROM
  test_tree
WHERE
  test_val = 'Function'
UNION ALL
SELECT
  T.test_id,
  T.pid,
  T.test_val,
  CTE.Lev + 1
FROM
  test_tree T INNER JOIN StepCTE CTE
  ON T.test_id = CTE.pid 
)
SELECT
  test_id, pid, test_val, Lev
FROM 
  StepCTE
ORDER BY
  Lev DESC

test_id     pid         test_val   Lev
----------- ----------- ---------- -----------
         10        NULL Database             4
         13          10 Oracle               3
         15          13 PL/SQL               2
         16          15 Function             1

这样， 再通过 Top 2 ， 就可以直接取得 Function 这个节点所属的 顶级节点与二级节点。

 

但是对方希望得到的是
ID   名字       顶级节点    二级节点
15   Function   Database    Oracle
这样的结果。

而且希望是，不指定条件的话，全部显示出来，指定了条件，单独显示指定的。
没办法，只能从根节点向下递归了。

取得数型结构中，所有的 当前ID, 当前名字，顶级节点ID，二级节点ID.

WITH StepCTE
AS
(
SELECT
  test_id,
  pid,
  test_val,
  1 as Lev,
  test_id AS Lev1_ID,
  test_val AS Lev1_Name,
  NULL AS Lev2_ID,
  Convert( varchar(10), NULL) AS Lev2_Name
FROM
  test_tree
WHERE
  pid IS NULL
UNION ALL
SELECT
  T.test_id,
  T.pid,
  T.test_val,
  CTE.Lev + 1,
  CTE.Lev1_ID,
  CTE.Lev1_Name,
  case 
    WHEN CTE.Lev = 1 THEN T.test_id
    ELSE CTE.Lev2_ID
  end as [Lev2_ID],
  Convert( varchar(10),
    case 
      WHEN CTE.Lev = 1 THEN T.test_val
      ELSE CTE.Lev2_Name
    end
  ) as Lev2_Name
FROM
  test_tree T INNER JOIN StepCTE CTE
  ON T.pid = CTE.test_id
)
SELECT
  test_id, test_val, Lev1_ID, Lev1_Name, Lev2_ID, Lev2_Name
FROM 
  StepCTE

test_id     test_val   Lev1_ID     Lev1_Name  Lev2_ID     Lev2_Name
----------- ---------- ----------- ---------- ----------- ----------
          1 .NET                 1 .NET              NULL NULL
          6 J2EE                 6 J2EE              NULL NULL
         10 Database            10 Database          NULL NULL
         11 DB2                 10 Database            11 DB2
         12 MySQL               10 Database            12 MySQL
         13 Oracle              10 Database            13 Oracle
         14 SQL Server          10 Database            14 SQL Server
         20 T-SQL               10 Database            14 SQL Server
         15 PL/SQL              10 Database            13 Oracle
         16 Function            10 Database            13 Oracle
         17 Procedure           10 Database            13 Oracle
         18 Package             10 Database            13 Oracle
         19 Cursor              10 Database            13 Oracle
          7 EJB                  6 J2EE                 7 EJB
          8 Servlet              6 J2EE                 8 Servlet
          9 JSP                  6 J2EE                 9 JSP
          2 C#                   1 .NET                 2 C#
          3 J#                   1 .NET                 3 J#
          4 ASP.NET              1 .NET                 4 ASP.NET
          5 VB.NET               1 .NET                 5 VB.NET

(20 行受影响)