--建测试表IF OBJECT_ID('wx_test_dep') IS NOT NULLDROP TABLE wx_test_depGOCREATE TABLE wx_test_dep(depId INT PRIMARY KEY,pDepId INT,depName NVARCHAR(20))GO--插入测试数据INSERT INTO wx_test_dep VALUES (1,0,'武汉大学')INSERT INTO wx_test_dep

--建测试表
IF OBJECT_ID('wx_test_dep') IS NOT NULL
	DROP TABLE wx_test_dep
GO
CREATE TABLE wx_test_dep(
	depId INT PRIMARY KEY,
	pDepId INT,
	depName NVARCHAR(20)
)
GO
--插入测试数据
INSERT INTO wx_test_dep VALUES (1,0,'武汉大学')
INSERT INTO wx_test_dep VALUES (2,0,'华科大')
INSERT INTO wx_test_dep VALUES (3,1,'武大后勤部')
INSERT INTO wx_test_dep VALUES (4,3,'武大后勤部技术科')
INSERT INTO wx_test_dep VALUES (5,3,'武大后勤部供应科')
INSERT INTO wx_test_dep VALUES (6,2,'华科大学生部')
INSERT INTO wx_test_dep VALUES (7,6,'华科大学生部技术科')
INSERT INTO wx_test_dep VALUES (8,6,'华科大学生部教务科')
-- 武汉大学
-- --武大后勤部
-- ----武大后勤部技术科
-- ----武大后勤部供应科
-- 华科大
-- --华科大学生部
-- ----华科大学生部技术科
-- ----华科大学生部教务科
--SELECT * FROM wx_test_dep wtd

--查出华科大所有子部门
/*
2	0	华科大
6	2	华科大学生部
7	6	华科大学生部技术科
8	6	华科大学生部教务科 
* */

DECLARE @t TABLE (
	depId INT,
	pDepId INT,
	depName NVARCHAR(20)
)

INSERT INTO @t
SELECT * FROM wx_test_dep wtd WHERE wtd.depId=2

WHILE EXISTS(
	SELECT 1 FROM wx_test_dep w WHERE EXISTS(
	SELECT 1 FROM @t t WHERE w.pDepId=t.depId-- AND w.depId!=t.depId
	)
	AND NOT EXISTS (
	SELECT 1 FROM @t t WHERE w.depId=t.depId
	)
)
BEGIN
	INSERT INTO @t
	SELECT
	wtd.depId,
	wtd.pDepId,
	wtd.depName
	FROM
	wx_test_dep wtd WHERE EXISTS(
	SELECT 1 FROM @t t WHERE wtd.pDepId=t.depId-- AND wtd.depId!=t.depId 	
	)
	AND NOT EXISTS (
	SELECT 1 FROM @t t WHERE wtd.depId=t.depId
	)
END

SELECT * FROM @t