MySQL GROUP BY子句使用疑问：视图与自定义查询报错差异解析

一、视图DDL未报错的原因 #

• MySQL的分组查询规则中，当sql_mode未开启ONLY_FULL_GROUP_BY，或者开启后SELECT的非聚合列函数依赖于GROUP BY子句中的列时，不会触发报错。
• 视图中GROUP BY的是s.store_id，而store（城市+国家拼接）、manager这两个列的值完全由store_id决定：每个门店（store_id）对应唯一的地址、城市、国家，也对应唯一的店长。这些列属于store_id的函数依赖列，MySQL能识别这种关联关系，因此即使没把它们加入GROUP BY，也能正常执行。

二、修正查询以按国家统计营收 #

你的目标是按国家汇总销售额，但当前查询GROUP BY的是inven.store_id，这会导致每个门店单独统计，同一个国家的多个门店会被拆分成多行，结果不符合需求。正确的处理方式如下：

直接按国家分组的查询 #

调整GROUP BY子句为cont.country，同时统一别名、简化冗余关联：

SELECT 
    cont.country, 
    SUM(pay.amount) AS "Total Sales"
FROM payment AS pay
INNER JOIN rental AS rent ON pay.rental_id = rent.rental_id
INNER JOIN inventory AS inven ON rent.inventory_id = inven.inventory_id
INNER JOIN store AS store ON inven.store_id = store.store_id
INNER JOIN address AS ad ON store.address_id = ad.address_id
INNER JOIN city AS city ON ad.city_id = city.city_id
INNER JOIN country AS cont ON city.country_id = cont.country_id
GROUP BY cont.country
ORDER BY "Total Sales" DESC;

先按门店统计再汇总国家的查询（兼容视图分组逻辑） #

如果需要先保留门店维度的统计数据，再向上汇总国家营收，可以使用子查询：

SELECT 
    cont.country,
    SUM(store_sales.total_sales) AS country_total_sales
FROM (
    SELECT 
        s.store_id,
        cy.country,
        SUM(p.amount) AS total_sales
    FROM payment p
    INNER JOIN rental r ON p.rental_id = r.rental_id
    INNER JOIN inventory i ON r.inventory_id = i.inventory_id
    INNER JOIN store s ON i.store_id = s.store_id
    INNER JOIN address a ON s.address_id = a.address_id
    INNER JOIN city c ON a.city_id = c.city_id
    INNER JOIN country cy ON c.country_id = cy.country_id
    GROUP BY s.store_id, cy.country
) AS store_sales
INNER JOIN country cont ON store_sales.country = cont.country
GROUP BY cont.country
ORDER BY country_total_sales DESC;

内容的提问来源于stack exchange，提问作者Apoorvaa Singh