我有下表：
| ID | 等級 ID | 姓名 |
|---|---|---|
| 1 | / | 兆赫 |
| 2 | /1/ | 金融 |
| 3 | /1/ | 它 |
| 4 | /1/3/ | 軟體開發 |
| 5 | /1/3/ | 質量保證 |
| 6 | /1/2/ | 會計 |
我想為我的TreeView獲取資料，例如：
[
{
"Id": 1,
"HierarchyId": "/"
"Name":"MHz",
"Children":[
{
"Id": 3,
"HierarchyId": "/1/"
"Name":"IT",
"Children":[
{
"Id": 4,
"HierarchyId": "/1/3/"
"Name":"Software Development",
"Children":[]
}
{
"Id": 5,
"HierarchyId": "/1/3/"
"Name":"QA",
"Children":[]
}
]
},
{
"Id": 2,
"HierarchyId": "/1/"
"Name":"Finance",
"Children":[
{
"Id": 6,
"HierarchyId": "/1/2/"
"Name":"Accountant",
"Children":[]
}
]
}
]
}
]
我試過這個：如何生成分層 JSON 資料
出現錯誤：事務中沒有更多可用的鎖類。
還是創建遞回函式并填充Treeon更好client side？
更新
CREATE FUNCTION dbo.GetJson (@departmentId hierarchyid)
RETURNS nvarchar(max)
AS BEGIN
RETURN (
SELECT
Id,
HierarchyId,
Name,
children = JSON_QUERY(dbo.GetJson(HierarchyId))
FROM Departments p
WHERE p.HierarchyId.IsDescendantOf(@departmentId ) = 1
FOR JSON PATH
);
END;
uj5u.com熱心網友回復：
我發現做到這一點的最好方法是使用遞回函式。
您可以使用獲取節點的所有后代，IsDescendantOf但需要確保排除當前節點
CREATE FUNCTION dbo.GetJson (@parentID hierarchyid)
RETURNS nvarchar(max)
AS BEGIN
RETURN (
SELECT
t.Id,
t.HierarchyId,
t.Name,
children = JSON_QUERY(dbo.GetJson(t.HierarchyId))
FROM Tree t
WHERE t.HierarchyId <> @parentID
AND t.HierarchyId.IsDescendantOf(@parentID) = 1
FOR JSON PATH
);
END;
go
SELECT dbo.GetJson(hierarchyid::GetRoot());
db<>小提琴
結果
[
{
"Id": 2,
"HierarchyId": "/1/",
"Name": "Finance",
"children": [
{
"Id": 4,
"HierarchyId": "/1/3/",
"Name": "Software Development"
},
{
"Id": 5,
"HierarchyId": "/1/3/",
"Name": "QA"
},
{
"Id": 6,
"HierarchyId": "/1/2/",
"Name": "Accountant"
}
]
},
{
"Id": 3,
"HierarchyId": "/1/",
"Name": "IT",
"children": [
{
"Id": 4,
"HierarchyId": "/1/3/",
"Name": "Software Development"
},
{
"Id": 5,
"HierarchyId": "/1/3/",
"Name": "QA"
},
{
"Id": 6,
"HierarchyId": "/1/2/",
"Name": "Accountant"
}
]
},
{
"Id": 4,
"HierarchyId": "/1/3/",
"Name": "Software Development"
},
{
"Id": 5,
"HierarchyId": "/1/3/",
"Name": "QA"
},
{
"Id": 6,
"HierarchyId": "/1/2/",
"Name": "Accountant"
}
]
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/460574.html