--cte递归查询 CREATE TABLE Employee_Tree (Employee_NM nvarchar(50), Employee_ID int PRIMARY KEY, ReportsTo int) --insert some data, build a reporting tree INSERT INTO Employee_Tree VALUES('Richard', 1, NULL) INSERT INTO Employee_Tree VALUES('Stephen', 2, 1) INSERT INTO Employee_Tree VALUES('Clemens', 3, 2) INSERT INTO Employee_Tree VALUES('Malek', 4, 2) INSERT INTO Employee_Tree VALUES('Goksin', 5, 4) INSERT INTO Employee_Tree VALUES('Kimberly', 6, 1) INSERT INTO Employee_Tree VALUES('Ramesh', 7, 5) -- with MyCTE as ( select Employee_ID, Employee_NM, -1 as ReportsTo, 0 as SubLevel from Employee_Tree where ReportsTo is null --root node union all select e.Employee_ID, e.Employee_NM, e.ReportsTo, SubLevel +1 from Employee_Tree e, MyCTE where e.ReportsTo = MyCTE.Employee_ID) --select * from MyCTE select MyCTE.Employee_NM as emp , MyCTE.SubLevel, e.Employee_NM as boss from MyCTE left join Employee_Tree e on MyCTE.ReportsTo = e.Employee_ID --OPTION(MAXRECURSION 3) --error --OPTION(MAXRECURSION 4) --ok where SubLevel < 4
----以下示例在示例 C 的基础上添加经理和雇员的名称,以及他们各自的头衔。通过缩进各个级别,突出显示经理和雇员的层次结构。 USE AdventureWorks2008R2; GO WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName), e.JobTitle, e.EmployeeID, 1, CONVERT(varchar(255), c.FirstName + ' ' + c.LastName) FROM HumanResources.Employee AS e JOIN Person.Contact AS c ON e.ContactID = c.ContactID WHERE e.ManagerID IS NULL UNION ALL SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) + c.FirstName + ' ' + c.LastName), e.JobTitle, e.EmployeeID, EmployeeLevel + 1, CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + LastName) FROM HumanResources.Employee as e JOIN Person.Contact AS c ON e.ContactID = c.ContactID JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID ) SELECT EmployeeID, Name, Title, EmployeeLevel FROM DirectReports ORDER BY Sort; GO
--公司,子公司 节点id,父节点parentId declare @id int set @id = 1 ;with cte as ( select id,parentId from SM_Company where id = @id union all select e.id,e.parentId from SM_Company as e join t as f on e.parentId = f.id )select * from cte
--公司,子公司 节点id,父节点parentId declare @id int set @id = 1 ;with cte as ( select id,parentId from SM_Company where id = @id union all select e.id,e.parentId from SM_Company as e join cte as f on e.parentId = f.id --上边这里打错! )select * from cte
cte可以实现那种功能,但在数据库中一般是集合操作。如果你真要实现先序遍历 可以用代码写循环之类。 下面写一个实现功能的 读取一棵树with cte as ( select * from tb where pid=0 -- 假设0是根节点 union all select a.* from tb a join cte b on a.pid=b.id -- tb中父节点在cte中的节点 ) select * from cte;
with cte as ( select id,pid from tb where id=@id union all select a.id,a.pid from tb a join cte b on a.pid= b.id ) select * from cte
不难写,就是这个套路 with cte union all前是首行结果集,union all后是根据前面的递归,你可以把它理解为select id,pid from tb where id=@id union all select a.id,a.pid from tb a join cte b on a.pid= b.id union allselect a.id,a.pid from tb a join cte b on a.pid= b.id union all select a.id,a.pid from tb a join cte b on a.pid= b.id ...一直这样递归下去
看看这个时候有帮助
http://blog.csdn.net/xys_777/archive/2010/06/15/5672481.aspx
CREATE TABLE Employee_Tree (Employee_NM nvarchar(50), Employee_ID int PRIMARY KEY, ReportsTo int)
--insert some data, build a reporting tree
INSERT INTO Employee_Tree VALUES('Richard', 1, NULL)
INSERT INTO Employee_Tree VALUES('Stephen', 2, 1)
INSERT INTO Employee_Tree VALUES('Clemens', 3, 2)
INSERT INTO Employee_Tree VALUES('Malek', 4, 2)
INSERT INTO Employee_Tree VALUES('Goksin', 5, 4)
INSERT INTO Employee_Tree VALUES('Kimberly', 6, 1)
INSERT INTO Employee_Tree VALUES('Ramesh', 7, 5)
--
with MyCTE
as
( select Employee_ID, Employee_NM, -1 as ReportsTo, 0 as SubLevel
from Employee_Tree where ReportsTo is null --root node
union all
select e.Employee_ID, e.Employee_NM, e.ReportsTo, SubLevel +1
from Employee_Tree e, MyCTE where e.ReportsTo = MyCTE.Employee_ID) --select * from MyCTE
select MyCTE.Employee_NM as emp , MyCTE.SubLevel, e.Employee_NM as boss
from MyCTE left join Employee_Tree e on MyCTE.ReportsTo = e.Employee_ID
--OPTION(MAXRECURSION 3) --error
--OPTION(MAXRECURSION 4) --ok
where SubLevel < 4
USE AdventureWorks2008R2;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
e.JobTitle,
e.EmployeeID,
1,
CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
FROM HumanResources.Employee AS e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
WHERE e.ManagerID IS NULL
UNION ALL
SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
c.FirstName + ' ' + c.LastName),
e.JobTitle,
e.EmployeeID,
EmployeeLevel + 1,
CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +
LastName)
FROM HumanResources.Employee as e
JOIN Person.Contact AS c ON e.ContactID = c.ContactID
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports
ORDER BY Sort;
GO
--公司,子公司 节点id,父节点parentId
declare @id int
set @id = 1
;with cte as
(
select id,parentId
from SM_Company
where id = @id
union all
select e.id,e.parentId
from SM_Company as e join t as f on e.parentId = f.id
)select *
from cte
--公司,子公司 节点id,父节点parentId
declare @id int
set @id = 1
;with cte as
(
select id,parentId
from SM_Company
where id = @id
union all
select e.id,e.parentId
from SM_Company as e join cte as f on e.parentId = f.id --上边这里打错!
)select *
from cte
cte可以实现那种功能,但在数据库中一般是集合操作。如果你真要实现先序遍历 可以用代码写循环之类。
下面写一个实现功能的 读取一棵树with cte as
(
select * from tb where pid=0 -- 假设0是根节点
union all
select a.* from tb a join cte b on a.pid=b.id -- tb中父节点在cte中的节点
)
select * from cte;
(
select id,pid from tb where id=@id
union all
select a.id,a.pid
from tb a join cte b on a.pid= b.id
)
select * from cte
所以我想能不能写一条比较简单的SQL,直接解决这个问题。
union all
select a.id,a.pid
from tb a join cte b on a.pid= b.id
union allselect a.id,a.pid
from tb a join cte b on a.pid= b.id
union all
select a.id,a.pid
from tb a join cte b on a.pid= b.id
...一直这样递归下去
CTE虽然出来5年了但是感觉彻底讨论这玩意的文章比较少哈
查询的时候可以使用MAXRECURSION选项来限制递归的级数,未指定该选项时,SQL默认的递归级数是100,也就是说,如果是无限递归,在100级时就会中断并报错。语句如:
SELECT * FROM cte OPTION (MAXRECURSION 100);
up up MAXRECURSION 100
CTE