MYSQL:将产品分成列 [英] MYSQL: Separate products into columns

问题描述

这与我之前的帖子 MYSQL:获取数量有关每个订单中的产品.

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

Using the query below, it generates a table of:

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 设置额外的列而不是生成与下面相同的输出的行?

As seen above, each new product_id is assigned its own row. Is it possible to have additional columns for each product_id instead of rows generating an output same as the one below?

_______________________________________________________________________
| 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 |
-----------------------------------------------------------------------

我的桌子:

-- 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 |
-------------------------------------------

推荐答案

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

If you know the products in advance, you can use conditional aggregation:

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() 来创建一个包含您想要的信息的字符串.

If you don't know the product ids, then you would have to use dynamic SQL, or perhaps use GROUP_CONCAT() to create a string with the information you want.

这篇关于MYSQL:将产品分成列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！