運行時報錯說沒有添加到group by里面，可是這是決議的josn不知道該怎么加求助指點迷津
select 202006,a.ProductName,a.SpecName,a.ProceName,a.amount,bp.wprice
from
(
select
SpecName=(select TOP 1 BusinessName from openjson(RulesJosn) with (BusinessName nvarchar(50) '$.BusinessName',BusinessType int '$.BusinessType') where BusinessType=3)
,ProceName=(select TOP 1 BusinessName from openjson(RulesJosn) with (BusinessName nvarchar(50) '$.BusinessName',BusinessType int '$.BusinessType') where BusinessType=2)
,ProductName=(select TOP 1 BusinessName from openjson(RulesJosn) with (BusinessName nvarchar(50) '$.BusinessName',BusinessType int '$.BusinessType') where BusinessType=7)
,Amount=sum(Amount)
,ProceID=(select ProceID from openjson(GroupDetail) with (ProceID uniqueidentifier '$.ProceID'))
,SpecID=(select SpecID from openjson(GroupDetail) with (SpecID uniqueidentifier '$.SpecID'))
,ProductID=(select ProductID from openjson(GroupDetail) with (ProductID uniqueidentifier '$.ProductID'))
from Device_IoTData
) a
join Basic_ProceRule bp on a.proceID=bp.proceID and a.Specid=bp.specid and a.productid=bp.productId where
a.proceID=bp.proceID and a.Specid=bp.specid and a.productid=bp.productId
GROUP BY a.productName,a.SpecName,a.ProceName,a.Amount
uj5u.com熱心網友回復：
如果用到聚會函式，其他列應該在group by中。Amount=sum(Amount)
應該加上
from iotdata group by RulesJosn
但是這樣可能和你原來的想查詢的結果就不一樣了
另外下邊的查詢沒加top 1 也可能會報錯
,ProceID=(select ProceID from openjson(GroupDetail) with (ProceID uniqueidentifier '$.ProceID'))
,SpecID=(select SpecID from openjson(GroupDetail) with (SpecID uniqueidentifier '$.SpecID'))
,ProductID=(select ProductID from openjson(GroupDetail) with (ProductID uniqueidentifier '$.ProductID'))
uj5u.com熱心網友回復：
問題已經解決了，謝謝大家幫助uj5u.com熱心網友回復：
成功解決，謝謝大家轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/19437.html
標籤：疑難問題