求助报错：选择列表中的列 'Device_IoTData.：RulesJosn' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中

[zhang1064866281]

运行时报错说没有添加到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

[二月十六]

如果用到聚会函数，其他列应该在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'))

[zhang1064866281]

问题已经解决了，谢谢大家帮助

[zhang1064866281]

成功解决，谢谢大家