php - MySQL 透视将行变成列

Question

我有三个表，产品、客户、订单

产品:

id | name |
 1 | milk |
 2 | bread|
 3 | Pea  |

客户:

id | name  | category
1  | James | retailer
2  | Paul  | vendor
3  | Dave  | retailer

订单:

id  | product_id | customer_id | qty | price
 1  | 1          | 2           | 23  | 50 
 2  | 2          | 2           | 4   | 30
 3  | 3          | 2           | 6   | 10
 4  | 2          | 1           | 9   | 30
 5  | 3          | 1           | 2   | 10
 6  | 1          | 3           | 6   | 50
 7  | 3          | 3           | 7   | 10

当我执行查询以显示 供应商 类别的客户的交易时，例如

SELECT customer.name, product.name as pname, order.qty, order.price FROM customer, product, order 
WHERE customer.id = order.customer_id 
AND product.id = order.product_id AND customer.category = "vendor"

我会得到类似的东西:

name |  pname | qty | price
Paul |  milk  | 23  | 50
Paul |  bread | 4   | 30
Paul |  pea   | 6   | 10

我想要这个:

name | milk  | bread  | pea  | total
Paul | 23    | 4      | 6    | 90

零售商的情况如下:

SELECT customer.name, product.name as pname, order.qty, order.price FROM 
customer, product, order 
WHERE customer.id = order.customer_id 
AND product.id = order.product_id AND customer.category = "retailer"

我会得到一个像这样的表格:

 name |  pname | qty | price
James |  bread | 9   | 30
James |  pea   | 2   | 10
 Dave |  milk  | 6   | 50
 Dave |  pea   | 7   | 10

但我想要这个:

name  | milk  | bread  | pea  | total
James | 0     |  9     | 2    | 40
Dave  | 6     |  0     | 7    | 60

Answer 1

只需使用条件聚合来旋转列。请务必使用显式连接，而不是已弃用的隐式连接，因为前者已成为 ANSI-92 25 年来的标准。

SELECT c.name,  
       SUM(CASE WHEN p.name = 'milk' THEN o.qty ELSE 0 END) as milk,
       SUM(CASE WHEN p.name = 'bread' THEN o.qty ELSE 0 END) as bread,
       SUM(CASE WHEN p.name = 'pea' THEN o.qty ELSE 0 END) as pea,
       SUM(o.price) AS Total 
FROM `customer` c
INNER JOIN `order` o
  ON c.id = o.customer_id 
INNER JOIN `product` p
  ON p.id = o.product_id 
WHERE c.category = 'vendor'   -- same for retailer
GROUP BY c.name