本文介绍了如何将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结果应用于变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-27 01:56