MySQL:如何将三个表组合成矩阵

Question

我有以下三个SQL表：

+-------------------------+
|          SHOP           |
+---------+---------------+
| shop_id |  shop_label   |
+---------+---------------+
|       1 | Shop Paris    |
|       2 | Shop Madrid   |
|       3 | Shop New York |
|       4 | Shop Tokyo    |
+---------+---------------+

+----------------------------+
|           PRODUCT          |
+------------+---------------+
| product_id | product_label |
+------------+---------------+
|          1 | Pen           |
|          2 | Workbook      |
|          3 | Smartphone    |
|          4 | Computer      |
|          5 | Chair         |
+------------+---------------+

+-------------------------------------------------------+
|                      COMMAND LINE                     |
+---------+------------+----------+---------------------+
| fk_shop | fk_product | quantity |        date         |
+---------+------------+----------+---------------------+
|       1 |          1 |       10 | 2021-10-20 12:10:59 |
|       4 |          3 |        1 | 2021-10-23 12:11:07 |
|       2 |          2 |        3 | 2021-10-29 12:12:07 |
|       1 |          2 |        8 | 2021-10-30 12:12:37 |
|       1 |          1 |        5 | 2021-11-03 13:10:07 |
+---------+------------+----------+---------------------+

现在，我正试图制作一个矩阵，将所有产品作为COLUMNS，将所有商店作为ROWS，显示购买了多少产品。我想做一个查询来检索这个结果：

+------------+------------+-------------+---------------+------------+
|            | Shop Paris | Shop Madrid | Shop New York | Shop Tokyo |
+------------+------------+-------------+---------------+------------+
| Pen        |         15 |           8 |               |            |
| Workbook   |            |           3 |               |            |
| Smartphone |            |             |               |          1 |
| Computer   |            |             |               |            |
| Chair      |            |             |               |            |
+------------+------------+-------------+---------------+------------+

你知道这样做的方法吗？很久以前我看到一个用with运算符创建矩阵的查询，但我记不太清楚了。。。

谢谢你的帮助。

Answer 1

在oracle或sqlserver中有PIVOT((可以使用，但在MYSQL中没有。

所以可以这样做：

select  p.product_label ,
sum(case when s.shop_id = 1 then  cl.quantity else 0 end) Shop_Paris ,
sum(case when s.shop_id = 2 then  cl.quantity else 0 end) Shop_madrid ,
sum(case when s.shop_id = 3 then  cl.quantity else 0 end) Shop_newyork ,
sum(case when s.shop_id = 4 then  cl.quantity else 0 end) Shop_tokyo 
from command_line cl 
inner join product p on p.product_id =  cl.fk_product 
inner join shop s on s.shop_id = cl.fk_shop 
group by p.product_label