Sqlserver如何遞迴查詢層級資料將父級欄位和本級某個欄位合并？如何自訂使用者函數並調用？

標籤：har   bubuko   名稱   nbsp   png   def   接下來   members   turn   

     開門見山，首先說下遇到的問題：前期系統地區字典表中，每個省市縣只存了本級名稱，沒存完整的欄位。如：肥西縣隸屬安徽省合肥市，表中就存了一個肥西縣。現有需求需要將完整欄位顯示，由於系統已線上上運營，無法做過多複雜修改，初步定的方案是在表中新追加一個欄位，將欄位補齊，一是方便修改，二是為了後期如果別的功能用到可以拿新欄位使用，簡化工作。

 好了，問題已經明確，接下來就想想怎麼解決問題了。也不是什麼比較難得問題，剛好閑著無聊，就開啟園子寫下隨筆，或許能幫到有相關問題的人。

 圍繞兩個問題來說。  

Sqlserver如何遞迴查詢層級資料將父級欄位和本級某個欄位合并？

       我們都知道oracle中遞迴是通過connect by prior 來實現的， 那sqlserver中如何?呢？sqlserver中是沒有此關鍵字輔助的。

       地區表結構如下：

           

 

    sqlserver遞迴代碼貼上：

     ------查詢樹結構某節點的上級所有跟節點

with areadata (sID,sSuperID,sName)
as
(

---起始條件
select m.sID,m.sSuperID,m.sName
from [AdoptionRegister_Membership].[dbo].[mdb_Area] m where sID=‘340824‘   --列出子節點查詢條件

--遞迴條件
union all
select a.sID,a.sSuperID,a.sName
from [AdoptionRegister_Membership].[dbo].[mdb_Area] a
inner join
areadata b on a.sID=b.sSuperID  

--根據子節點父級欄位查詢父級資訊
)

select  * from areadata

查詢結果如下：

       接下來 我們現在要考慮如何將欄位拼接合成，這裡我們可以使用stuff來完成，代碼如下：

select stuff((
select ‘‘+sName
from areadata where sID!=‘000000‘ order by sID asc for xml path(‘‘)),1,0,‘‘) as name ;     -----sID!=‘000000‘ 這裡是過濾全國這行資料

查詢效果如下：

      OK，以上已初步解決了如何遞迴查詢層級資料將父級欄位和本級某個欄位合并問題！現在我們繼續看待第二個問題。

       如何自訂使用者函數並調用？

       其實就是建立一個自訂標量值自訂函數，將我們剛才寫的遞迴查詢封裝起來，並返回合并後的值，這個地方就很簡單了。代碼如下：

USE [AdoptionRegister_Manage]
GO
/****** Object: UserDefinedFunction [dbo].[GET_Area_FullName] Script Date: 01/10/2018 15:50:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION [dbo].[GET_Area_FullName]
(
@sid nvarchar(40)

)
RETURNS nvarchar(40)

AS

BEGIN

DECLARE @str nvarchar(40)

set @str=N‘未查到‘;

with areadata (sID,sSuperID,sName)
as
(
select m.sID,m.sSuperID,m.sName
from [AdoptionRegister_Membership].[dbo].[mdb_Area] m where [email protected]
union all
select a.sID,a.sSuperID,a.sName
from [AdoptionRegister_Membership].[dbo].[mdb_Area] a
inner join
areadata b on a.sID=b.sSuperID
)
select @str=stuff((
select ‘‘+sName
from areadata where sID!=‘000000‘ order by sID asc for xml path(‘‘)),1,0,‘‘)

RETURN @str

END

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

       測試調用

      select  dbo.GET_Area_FullName(‘340824‘)

        -----------

        至此我們已經解決了拋出的問題，最後運用函數將表中新加欄位全域更新就OK了！

       update [AdoptionRegister_Membership].[dbo].[mdb_Area] set sFullName=dbo.GET_Area_FullName(sID) 

       看下最終結果：

結束，收工！希望對你有協助！

 

Sqlserver如何遞迴查詢層級資料將父級欄位和本級某個欄位合并？如何自訂使用者函數並調用？