sqlserver 遞歸查詢

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

在甘谷等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場(chǎng)前瞻性、產(chǎn)品創(chuàng)新能力，以專(zhuān)注、極致的服務(wù)理念，為客戶提供成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè) 網(wǎng)站設(shè)計(jì)制作按需定制,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),高端網(wǎng)站設(shè)計(jì),網(wǎng)絡(luò)營(yíng)銷(xiāo)推廣,外貿(mào)網(wǎng)站制作,甘谷網(wǎng)站建設(shè)費(fèi)用合理。

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

sqlserver遞歸算法

With T

As

(

Select * From TB Where newNo=103

Union All

Select TB.* From TB Inner Join T on TB.newNo=T.oldNo

)

Select * From T

怎么用sqlserver實(shí)現(xiàn)遞歸刪除目錄結(jié)構(gòu)

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS ( SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel FROM dbo.MyEmployees WHERE ManagerID IS NULL UNION ALL --主要是在此，增加一個(gè)union all SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1 FROM dbo.MyEmployees AS e INNER JOIN DirectReports AS d -- 在此調(diào)用自身，就可以實(shí)現(xiàn)遞歸了 ON e.ManagerID = d.EmployeeID ) SELECT ManagerID, EmployeeID, Title, EmployeeLevel FROM DirectReports ORDER BY ManagerID;

sqlserver函數(shù)中引用遞歸

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)