sqlserver使用公用表表达式CTE实现递归查询

公用表表达式 (CTE) 具有一个重要的优点,那就是能够引用其自身,从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

在 SQL Server 2005 中,当某个查询引用递归 CTE 时,它即被称为“递归查询”。递归查询通常用于返回分层数据,例如:显示某个组织图中的雇员或物料清单方案(其中父级产品有一个或多个组件,而那些组件可能还有子组件,或者是其他父级产品的组件)中的数据。

递归 CTE 可以极大地简化在 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 语句中运行递归查询所需的代码。在 SQL Server 的早期版本中,递归查询通常需要使用临时表、游标和逻辑来控制递归步骤流。有关公用表表达式的详细信息,请参阅使用公用表表达式

微软官方文档见:http://msdn.microsoft.com/zh-cn/library/ms186243(v=sql.90).aspx

利用CTE递归查询部门信息实例:

WITH DeptINFO AS(

SELECT id,parentid,deptName FROM dept_table WHERE id = 10008

UNION ALL

–查询下级部门信息
SELECT a.id,a.parentid,a.deptName FROM dept_table AS a,DeptINFO AS b WHERE a.parentid = b.id
–查询上级部门信息
–SELECT a.id,a.parentid,a.deptName FROM dept_table AS a,DeptINFO AS b WHERE a.id = b.parentid

)

SELECT * FROM DeptINFO

 

可以简单的封装为表函数,方便调用:

CREATE FUNCTION F_GET_CHILD_DEPT ( @root_dept_id VARCHAR(200) )
RETURNS @dept_table TABLE ( dept_id VARCHAR(200),dept_pid VARCHAR(200),dept_name VARCHAR(200) )
AS
    BEGIN

        WITH DEPT_INFO AS(

        SELECT TSD_ID,TSD_PARENT_ID,TSD_NAME FROM T_DEPARTMENT WHERE TSD_ID = @root_dept_id

        UNION ALL

        SELECT a.TSD_ID,a.TSD_PARENT_ID,a.TSD_NAME FROM T_DEPARTMENT AS a,DEPT_INFO AS b WHERE a.TSD_PARENT_ID = b.TSD_ID
        –SELECT a.TSD_ID,a.TSD_PARENT_ID,a.TSD_NAME FROM T_DEPARTMENT AS a,DEPT_INFO AS b WHERE a.TSD_ID = b.TSD_PARENT_ID

        )

        INSERT INTO @dept_table SELECT * FROM DEPT_INFO

        RETURN

    END

查询时将需要查询的部门ID作为函数的参数即可:

SELECT * FROM F_GET_CHILD_DEPT(‘B0AA5C53-67CD-478B-9284-E3A01E4A012F’)

SELECT * FROM F_GET_PARENT_DEPT(‘B0AA5C53-67CD-478B-9284-E3A01E4A012F’)

 

image

截图为取上级部门信息示例。

About 智足者富

http://chenpeng.info

发表评论

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

您可以使用这些HTML标签和属性:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>