SqlServer递归查询

 1 DECLARE @ParentId NVARCHAR(36);
 2 SET @ParentId = '078499bf-dedd-b293-4653-7bf6a2e54cbb';
 3 
 4 WITH [Temp] AS
 5 (
 6     SELECT [A].* FROM [dbo].[Contacts] A WHERE [A].[ParentID] = @ParentId AND [A].[IsDefault] = 1
 7     UNION ALL
 8     SELECT [A].* FROM [dbo].[Contacts] A INNER JOIN [Temp] B ON A.[ParentID] = B.[ObjectID] AND [A].[IsDefault] = 1
 9 )
10 SELECT * FROM [Temp] ORDER BY [Temp].[GlobalSort] ASC;

搜寻良久,终于达成,记个笔记:

–SqlServer 2005 CTE

with cte (deptid,dptname,parentid) as (

–初步条件
select a.deptid,a.dptname,a.parentid from base_dept a where a.deptid =
2
union all

–CTE每一趟递归条件
select a.deptid,a.dptname,a.parentid from base_dept a, cte where
a.parentid = cte.deptid
)

–DTE必须紧跟着SQL语句使用。
select deptid,dptname,parentid from cte order by dptname;

 

附送二个利用CTE的事例:

–CTE
with cte (deptid,dptname,parentid) as (
select a.deptid,a.dptname,a.parentid from base_dept a where a.deptid =
2
union all
select a.deptid,a.dptname,a.parentid from base_dept a, cte where
a.parentid = cte.deptid
)
select a.cdeptid,a.cdeptparentid,count(a.cid) as ccount
from base_excute_compel a,cte
where a.CompelTime >= ‘2010-01-01’
and a.CompelTime <= ‘2013-01-01’
and a.cdeptid in (cte.deptid)
group by a.cdeptid,a.cdeptparentid

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图