MYSQL:将产品分成几列

Question

这与我之前的帖子 MYSQL: Get quantity per product in each order 有关.

使用下面的查询，它会生成一个表:

SELECT o.order_id, o.username, op.product_id, SUM( op.quantity ) as quantity
FROM  `orders` o JOIN 
      orders_products op
      ON op.order_id = o.order_id
GROUP BY op.product_id, o.order_id
ORDER BY o.order_id, op.product_id;

_______________________________________________
| order_id | username | product_id | quantity |
-----------------------------------------------
|        1 | bill     |          1 |        5 |
-----------------------------------------------
|        1 | bill     |          2 |        3 |
-----------------------------------------------
|        1 | bill     |          3 |        2 |
-----------------------------------------------
|        2 | sally    |          1 |        2 |
-----------------------------------------------
|        3 | jeff     |          1 |        6 |
-----------------------------------------------
|        3 | jeff     |          2 |        7 |
-----------------------------------------------

如上所示，每个新的 product_id 都分配有自己的行。是否可以为每个 product_id 添加额外的列，而不是生成与下面相同的输出的行？

_______________________________________________________________________
| order_id | username | product_qty_1 | product_qty_2 | product_qty_3 |
-----------------------------------------------------------------------
|        1 | bill     |             5 |             3 |             2 |
-----------------------------------------------------------------------
|        2 | sally    |             2 |             0 |             0 |
-----------------------------------------------------------------------
|        3 | jeff     |             6 |             7 |             0 |
-----------------------------------------------------------------------

我的 table :

-- Orders
_______________________
| order_id | username |
-----------------------
|        1 | bill     |
-----------------------
|        2 | sally    |
-----------------------
|        3 | jeff     |
-----------------------

-- Products
___________________
|    id | product |
-------------------
|     1 | Table   |
-------------------
|     2 | Chair   |
-------------------
|     3 | Mouse   |
-------------------

-- Order Products
___________________________________________
|   id | order_id | product_id | quantity |
-------------------------------------------
|    1 |        1 |          1 |        5 |
-------------------------------------------
|    2 |        1 |          2 |        3 |
-------------------------------------------
|    3 |        1 |          3 |        2 |
-------------------------------------------
|    4 |        2 |          1 |        2 |
-------------------------------------------
|    5 |        3 |          1 |        6 |
-------------------------------------------
|    6 |        3 |          2 |        7 |
-------------------------------------------

Answer 1

如果你提前知道产品，你可以使用条件聚合:

SELECT o.order_id, o.username,
       SUM(CASE WHEN op.product_id = 1 THEN op.quantity ELSE 0 END) as quantity1,
       SUM(CASE WHEN op.product_id = 2 THEN op.quantity ELSE 0 END) as quantity2,
       SUM(CASE WHEN op.product_id = 3 THEN op.quantity ELSE 0 END) as quantity3
FROM  `orders` o JOIN 
      orders_products op
      ON op.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;

如果您不知道产品 ID，则必须使用动态 SQL，或者可能使用 GROUP_CONCAT() 创建包含所需信息的字符串。