SQL 错误 '1140 - In aggregated query without GROUP BY...' 解決方案

SQL 错误 '1140 - In aggregated query without GROUP BY...' 解決方案

当你在 SQL 查询中使用聚合函数（如 sum()）时，但没有使用 GROUP BY 子句，可能会出现错误 '1140 - In aggregated query without GROUP BY...'. 这是因为在没有 GROUP BY 的情况下，数据库无法确定如何将非聚合列与聚合结果关联起来。

错误示例:

SELECT
    ManufacturingOrder,
    Material,
    Reservation,
    ReservationItem,
    sum(RequiredQuantity),
    ManufacturingOrderOperation,
    BillOfMaterialItemNumber,
    GoodsMovementType,
    MaterialGroup 
FROM
    ods_productionordercomponent 
WHERE
    ManufacturingOrder ='1000098000'
    AND Material = '068840280104007'

解决方法:

有两种方法可以解决这个问题:

1. 添加 GROUP BY 子句:

   如果需要对非聚合列进行分组，并显示每个分组的结果，可以使用 GROUP BY 子句将所有非聚合列包含在其中。

   SELECT
       ManufacturingOrder,
       Material,
       Reservation,
       ReservationItem,
       sum(RequiredQuantity),
       ManufacturingOrderOperation,
       BillOfMaterialItemNumber,
       GoodsMovementType,
       MaterialGroup 
   FROM
       ods_productionordercomponent 
   WHERE
       ManufacturingOrder ='1000098000'
       AND Material = '068840280104007'
   GROUP BY
       ManufacturingOrder,
       Material,
       Reservation,
       ReservationItem,
       ManufacturingOrderOperation,
       BillOfMaterialItemNumber,
       GoodsMovementType,
       MaterialGroup;
   

2. 修改 SQL 模式参数:

   如果只需要显示聚合结果，不需要对非聚合列进行分组，可以修改 sql_mode 参数，将 only_full_group_by 移除。

   SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
   SELECT
       ManufacturingOrder,
       Material,
       Reservation,
       ReservationItem,
       sum(RequiredQuantity),
       ManufacturingOrderOperation,
       BillOfMaterialItemNumber,
       GoodsMovementType,
       MaterialGroup 
   FROM
       ods_productionordercomponent 
   WHERE
       ManufacturingOrder ='1000098000'
       AND Material = '068840280104007';
   

注意:

• 修改 sql_mode 参数可能会影响其他查询的行为，因此在修改之前要谨慎评估。
• 选择合适的解决方案取决于你的具体需求。