数据库递归查询（CET）

IF OBJECT_ID('[ta]') IS NOT NULL

DROP TABLE [ta]

Go

CREATE TABLE ta([id] INT,[name] NVARCHAR(4),[parentid] NVARCHAR(2))

Go

INSERT INTO ta

SELECT 1,'河北省','0' UNION ALL

SELECT 2,'邢台市','1' UNION ALL

SELECT 3,'石家庄市','1' UNION ALL

SELECT 4,'张家口市','1' UNION ALL

SELECT 5,'南宫','2' UNION ALL

SELECT 6,'坝上','4' UNION ALL

SELECT 7,'任县','2' UNION ALL

SELECT 8,'清河','2' UNION ALL

SELECT 9,'河南省','0' UNION ALL

SELECT 10,'新乡市','9' UNION ALL

SELECT 11,'aaa','10' UNION ALL

SELECT 12,'bbb','10'

GO

--Start

;with district

as

( select * from ta where [name]=N'河北省'

union all

select a.* from ta a,district b where a.parentid=b.id

)

select * from district

--Result:

/*

id name parentid

----------- ---- --------

1 河北省 0

2 邢台市 1

3 石家庄市 1

4 张家口市 1

6 坝上 4

5 南宫 2

7 任县 2

8 清河 2

*/

上面代码可以还不够清晰给多一个例子

WITH district
--查询列
(PID, FDepartmentName,FState, FFatherName) AS

--父表
(SELECT PID, FDepartmentName,FState,CONVERT(nvarchar(50), '') AS FFatherName FROM dbo.T_Department WHERE (FParentUserID IS NULL or FParentUserID=0)
UNION ALL
--子表(注意是b的FDepartmentName)
SELECT a.PID, a.FDepartmentName,a.FState,b.FDepartmentName AS FFatherName FROM dbo.T_Department AS a

--关系
INNER JOIN district AS b ON a.FParentUserID = b.PID)

--查询
SELECT PID, FDepartmentName,FState, FFatherName
FROM district AS c

巴特西

数据库递归查询（CET）

最新文章

热门文章