关于Sql server数据类型HierarchyID 数据类型用法和递归显示完整路径

内置的 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