關於Sql server數據類型HierarchyID 數據類型用法和遞歸顯示完整路徑

對於資料庫表中的大類,小類我們基本一直在使用id ,parentid的方式，今天發現了一種更清晰，更完美的解決方式。

SQL Server 2008版本之後的新類型HierarchyID 不知道大家有沒有瞭解， 該類型作為取代id, parentid的一種解決方案，讓人非常驚喜。

官方給的案例淺顯易懂，但是沒有實現我想要的基本功能，樹形結構中完整名稱路徑的展示。本文末尾是一個完整路徑的樣例，需要更多基本操作可以參考文末微軟鏈接

另外，現在基本不太碰Oracle資料庫了，平時也沒怎麼研究SQL Server.

希望本文對有這方面需求的同學有一定幫助，完整示例如下

內置的 hierarchyid 數據類型使存儲和查詢層次結構數據變得更為容易。

下麵為一個完整的實現例子

數據表的準備和結構

CREATE TABLE SimpleDemo

(

Level hierarchyid NOT NULL,

Location nvarchar(30) NOT NULL,

LocationType nvarchar(9) NULL

);

現在插入一些洲、國家/地區、州和城市的數據。

INSERT SimpleDemo  
    VALUES   
('/1/', 'Europe', 'Continent'),  
('/2/', 'South America', 'Continent'),  
('/1/1/', 'France', 'Country'),  
('/1/1/1/', 'Paris', 'City'),  
('/1/2/1/', 'Madrid', 'City'),  
('/1/2/', 'Spain', 'Country'),  
('/3/', 'Antarctica', 'Continent'),  
('/2/1/', 'Brazil', 'Country'),  
('/2/1/1/', 'Brasilia', 'City'),  
('/2/1/2/', 'Bahia', 'State'),  
('/2/1/2/1/', 'Salvador', 'City'),  
('/3/1/', 'McMurdo Station', 'City');

此外，此表未使用層次結構頂層 '/'。 該層被省略，因為沒有所有州的公共父級。 可以通過添加整個星球來添加一個頂層。

INSERT SimpleDemo

VALUES ('/', 'Earth', 'Planet');

看下麵的語句是通過GetAncestor 來達到完整路徑顯示的關鍵。

下麵實現顯示完整路徑的SQL腳本

WITH ancestor_path

AS (

SELECT [level],

location,

CAST(LEVEL.GetAncestor(1) AS VARCHAR(1000)) AS parent_id,

CAST(location AS VARCHAR(1000)) AS path

FROM SimpleDemo

WHERE LocationType = 'Planet'
UNION ALL
SELECT d.[level],

d.location,

CAST(d.LEVEL.GetAncestor(1) AS VARCHAR(1000)),

CAST(CONCAT (

ap.path,

' > ',

d.location

) AS VARCHAR(1000))

FROM SimpleDemo d

JOIN ancestor_path ap

ON d.[level].GetAncestor(1) = ap.[level]

)

SELECT *

FROM ancestor_path;

SELECT CAST(LEVEL.GetAncestor(1) AS VARCHAR(1000)) AS LevelName,

location,

locationtype

FROM SimpleDemo

最原始鏈接可以參考

https://learn.microsoft.com/zh-cn/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver16

本文來自博客園，作者：Tracy.，轉載請註明原文鏈接：https://www.cnblogs.com/tracy/p/18070870