MSSQL之树形结构查询语句

May 23 2019

我们在设计父子级关系的表结构时，往往使用单表就足够了。但是如果在没有多余字段记录结构的情况下，仅仅通过ID、ParentID以及Name基本字段来展示树形结构的话，该怎么写树形结构的查询语句呢？

假设现在有这么一张表GP

ID ParentID Name
1 NULL 阿里巴巴
2 1 蚂蚁金服
3 1 淘宝网
4 2 支付宝

它的树形关系应该如下

1
2
3
4
├ 阿里巴巴
	 ├ 蚂蚁金服
	 	  ├ 支付宝
	 ├ 淘宝网

那么树形结构的排序语句应该这么写

1
2
3
4
5
6
7
8
9
10
11
12
13
;WITH T AS
(
    SELECT *,CAST(ID AS VARBINARY(MAX)) AS px, 0 as Level
    	FROM GP AS A
    	WHERE NOT EXISTS(SELECT * FROM GP WHERE ID=A.ParentID)
	UNION ALL 
    SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX)), B.Level+1
    	FROM GP AS A
    	INNER JOIN T AS B
    	ON A.ParentID=B.ID
)
SELECT ID,ParentID,Name,Level FROM T 
ORDER BY px

其中px字段是树形结构排序的关键，而Level字段则表示树结构的深度。

解释一下上面这段语句，WITH AS与()内包含UNION ALL的查询语法可以构成复杂而且功能强大的递归查询。查询的过程可以分为：

  1. 将UNION ALL之前的语句第一次运行的结果作为递归原点，存入T中
  2. 运行UNION ALL之后的递归语句得到结果集T0，将其存入T中
  3. 以此类推，直到返回结果集为空为止
  4. 最后将每次返回的结果集T0至Tn全部UNION ALL起来，得到最后的结果存入T中

而这么做的目的，就是为了得到px的值，我们可以看一下px列的结果

ID ParentID Name px Level
1 NULL 阿里巴巴 0x00000001 0
2 1 蚂蚁金服 0x0000000100000002 1
3 1 淘宝网 0x0000000100000003 1
4 2 支付宝 0x000000010000000200000004 2

最后按照px列的顺序排序就可以得到正确的树形结构查询顺序了。

再说一个比较常用的查询，查询所有父节点的树形结构查询语句。

有了上面的例子，我们可以稍微改动一下

1
2
3
4
5
6
7
8
9
10
11
;WITH T AS
(
    SELECT * from GP where ID=#ID# --需要查找的子节点
    UNION ALL 
    SELECT A.* 
		FROM T
    	INNER JOIN GP AS A
		ON T.ParentID= A.ID
)
SELECT * FROM T
ORDER BY ID;

同样是用了WITH AS以及UNION ALL递归查询方法，不同的是这次使用的是目标子节点最为递归原点。

avatar

chilihotpot

You Are The JavaScript In My HTML