时间戳

首页 > 解决方案 > 选择不同行上具有 2 个属性的已售产品数量

sql - 选择不同行上具有 2 个属性的已售产品数量

问题描述

我正在尝试生成 SKUABC 销售的每种产品的报告，尺寸为 34，内缝为 33（内缝为 33 和 31）。表 - orders_products

表 - 订单：

+-----------+------------------------+--+
| Orders_id |     date_purchased     |  |
+-----------+------------------------+--+
|    46198  | 2020-10-18 19:43:25    |  |
|     46199 | 2020-10-19 19:43:25    |  |
|     46200 | 2020-10-22 19:43:25    |  |
+-----------+------------------------+--+

表 - orders_products

+--------------------+-----------+-------------+----------------+--+
| orders_products_id | Orders_id | products_id | products_mode | QTY
+--------------------+-----------+-------------+----------------+--+
|           42154907 |     46198 |         878 | SKUABC         |1  |
|           42154908 |     46198 |         878 | SKUABC         |1  |
|           42154909 |     46198 |         282 | DIFFSKU        |1  |
+--------------------+-----------+-------------+----------------+--+

表 - Orders_products_attributes（仅显示 order_id 46198）：

+------------------------------+-----------+--------------------+-----------------+-----------------------+--+
| orders_products_attribute_id | orders_id | orders_products_id | Product options | Product_options_value |  |
+------------------------------+-----------+--------------------+-----------------+-----------------------+--+
|                      167618  |     46198 |           42155189 | Color           | Green                 |  |
|                       167619 |     46198 |           42155189 | Inseam          | 33                    |  |
|                       167620 |     46198 |           42155189 | Size            | 34                    |  |
+------------------------------+-----------+--------------------+-----------------+-----------------------+--+

到目前为止我的sql：

SELECT  distinct o.orders_id, op.products_model, opa.products_options_values, sum(op.products_quantity)
FROM orders o  
LEFT JOIN orders_products op 
    ON o.orders_id = op.orders_id
LEFT JOIN orders_products_attributes opa
    on op.orders_id = opa.orders_id
WHERE op.products_model in ('SKUABC')
and  opa.`products_options_values` in ('36')
and o.date_purchased > '2020-10-13'

如果我添加：

and  opa.`products_options_values` in ('31')

它不返回任何结果，原因是因为 inseam 和 size 行是分开的。上面代码的问题在于它结合了任何订单/订购的产品，其中内接缝为 33 或 31，但我希望它是分开的。

我想要的结果是

+--------+------------+------------+-------------------+
| model  | attribute1 | attribute2 | quantity sold sum |
+--------+------------+------------+-------------------+
| ABCSKU |         34 |         33 |               120 |
+--------+------------+------------+-------------------+

标签： sql

解决方案

这是一个有趣的解决方案：选择两个 products_options_values 并用不同的名称标记它们，那么一切都会很容易

SELECT  distinct o.orders_id, op.products_model, opa.products_options_values 
AS Inseem,opa2.products_options_values AS Size, sum(op.products_quantity)
FROM orders o  
LEFT JOIN orders_products op 
ON o.orders_id = op.orders_id
LEFT JOIN orders_products_attributes opa
ON op.orders_id = opa.orders_id
LEFT JOIN orders_products_attributes opa2
ON op.orders_id = opa.orders_id
--your condition below

然后只需将 opa 用于 inseem 和 opa2 用于大小。这很愚蠢但可以工作。您甚至可以通过使用 Product 选项列添加一些条件来使行数据为空，以便以后插入。

推荐阅读