mysql - 按类别分类的产品价格总和作为 MySQL 中的列

Question

我有 3 个表，customers，products，categories 如下:

客户:

id      last_name
-----------------
100     Terry
200     molly
300     John

产品:

id  name        description     price   category_id     customer_id
-------------------------------------------------------------------
1   product1    lorem ipsum     150     10              100
2   product2    lorem ipsum     100     10              100
3   product3    lorem ipsum     300     20              200

类别:

id  name
---------
10  bags
20  toys
30  phones

期望的结果:

customerID  customerLastName    bags    toys    phones
100         Terry               250     0       0
200         Molly               0       300     0
300         John                0       0       0

如您所见，所需结果是按类别划分的产品价格总和。所以对于第一个客户，他只有与类别 bags 相关的产品，所以我们应该有一个 bags 列，其中包含产品价格的总和(product1 为 100和 product2 150，总共 250)。

如果客户在某个类别中没有任何产品，则该类别列中的值应为 0。

我已经试过了:

select 
customers.id as customerID, 
customers.last_name as customerLastName, 
SUM(CASE WHEN products.category_id = 10 THEN 1 ELSE NULL END) AS 'bags',
SUM(CASE WHEN products.category_id = 20 THEN 1 ELSE NULL END) AS 'toys',
SUM(CASE WHEN products.category_id = 30 THEN 1 ELSE NULL END) AS 'phones'
from customers
left join products on products.customer_id = customers.id
left join categories on products.category_id = categories.id
group by customers.id

但我得到了这个结果:

customerID  customerLastName    bags    toys    phones
100         Terry               2       0       0
200         Molly               0       1       0
300         John                0       0       0

Answer 1

将 THEN 1 更改为 THEN products.price 以聚合 price 值。此外，如果您未指定 ELSE NULL，则默认情况下仅为 NULL。

select 
  customers.id as customerID, 
  customers.last_name as customerLastName, 
  SUM(CASE WHEN products.category_id = 10 THEN products.price END) AS 'bags',
  SUM(CASE WHEN products.category_id = 20 THEN products.price END) AS 'toys',
  SUM(CASE WHEN products.category_id = 30 THEN products.price END) AS 'phones'
from customers
left join products on products.customer_id = customers.id
left join categories on products.category_id = categories.id
group by customers.id