;with f as ( select * from tb where id=4 union all select a.* from tb a join f on a.id=b.pid )select * from f
select * from tb where pid=(select pid from tb tt where tt.id=3) 这样?
USE tempdb GO-- 建立演示环境 CREATE TABLE Dept( id int PRIMARY KEY, parent_id int, name nvarchar(20)) INSERT Dept SELECT 0, 0, N'<全部>' UNION ALL SELECT 1, 0, N'财务部' UNION ALL SELECT 2, 0, N'行政部' UNION ALL SELECT 3, 0, N'业务部' UNION ALL SELECT 4, 0, N'业务部' UNION ALL SELECT 5, 4, N'销售部' UNION ALL SELECT 6, 4, N'MIS' UNION ALL SELECT 7, 6, N'UI' UNION ALL SELECT 8, 6, N'软件开发' UNION ALL SELECT 9, 8, N'内部开发' GO DECLARE @id int SET @id = 6 ;WITH DEPTS AS( -- 定位点成员 SELECT * FROM Dept WHERE parent_id = @id UNION ALL -- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归 SELECT A.* FROM Dept A, DEPTS B WHERE A.id = B.parent_id and A.id>a.parent_id ) SELECT distinct * FROM DEPTS GO--一个例子 子节点的所有父节点
id name pid
1 文档 0
2 视频 0
3 培训 2
4 咨询 2
5 企业文化 3
(
select * from tb where id=4
union all
select a.* from tb a join f on a.id=b.pid
)select * from f
这样?
USE tempdb
GO-- 建立演示环境
CREATE TABLE Dept(
id int PRIMARY KEY,
parent_id int,
name nvarchar(20))
INSERT Dept
SELECT 0, 0, N'<全部>' UNION ALL
SELECT 1, 0, N'财务部' UNION ALL
SELECT 2, 0, N'行政部' UNION ALL
SELECT 3, 0, N'业务部' UNION ALL
SELECT 4, 0, N'业务部' UNION ALL
SELECT 5, 4, N'销售部' UNION ALL
SELECT 6, 4, N'MIS' UNION ALL
SELECT 7, 6, N'UI' UNION ALL
SELECT 8, 6, N'软件开发' UNION ALL
SELECT 9, 8, N'内部开发'
GO
DECLARE @id int
SET @id = 6
;WITH
DEPTS AS(
-- 定位点成员
SELECT * FROM Dept
WHERE parent_id = @id
UNION ALL
-- 递归成员, 通过引用CTE自身与Dept基表JOIN实现递归
SELECT A.*
FROM Dept A, DEPTS B
WHERE A.id = B.parent_id
and A.id>a.parent_id
)
SELECT distinct * FROM DEPTS
GO--一个例子 子节点的所有父节点