这是
a part of this question
1.我有
Departments
使用
HierarchyId
存储分层数据。
身份证件
|
层次结构
|
名称
|
1.
|
/
|
兆赫
|
2.
|
/2/
|
资金
|
3.
|
/3/
|
信息技术
|
4.
|
/3/4/
|
软件开发
|
5.
|
/3/5/
|
质量保证
|
6.
|
/2/6/
|
会计
|
我想要这张桌子
JSON
为了我的
TreeView
在前面。
我所做的:
CREATE FUNCTION dbo.GetDepartmentAsJson
(@departmentId hierarchyid,
@IsRoot int)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @Json NVARCHAR(MAX) = '{}',
@Id int,
@Name varchar(50),
@Children NVARCHAR(MAX) = '{}',
@Hierar Hierarchyid
SET @Json = (SELECT
t.Id, t.HierarchyId, t.Name,
children = JSON_QUERY(dbo.GetDepartmentAsJson(t.HierarchyId, 2))
FROM Departments t
WHERE t.HierarchyId <> @departmentId
AND t.HierarchyId.IsDescendantOf(@departmentId) = 1
FOR JSON PATH);
IF (@IsRoot = 1)
BEGIN
SELECT
@Id = t.Id,
@Hierar = t.HierarchyId,
@Name = t.Name
FROM
Departments t
WHERE
t.HierarchyId = @departmentId;
SET @Json =
'{"Id":"' + CONVERT(varchar(7), @Id) +
'","HierarchyId":"' + @Hierar.ToString() +
'","Name":"' + @Name +
'","Children":' + CAST(@Json AS NVARCHAR(MAX)) + '}';
SET @IsRoot = 2;
END
RETURN @Json;
END;
结果:
{
"Id": "1",
"HierarchyId": "/",
"Name": "MHz",
"Children": [
{
"Id": 2,
"HierarchyId": "/2/",
"Name": "Finance",
"Children": [
{
"Id": 6,
"HierarchyId": "/2/6/",
"Name": "Accountant"
}
]
},
/* This should not be listed because already included as a child in Finance */
{
"Id": 6,
"HierarchyId": "/2/6/",
"Name": "Accountant"
},
{
"Id": 3,
"HierarchyId": "/3/",
"Name": "IT",
"Children": [
{
"Id": 4,
"HierarchyId": "/3/4/",
"Name": "Software Development"
},
{
"Id": 5,
"HierarchyId": "/3/5/",
"Name": "QA"
}
]
},
/* They both should not be listed because already included as a child in IT */
{
"Id": 4,
"HierarchyId": "/3/4/",
"Name": "Software Development"
},
{
"Id": 5,
"HierarchyId": "/3/5/",
"Name": "QA"
}
]
}
如何排除已在父元素中列出的子元素?