前些日子要查詢查詢部門所有子部門
上網搜索了一下,方法有
(1)、sql 2005的數據庫歐!!2000不支持的
with SUBdeptName(DEPT_ID,DEPT_NAME)
as
(
SELECT
DEPT_ID,
DEPT_NAME
FROM dbo.DEPARTMENTInfo
WHERE DEPT_ID=1
)
UNION ALL
SELECT
DEPT_ID
,DEPT_NAME
FROM dbo.DEPARTMENTInfo
INNER JOIN SUBdeptName
ON DEPARTMENTInfo.SUPERIOR_DEPT=SUBdeptName.DEPT_ID
)
(2)、測試數據--可直接運行
SELECT * INTO t --載入測試數據
FROM
(
SELECT 1 AS ID,'food' AS Product,0 AS ParentID UNION ALL
SELECT 2,'sport',0 UNION ALL
SELECT 3,'drink',1 UNION ALL
SELECT 4,'ball',2 UNION ALL
SELECT 5,'fruit',1 UNION ALL
SELECT 6,'apple',5 UNION ALL
SELECT 7,'banana',5 UNION ALL
SELECT 8,'football',4 UNION ALL
SELECT 9,'basketball',4 UNION ALL
SELECT 10,'peisi',3 UNION ALL
SELECT 11,'wohaha',3
) AS a
SELECT * FROM T
GO
WITH ShowTree(ID,Product,ORDERBY) AS
(
SELECT ID,Product,ID FROM t WHERE ParentID=0 --固定成員
UNION ALL
SELECT t.ID,t.Product,ORDERBY*100+t.ID FROM ShowTree AS s
INNER JOIN t ON s.ID = t.ParentID
)
SELECT * FROM ShowTree ORDER BY CAST(ORDERBY AS VARCHAR(50))--OPTION(MAXRECURSION 1)
DROP TABLE t