日期:2014-05-18  浏览次数:20695 次

为什么说“因为该列没有包含在聚合函数或 GROUP BY 子句中”
SQL code
INSERT INTO PorderSub(LineID,PorderID,ProductID,ProductSN,ProductCode,ProductName,Spec,Manufactory,ApprovalID,InsuranceID,PackRate1,PackRate2,ConvertRate,
RequestQuantity,RequestPrice,RequestAmount,Quantity,Price,Amount,Remark)
Select ROW_NUMBER() OVER(ORDER BY ProductID),PorderID,ProductID,ProductSN,ProductCode,ProductName,Spec,Manufactory,ApprovalID,InsuranceID,PackRate1,PackRate2,ConvertRate,
SUM(RequestQuantity),RequestPrice,SUM(RequestAmount),SUM(Quantity),Price,SUM(Amount),Remark
From #PorderSub
Group By ProductID,PorderID,ProductSN,ProductCode,ProductName,Spec,Manufactory,InsuranceID,PackRate1,PackRate2,ConvertRate,RequestPrice,Price,Remark



消息 8120,级别 16,状态 1,第 1 行
选择列表中的列 '#PorderSub.ApprovalID' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。

这是为什么?

------解决方案--------------------
选择列表中有形如SUM,COUNT等聚合函数,需要将所有SELECT的列加入到GROUP BY子句中去,你选择了ApprovalID列在GROUP BY子句中没有ApprovalID,所以会报错。
------解决方案--------------------
你在Group By 後面加上ApprovalID 不就可以了.
------解决方案--------------------
SQL code

你漏了
INTO PorderSub(LineID,PorderID,ProductID,ProductSN,ProductCode,ProductName,Spec,Manufactory,ApprovalID,InsuranceID,PackRate1,PackRate2,ConvertRate,
RequestQuantity,RequestPrice,RequestAmount,Quantity,Price,Amount,Remark)

Select ROW_NUMBER() OVER(ORDER BY ProductID),PorderID,ProductID,ProductSN,ProductCode,ProductName,Spec,Manufactory,
ApprovalID,
InsuranceID,PackRate1,PackRate2,ConvertRate,
SUM(RequestQuantity),RequestPrice,SUM(RequestAmount),SUM(Quantity),Price,SUM(Amount),Remark
From #PorderSub
Group By ProductID,PorderID,ProductSN,ProductCode,ProductName,Spec,Manufactory,ApprovalID,
InsuranceID,PackRate1,PackRate2,ConvertRate,RequestPrice,Price,Remark

------解决方案--------------------
SQL code

group by 
PorderID,ProductID,ProductSN,ProductCode,ProductName,Spec,
Manufactory,ApprovalID,InsuranceID,PackRate1,PackRate2,ConvertRate,RequestPrice,Price,Remark