sqlserver函数递归,sql中的递归

sql语句中怎么实现递归查询

在SQL

SERVER

2000

中你可以先一些自定义函数，或一些存储过程，实现递归：

select

level,TypeName

from

ProductType

t

START

WITH

t.ParentID=0

CONNECT

BY

PRIOR

t.ProductTypeID=

t.ParentID;

相关问答

Q1: sqlserver 递归查询

CREATE TABLE #tb1(stuId INT,stuName VARCHAR(30),teaId INT);

INSERT INTO #tb1 (stuId,stuName,teaId)

VALUES(1,'zhou',0),(2,'kong',0),(3,'hong',2),(4,'zhang',1),(5,'liu',4),

(6,'zhao',5),(7,'zheng',6),(8,'wei',7)

;WITH cte AS (

SELECT t.stuId,t.stuName,t.teaId FROM #tb1 AS t

WHERE t.stuId=8

UNION ALL

SELECT t.stuId,t.stuName,t.teaId FROM cte AS c

JOIN #tb1 AS t ON c.teaId=t.stuId

)

SELECT * FROM cte

Q2: sqlserver 向上递归 向下递归我知道了，但是如何向上递归难

写在SQL里吧,可以避免频繁的创建连接,那是开销很大的.

不过可能写在sql里比较烦吧.

Q3: SQL Server 两个表递归

with district as ( select * from SECFUNMODULE where MENUID='030' union all select a.* from SECFUNMODULE a, district b where a.PMENUID = b.MENUID ), district1 as ( select a.* from district a where a.MENUID in (select PMENUID from district) ) select * from district1 order by MENUID with当然可以实现. 请参照sqlserver2005 的递归查询

Q4: sqlserver函数中引用递归

set @p_b = (select fid from w_Storage_Depository where fid in(select fid from dbo.jt_base_company where bj=1))

改成

select  @p_b =fid from w_Storage_Depository where fid in(select fid from dbo.jt_base_company where bj=1)

Q5: 怎么用sqlserver实现递归删除目录结构

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel FROM dbo.MyEmployees WHERE ManagerID IS NULL UNION ALL --主要是在此，增加一个union all SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 FROM dbo.MyEmployees AS e INNER JOIN DirectReports AS d -- 在此调用自身，就可以实现递归了 ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, Title, EmployeeLevel FROM DirectReports ORDER BY ManagerID;