MySQL 8.0.31多表关联查询报错，如何获取商品性别类型信息？

方案1：将非聚合列加入GROUP BY子句 #

如果每个商品分组对应的g.type是唯一值，直接把g.type添加到GROUP BY列表中，符合ONLY_FULL_GROUP_BY模式的要求：

SELECT 
    p.id AS product_id,
    p.name AS product_name,
    c.name AS category_name,
    sc.name AS sub_category_name,
    COUNT(DISTINCT po.color) AS color_count,
    g.type AS gender_type
FROM products p
JOIN sub_categories sc ON p.sub_category_id = sc.id
JOIN categories c ON sc.category_id = c.id
JOIN products_options po ON p.id = po.product_id
JOIN genders g ON p.gender_id = g.id -- 假设商品与性别为一对一关联
GROUP BY p.id, p.name, c.name, sc.name, g.type;

方案2：用聚合函数处理性别字段 #

如果同一分组内可能存在多个g.type值，或想确保返回有效结果，可通过聚合函数（如MAX()、MIN()，字符串类型同样适用）包裹g.type：

SELECT 
    p.id AS product_id,
    p.name AS product_name,
    c.name AS category_name,
    sc.name AS sub_category_name,
    COUNT(DISTINCT po.color) AS color_count,
    MAX(g.type) AS gender_type -- 用聚合函数处理非聚合列
FROM products p
JOIN sub_categories sc ON p.sub_category_id = sc.id
JOIN categories c ON sc.category_id = c.id
JOIN products_options po ON p.id = po.product_id
JOIN genders g ON p.gender_id = g.id
GROUP BY p.id, p.name, c.name, sc.name;

补充说明 #

MySQL 8.0默认启用ONLY_FULL_GROUP_BY SQL模式，要求GROUP BY子句必须包含SELECT列表中所有非聚合列，或这些列能被GROUP BY中的列唯一确定（比如主键）。若products.id是主键，理论上p.name、c.name、sc.name可不加入GROUP BY，但显式列出所有非聚合列会让SQL更具兼容性和可读性。

内容的提问来源于stack exchange，提问作者Jay Lee