看begtut.com/课题时想到的关于group by使用和报错：ERROR 1055 (42000)的心得-CSDN博客

本文链接：https://blog.csdn.net/morningu/article/details/128699890

#课题地址：https://www.begtut.com/mysql/mysql-cross-join.html

#遇到的两个问题

#问题1：为什么在下面的查询中要用到聚合函数sum和group by分组？

SELECT 
    store_name,
    product_name,
    SUM(quantity * price) AS revenue
FROM
    sales
        INNER JOIN
    products ON products.id = sales.product_id
        INNER JOIN
    stores ON stores.id = sales.store_id
GROUP BY store_name , product_name;

#猜想可能查询出来有“同一家店的同一种型号在不同时间点上有出货”的可能，所以聚合统计一下。只不过题目给的数据没有涉及到相同店铺在不同日子里售卖了相同型号的产品。所以自行设计改进了一下：
#（1）、在创建中间表（sales表）时，增加一列自增长的id值替换掉原来设计表中的联合主键，这样可以添加“同一家店在不同日子里都有售卖出相同的一款产品”，以模拟出“按日记账的“sales表。

CREATE TABLE sales (
    id int auto_increment,
    product_id INT,
    store_id INT,
    quantity DECIMAL(13 , 2 ) NOT NULL,
    sales_date DATE NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (product_id)
        REFERENCES products (id)
        ON UPDATE cascade ON DELETE set null,   //外键约束删除/更新行为的顺序是无所谓的，这里先定义UPDATE
    FOREIGN KEY (store_id)
        REFERENCES stores (id)
        ON DELETE set null ON UPDATE cascade   //这里先定义DELETE
);

#（2）、另外执行添加一条联会唯一的约束（sales表中的store_id,product_id,sales_date字段组成），以模拟”避免员工重复录入当天销售数据的误操作“情景。

alter table sales add constraint unique (store_id,product_id,sales_date);

#在给sales表加数据时，再来一条同一家店铺同一型号的售卖记录：

INSERT INTO sales (store_id,product_id,quantity,sales_date) VALUES (1,1,23,'2017-01-07')；

#然后可以试一下，上面有聚合函数sum和分组的结果是什么样的。
#再试一下下面没有聚合函数sum和分组的结果是什么样的。

SELECT 
    store_name,
    product_name,
    (quantity * price) AS revenue
FROM
    sales
        INNER JOIN
    products ON products.id = sales.product_id
        INNER JOIN
    stores ON stores.id = sales.store_id;

#问题2：在课题最后进行”cross join“的演示时，直接用课题代码在8.x版本的MySQL中会报错：ERROR 1055 (42000)。

#原因是默认设置的sql_mode=only_full_group_by，该参数表示：MySQL默认开启了SQL_MODE严格模式，对数据进行严格校验。如果代码中含有group by聚合操作，那么select中的字段（列名）只能是要group by的字段或聚合函数（或者在group by后加入select后多出的字段，虽然这看起来不符合开发最初要表达的意思……）。课题给的代码，select后多了两个group by后没有的stores.id AS store_id和products.id AS product_id字段，所以报错了。
#虽然课题中给的数据，在做完on条件join连接后只有不重复的5行数据，但是依然会在group by时因为”sql_mode=only_full_group_by严格模式“而”严（机）格（械）“报错，所以不要纠结。

#解决办法是：

#（1）在group by 后”画蛇添足“的加上select中多出的stores.id AS store_id和products.id AS product_id字段（写原名”stores.id“或者as后的别名”store_id“都行，因为执行到group by时是可以读到select后起的别名的）。

SELECT 
    b.store_name,
    a.product_name,
    IFNULL(c.revenue, 0) AS revenue
FROM
    products AS a
        CROSS JOIN
    stores AS b
        LEFT JOIN
    (SELECT 
        stores.id AS store_id,
        products.id AS product_id,
        store_name,
        product_name,
        ROUND(SUM(quantity * price), 0) AS revenue
    FROM
        sales
    INNER JOIN products ON products.id = sales.product_id
    INNER JOIN stores ON stores.id = sales.store_id
    GROUP BY store_name,product_name,stores.id ,products.id ) AS c 
        ON c.store_id = b.id
        AND c.product_id= a.id
ORDER BY b.store_name; 
//（注意子程序里的别名问题，不设置别名的话，子查询的结果会有相同的列：”id“，虽然子查询明白，但是再和别的表join时，那就”傻傻分不清“了。）

#（2）因为实例中给的数据设计，stores.id和stores.store_name是对应的，producus.id也是和products.product_name对应的，所以个人感觉没必要在和子查询（”店铺各型号售卖总额统计表“）连接时画蛇添足的用店铺id和产品id关联（ON c.store_id = b.id AND c.product_id= a.id这里），直接用店铺名字和商品名字关联不就好了（ON c.store_name = b.store_name AND c.product_name= a.product_name）。

SELECT 
    b.store_name,
    a.product_name,
    IFNULL(c.revenue, 0) AS revenue
FROM
    products AS a
        CROSS JOIN
    stores AS b
        LEFT JOIN
    (SELECT 
        store_name,
        product_name,
        ROUND(SUM(quantity * price), 0) AS revenue
    FROM
        sales
    INNER JOIN products ON products.id = sales.product_id
    INNER JOIN stores ON stores.id = sales.store_id
    GROUP BY store_name , product_name) AS c ON c.store_name = b.store_name
        AND c.product_name= a.product_name
ORDER BY b.store_name;

#又或者设计子查询时，让select返回的是id，group by的也是id，道理是一样的。

SELECT 
    b.store_name,
    a.product_name,
    IFNULL(c.revenue, 0) AS revenue
FROM
    products AS a
        CROSS JOIN
    stores AS b
        LEFT JOIN
    (SELECT 
        stores.id AS store_id,
        products.id AS product_id,
        ROUND(SUM(quantity * price), 0) AS revenue
    FROM
        sales
    INNER JOIN products ON products.id = sales.product_id
    INNER JOIN stores ON stores.id = sales.store_id
    GROUP BY stores.id, products.id) AS c ON c.store_id = b.id
        AND c.product_id= a.id
ORDER BY b.store_name;

ps.看到外键约束，可以回想复习一下外键约束怎么设置（建表时和建表后）外键约束的行为有哪些，建表后怎么删除和更改（感觉”更改“只能删除后再添加，还请知道别的更改外键约束行为的大佬指点迷津）。