关系数据库管理系统：mariadb 10.1.44
我的数据库结构如下：

CREATE TABLE product
(
   id int primary key auto_increment,
   name varchar(255),
   is_pack tinyint(1)
);

CREATE TABLE cart
(
   id int primary key auto_increment
);

CREATE TABLE cart_item
(
  id int primary key auto_increment,
  id_cart int not null,
  id_product int not null,
  quantity int not null default 0
);

CREATE TABLE product_pack
(
  id int primary key auto_increment,
  id_pack int not null,
  id_product int not null,
  quantity int not null default 0
);

alter table cart_item
    add constraint cart_item_cart_fk
        foreign key (id_cart) references cart (id);
alter table cart_item
    add constraint cart_item_product_fk
        foreign key (id_product) references product (id);
alter table product_pack
    add constraint product_pack_pack_fk
        foreign key (id_pack) references product (id);
alter table cart_item
    add constraint product_pack_product_fk
        foreign key (id_product) references product (id);

INSERT INTO product (name, is_pack) VALUES ('Product 1', 0);
INSERT INTO product (name, is_pack) VALUES ('Product 2', 0);
INSERT INTO product (name, is_pack) VALUES ('Product 3', 0);
INSERT INTO product (name, is_pack) VALUES ('Product pack 1', 1);

INSERT INTO cart () VALUES ();

-- My cart contains 1x Product 1, 2x Product 3 and 2x Product pack 1
INSERT INTO cart_item (id_cart, id_product, quantity) VALUES (1, 1, 1);
INSERT INTO cart_item (id_cart, id_product, quantity) VALUES (1, 3, 2);
INSERT INTO cart_item (id_cart, id_product, quantity) VALUES (1, 4, 2);

-- Product pack 1 "contains" 2x Product 1 plus 2x Product 2
INSERT INTO product_pack (id_pack, id_product, quantity) VALUES (4, 1, 2);
INSERT INTO product_pack (id_pack, id_product, quantity) VALUES (4, 2, 2);

db小提琴mcre
我有一张table cart_items 它表示 cart 以及 products . 选择那些购物车项目是微不足道的，但我面临一个问题。我实现了产品包，它是用户看不到的“元产品”，并且包含一些其他产品，数量已定义，我找不到如何选择购物车中每个非 Package 产品的数量总和。
例如
我有一包 Pack 1 包含2个 Product 1 和2倍 Product 2 -假设我有1个 Product 1 ，2倍 Product 3 和2倍 Pack 1 在我的车里。然后我希望我的查询返回：

----------------------
product   | quantity |
Product 1 |        5 |
Product 2 |        4 |
Product 3 |        2 |
----------------------

我试过了 JOINING 在我的产品 Package 表上：

SELECT (CASE WHEN p.is_pack = 0 THEN p.id ELSE pp.id END) as id
       , (CASE WHEN p.is_pack = 0 THEN p.name ELSE cp.name END) as name
       , (CASE WHEN p.is_pack = 0 THEN ci.quantity ELSE pp.quantity * ci.quantity END) as quantity 
       FROM cart_item ci
       JOIN product p 
       ON ci.id_product = p.id
       LEFT 
       JOIN product_pack pp 
       ON pp.id_pack = p.id
       LEFT 
       JOIN product cp 
       ON cp.id = pp.id_product

但我当然不能一个产品一条线 SUM 没有 GROUPING BY product.id （我能吗？）。我得到这个错误： Query Error: Error: ER_WRONG_FIELD_WITH_GROUP: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.p.is_pack' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by