久久免费视频7,四虎tv,日韩精品一区二区三区毛片

很多人可能想要查詢整個樹形表關聯的內容都會通過循環遞歸來查...事實上在微軟在SQL2005或以上版本就能用別的語法進行查詢，下面是示例。

				?

									--通過子節點查詢父節點

									WITH

									TREE AS( 

									SELECT * FROM Areas 

									WHERE id = 6 -- 要查詢的子 id 

									UNION ALL

									SELECT Areas.* FROM Areas, TREE 

									WHERE TREE.PId = Areas.Id

									) 

									SELECT Area FROM TREE

									--通過父節點查詢子節點

									WITH

									TREE AS( 

									SELECT * FROM Areas 

									WHERE id = 7 -- 要查詢的子 id 

									UNION ALL

									SELECT Areas.* FROM Areas, TREE 

									WHERE TREE.Id = Areas.PId

									) 

									SELECT Area FROM TREE

通過子節點查詢父節點查詢結果為：

SQL Server 樹形表非循環遞歸查詢的實例詳解

修改代碼為

				?

									--通過子節點查詢父節點

									declare @area varchar(8000);

									WITH

									TREE AS( 

									SELECT * FROM Areas 

									WHERE id = 6 -- 要查詢的子 id 

									UNION ALL

									SELECT Areas.* FROM Areas, TREE 

									WHERE TREE.PId = Areas.Id

									)

									select @area=isnull(@area,'')+Area from Tree order by id 

									select Area= @area

則結果為：中國北京市豐臺區

根據以上可以將這段代碼封裝為一個存儲過程

				?

									-----存儲過程，遞歸獲取樹形地區表字符串

									if exists (select * from sysobjects where name='SP_GetAreaStr')

									drop proc SP_GetAreaStr

									go

									create procedure SP_GetAreaStr 

									@id int

									as

									declare @area varchar(8000)

									begin

									WITH

									TREE AS( 

									SELECT * FROM Areas 

									WHERE id = @id -- 要查詢的子 id 

									UNION ALL

									SELECT Areas.* FROM Areas, TREE 

									WHERE TREE.PId = Areas.Id

									)

									select @area=isnull(@area,'')+Area from Tree order by id 

									select Area= @area

									end

									go

									--exec sp_helptext 'SP_GetAreaStr'

									--go

									exec SP_GetAreaStr 28

									go