如何解决SQL Error (1055): SELECT列表表达式不在GROUP BY子句报错

报错信息 #

SQL Error (1055): Expression #7 of Select list is not in group by Clause and contains nonaggregated column 'test_db.pid.product_id' which is not Functionally dependent on columns in group by clause; this is incomplete with sql_mode=only_full_group_by

该报错核心原因：SELECT列表中第7个计算库存的表达式（IFNULL(...) 部分）引用了非聚合字段pid.product_id，该字段没有出现在GROUP BY子句中，也不满足MySQL的函数依赖校验规则，因此被only_full_group_by模式拦截。

问题定位 #

原SQL的问题出在计算quantity的子查询关联条件：子查询中写了WHERE pbd.product_id=pid.product_id，其中pid是出库明细表product_issue_details的别名，在外层查询做分组聚合时，pid.product_id既没有被加入GROUP BY列表，也没有被聚合函数包裹，违反了only_full_group_by的校验要求。
由于原SQL已经通过LEFT JOIN products AS pro ON pro.id=pid.product_id做了等值关联，且分组维度包含pro.id，同一分组下所有pid.product_id的值都和pro.id完全相等，逻辑上可以直接替换使用。

修复方案 #

方案1：最小改动修复 #

直接将子查询中的关联条件从pid.product_id替换为已经在GROUP BY列表中的pro.id，逻辑完全等价，不需要调整整体查询结构，修正后的SQL如下：

SELECT 
  pro.name AS product_name,
  pro.id AS product_id,
  pro.model AS product_model,
  bnd.name AS brand_name,
  sub_cat.name AS sub_category_name,
  cat.name AS category_name,
  IFNULL(
    SUM(pid.quantity) - (
      SELECT SUM(pbd.quantity)
      FROM product_bill_details AS pbd
      WHERE pbd.product_id = pro.id -- 替换为分组内的pro.id，避免引用未聚合的pid字段
      GROUP BY pbd.product_id
    ),
    SUM(pid.quantity)
  ) AS quantity
FROM product_issue_details AS pid
LEFT JOIN product_issue_masters AS pim ON pid.product_issue_master_id = pim.id
LEFT JOIN products AS pro ON pro.id = pid.product_id
LEFT JOIN brands AS bnd ON pro.brand_id = bnd.id
LEFT JOIN sub_categories AS sub_cat ON sub_cat.id = bnd.sub_category_id
LEFT JOIN categories AS cat ON cat.id = sub_cat.category_id
WHERE pim.project_id = 1
GROUP BY pro.id, pro.name, pro.model, bnd.name, cat.name, sub_cat.name;

方案2：性能更优的改写（推荐） #

原写法中相关子查询会对外层查询的每一个分组做一次子查询计算，数据量大时性能较差。可以先将两个明细表的聚合结果单独计算后再做关联，既符合only_full_group_by规则，也能提升查询效率：

SELECT 
  pro.name AS product_name,
  pro.id AS product_id,
  pro.model AS product_model,
  bnd.name AS brand_name,
  sub_cat.name AS sub_category_name,
  cat.name AS category_name,
  IFNULL(pid_total.total_quantity - pbd_total.total_quantity, pid_total.total_quantity) AS quantity
FROM (
  -- 先聚合算出每个产品的总出库量
  SELECT pid.product_id, SUM(pid.quantity) AS total_quantity
  FROM product_issue_details AS pid
  LEFT JOIN product_issue_masters AS pim ON pid.product_issue_master_id = pim.id
  WHERE pim.project_id = 1
  GROUP BY pid.product_id
) AS pid_total
LEFT JOIN (
  -- 先聚合算出每个产品的总账单数量
  SELECT pbd.product_id, SUM(pbd.quantity) AS total_quantity
  FROM product_bill_details AS pbd
  GROUP BY pbd.product_id
) AS pbd_total ON pid_total.product_id = pbd_total.product_id
LEFT JOIN products AS pro ON pro.id = pid_total.product_id
LEFT JOIN brands AS bnd ON pro.brand_id = bnd.id
LEFT JOIN sub_categories AS sub_cat ON sub_cat.id = bnd.sub_category_id
LEFT JOIN categories AS cat ON cat.id = sub_cat.category_id;

注意：如果产品、品牌、分类等维度表存在一对多关联导致数据重复，可以在最后关联维度表后补充GROUP BY pro.id, pro.name, pro.model, bnd.name, cat.name, sub_cat.name, pid_total.total_quantity, pbd_total.total_quantity，完全符合only_full_group_by语法要求。

内容的提问来源于stack exchange，提问作者Julan Mohajan