本文介绍了如何将CTE-JSON结果应用于变量的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个这样的简单CTE:
DECLARE @Json nvarchar(MAX);
WITH cteBuildJSON(Sequence,level,WBS,parentId)AS(
SELECT anchor.Sequence,0 as level,Cast(mnu_order As NVARCHAR(512))AS WBS,
mnu_IDParentSequence as parentId
来自tblMenuDefinition作为锚
WHERE mnu_IDParentSequence为null并且mnu_State = 0并且mnu_IDLicenseSequence为null
UNION ALL
选择recur.Sequence,cte.level + 1,Cast(cte.WBS +'。'+ Cast(recur.mnu_order As NVARCHAR(512))As NVARCHAR(512))AS WBS,
recur.mnu_IDParentSequence as parentId
FROM tblMenuDefinition AS recur
INNER JOIN cteBuildJSON as cte On cte.Sequence = recur.mnu_IDParentSequence
where recur.mnu_State = 0)
SELECT *
FROM cteBuildJSON
按WBS排序
对于JSON路径
如何将结果应用于声明的可变量@Json?
谢谢,
问候,
Arno
决策案
I have a simple CTE like this:
DECLARE @Json nvarchar(MAX); WITH cteBuildJSON(Sequence,level,WBS,parentId) AS ( SELECT anchor.Sequence,0 as level, Cast(mnu_order As NVARCHAR(512)) AS WBS, mnu_IDParentSequence As parentId From tblMenuDefinition As anchor WHERE mnu_IDParentSequence Is null And mnu_State=0 And mnu_IDLicenseSequence Is null UNION ALL Select recur.Sequence, cte.level + 1, Cast(cte.WBS + '.' + Cast(recur.mnu_order As NVARCHAR(512)) As NVARCHAR(512)) AS WBS, recur.mnu_IDParentSequence As parentId FROM tblMenuDefinition AS recur INNER JOIN cteBuildJSON As cte On cte.Sequence = recur.mnu_IDParentSequence Where recur.mnu_State=0 ) SELECT * FROM cteBuildJSON Order By WBS For JSON Path
How can I apply th result to the declared varable @Json?
Thanks,
Regards,
Arno
解决方案
这篇关于如何将CTE-JSON结果应用于变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!