如何在不使用CROSS和ROWNUMBER的情况下获得相同的结果 [英] How to get same result without using CROSS and ROWNUMBER

问题描述

With Tb1 as
(select Date,Particulars,BillAmount,0'PaidAmount' from tblBill
union
select Date,Particulars,0'BillAmount',PaidAmount from tblPayment
)

SELECT T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount],(Sum(T2.BillAmount) - Sum(T2.PaidAmount)) as Balance FROM Tb1 as T1
            INNER JOIN
                Tb1 as T2
                ON T1.[date] >= T2.[date]
                Group By T1.Particulars,T1.[Date],T1.[BillAmount],T1.[PaidAmount]
                Order by [Date]

< b>我尝试了什么：

What I have tried:

DECLARE @JsonParsing NVARCHAR(MAX) = N'{"HD" :["CombinedMetadata:energy","CombinedMetadata:enron","energy","enron","information","time"],"HF":[2, 2, 3, 35, 2, 8],"REx":[]}'

SELECT S.DATA,SUM(t.Frequency) AS FREQUENCY 
	FROM (SELECT RIGHT(q.VALUE, LEN(q.VALUE) - CHARINDEX(':', q.VALUE)) AS DATA,
				ROW_NUMBER() OVER(ORDER BY(SELECT 0)) AS Row_nu
			FROM OPENJSON(@JsonParsing)
	WITH (HD NVARCHAR(MAX) AS JSON) AS p
	CROSS APPLY OPENJSON(p.HD) AS q) s,
		 (SELECT CONVERT(INT, b.VALUE) AS Frequency,
				ROW_NUMBER() OVER(ORDER BY(SELECT 0)) AS Row_nu
			FROM OPENJSON(@JsonParsing)
	WITH (HF NVARCHAR(MAX) AS JSON) AS a
	CROSS APPLY OPENJSON(a.HF) AS b) t
	WHERE t.Row_nu = s.Row_nu
	GROUP BY s.DATA

推荐答案

Google是你的朋友：



在SQL Server中使用JSON数据 - SQL Server Microsoft Docs [ ^ ]

Google is your friend:

Work with JSON data in SQL Server - SQL Server | Microsoft Docs[^]

这篇关于如何在不使用CROSS和ROWNUMBER的情况下获得相同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！