SQLServer 2005提供了递归查询(Recursive Query)功能,可以用来查询树形数据。常用的场景是查询组织机构、产品分类、地区等具有层级关系的数据。
递归查询的语法如下:
WITH CTE AS (
-- Anchor member
SELECT ...
UNION ALL
-- Recursive member
SELECT ...
FROM CTE
WHERE ...
)
SELECT ...
FROM CTE
其中,AN(X)CHOR MEMBER是递归的起点,相当于树的根节点,不能递归,但是必须包含在CTE中;RECURSIVE MEMBER是递归的迭代过程,相当于树的子节点,可以递归,必须引用CTE本身,直到满足递归停止的条件。
下面的示例演示如何使用递归查询来查询组织机构树,假设有以下表结构:
CREATE TABLE organizations (
id INT PRIMARY KEY,
name VARCHAR(50),
parent_id INT
);
其中,id是组织机构的唯一标识符,name是组织机构的名称,parent_id是组织机构的上级节点的id。
首先,将根节点的id为0的组织机构(假设名称为根节点)插入到表中:
INSERT INTO organizations (id, name, parent_id)
VALUES (0, '根节点', NULL);
然后,插入一些子节点数据:
INSERT INTO organizations (id, name, parent_id)
VALUES (1, '子节点1', 0);
INSERT INTO organizations (id, name, parent_id)
VALUES (2, '子节点2', 0);
INSERT INTO organizations (id, name, parent_id)
VALUES (3, '子节点3', 1);
INSERT INTO organizations (id, name, parent_id)
VALUES (4, '子节点4', 1);
INSERT INTO organizations (id, name, parent_id)
VALUES (5, '子节点5', 2);
INSERT INTO organizations (id, name, parent_id)
VALUES (6, '子节点6', 2);
接下来,使用递归查询来查询组织机构树:
WITH CTE AS (
SELECT id, name, parent_id
FROM organizations
WHERE id = 0
UNION ALL
SELECT o.id, o.name, o.parent_id
FROM organizations o
INNER JOIN CTE ON o.parent_id = CTE.id
)
SELECT *
FROM CTE;
上述代码中,WHERE子句中指定根节点的id为0,作为递归查询的起点。UNION ALL将根节点也包含在递归查询中。第一个SELECT是递归的起点,第二个SELECT是递归的迭代过程,从上一层级的节点查询下一层级的子节点。
除了查询整个树,还可以查询指定节点的子树。例如,查询id为1的节点的子树:
WITH CTE AS (
SELECT id, name, parent_id
FROM organizations
WHERE id = 1
UNION ALL
SELECT o.id, o.name, o.parent_id
FROM organizations o
INNER JOIN CTE ON o.parent_id = CTE.id
)
SELECT *
FROM CTE;
上述代码中,WHERE子句指定id为1的节点为起点,查询其子节点。其他部分和查询整个树的代码相同。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:SQLSERVER2005 中树形数据的递归查询 - Python技术站