?????????????У???2008???SQL Server????????μ?????????hierarchyid?????????????????????????
????hierarchyid  ??????ν??????й??????????????????????????????????????????y???·?????б???
????????·??????????????????????????????????????????С? ???????????б??????????????·???????б?????? ????????μ?????????????????????????????С? ?????????????????????????????????С? ?????????????????б??? ???б???????????????????? ???磬???????????? 1 ????2 ????2 ????3 ???? 3 ??????Ч hierarchyid ·????
???? /
???? /1/
???? /0.3.-7/
???? /1/3/
???? /0.1/0.2/
?????????hierarchyid???????????????CTE??????????????????????????????????????????￡?CTE????????????????????????????????PATH??????????????????????hierarchyid?????????????hierarchyid???
?????????????????????????????hierarchyid????????
???????CTE???
????WITH CTEGetChild AS
????(
????SELECT * FROM EL_Organization.Organization WHERE ID='ecc43c7159924dca91e2916368f923f4' --and [State]=0 and AuditState=2
????UNION ALL
????(
????SELECT A.* FROM EL_Organization.Organization AS A
????INNER JOIN CTEGetChild AS B ON a.PARENTID=B.ID  --and A.[State]=0 and A.AuditState=2
????)
????)
???????????4489?У????25S??
????????CTE???????????????????????????????????????????????????
????????hierarchyid
?????????????????????Σ??????丳???
create function f_cidname(@id varchar(50)) returns varchar(max) as
begin
declare @pids nvarchar(max);
declare @pNames nvarchar(max);
set @pids='';
set @pNames='';
with cte as
( select id??parentid??name from EL_Organization.Organization where id =@id--'00037fdf184e48d084b87c3499e3c0e5'
union all
select b.id??b.parentid??b.name from cte A ??EL_Organization.Organization B where a.parentid = b.id
)
select @pids=convert(varchar(32)??Convert(int?? Convert(varbinary(max)?? id)))  + '/'+ @pids from cte
return '/'+@pids
end
go
????????????????Update????
????UPDATE EL_Organization.Organization SET PIDS=dbo.f_cidname(id)
???????id??guid??32λ?????????hierarchyid??β??????????Path??·?????????????GUID?????????convert(varchar(32)??Convert(int?? Convert(varbinary(max)?? id)))