sqlserver 樹形結構表查詢 擷取拼接結果

標籤：sql   select   nvarchar   存在   test   實現   img   server   expr   

 

樹形表結構如下

IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N‘[dbo].[Test]‘) AND type IN (‘U‘))    DROP TABLE [dbo].[Test]GOCREATE TABLE [dbo].[Test] (  [Id] bigint  IDENTITY(1,1) NOT NULL,  [TypeName] nvarchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL,  [ParentId] bigint  NULL)GO-- ------------------------------ Records of Test-- ----------------------------SET IDENTITY_INSERT [dbo].[Test] ONGOINSERT INTO [dbo].[Test] ([Id], [TypeName], [ParentId]) VALUES (N‘1‘, N‘生活常識‘, N‘0‘)GOINSERT INTO [dbo].[Test] ([Id], [TypeName], [ParentId]) VALUES (N‘2‘, N‘生活‘, N‘1‘)GOINSERT INTO [dbo].[Test] ([Id], [TypeName], [ParentId]) VALUES (N‘3‘, N‘常識‘, N‘2‘)GOSET IDENTITY_INSERT [dbo].[Test] OFFGO

表結構及資料

需要實現的結果

 對於這種情況有三種解決方案：遊標、迴圈、CTE

 

一、遊標、迴圈

 

DECLARE @temp nvarchar(MAX)=‘‘,@pid bigint=3; WHILE @pid<>0 BEGIN IF @temp=‘‘     SELECT @temp=TypeName,@pid=ParentId     FROM [dbo].[Test]     WHERE Id=@pid; ELSE     SELECT @temp=(TypeName+‘->‘+@temp),@pid=ParentId     FROM [dbo].[Test]     WHERE Id=@pid; END; SELECT @temp AS TypeName;

 

二、CTE

 

;with Tree as(select * from [Test] where Id=3union allselect a.* from [Test] a join Tree b on a.Id=b.ParentId) select stuff((select ‘->‘ + TypeName from Tree order by Id FOR XML PATH(‘‘)),1,5,‘‘)

 

CTE文法

;[ WITH [ ,n ] ] ::= expression_name [ ( column_name [ ,n ] ) ] AS ( CTE_query_definition )

在使用CTE時應注意如下幾點： 
1、CTE後面必須直接跟使用CTE的SQL語句（如select、insert、update等），否則，CTE將失效。

2、CTE後面也可以跟其他的CTE，但只能使用一個with，多個CTE中間用逗號（,）分隔，如下

;withcte1 as(    select * from table1 where name like ‘abc%‘),cte2 as(    select * from table2 where id > 20),cte3 as(    select * from table3 where price < 100)select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id

3、如果CTE的運算式名稱與某個資料表或視圖重名，則緊跟在該CTE後面的SQL語句使用的仍然是CTE，當然，後面的SQL語句使用的就是資料表或視圖了，如下

--  table1是一個實際存在的表;withtable1 as(    select * from persons where age < 30)select * from table1  --  使用了名為table1的公用表運算式select * from table1  --  使用了名為table1的資料表

4、CTE 可以引用自身，也可以引用在同一 WITH 子句中預先定義的 CTE。不允許前向引用。

5、不能在 CTE_query_definition 中使用以下子句： 
（1）COMPUTE 或 COMPUTE BY 
（2）ORDER BY（除非指定了 TOP 子句） 
（3）INTO 
（4）帶有查詢提示的 OPTION 子句 
（5）FOR XML 
（6）FOR BROWSE

 6、如果將 CTE 用在屬於批處理的一部分的語句中，那麼在它之前的語句必須以分號結尾，

 

參考連結 

 

sqlserver 樹形結構表查詢 擷取拼接結果