SQL 声明选择计算作为变量，以便在查询中进一步使用

Question

我想将选择查询中的特定计算更改为变量，以便重新处理查询内其他计算的结果。在这个例子中，我需要作为变量的值是 dt_raw 。到目前为止，我还没有成功实施解决方案。

SELECT
cpe.entity_id AS product_id,
cpe.sku,
CASE
    WHEN (csi.qty - csi.stock_ordered_qty) >= cped2.value AND csi.stock_id = 3  /* on stock burgsdorgstrasse */
        THEN 3
    WHEN (csi.qty - csi.stock_ordered_qty) >= cped2.value AND csi.stock_id = 4  /* on stock hoppegarten */
        THEN 7
    WHEN sum(sm_qty) > 2 AND csi.stock_id = 3                       /* history burgsdorfstrasse */
        THEN round(avg(DATEDIFF(sm.sm_date, po.po_date)) + 3)
    WHEN sum(sm_qty) > 2 AND csi.stock_id = 4                       /* history hoppegarten */
        THEN round(avg(DATEDIFF(sm.sm_date, po.po_date)) + 4)
        ELSE cped.value                                             /* default_delivery_time */
    END AS dt_raw

FROM `test_live`.`catalog_product_entity` cpe
LEFT JOIN `test_live`.`cataloginventory_stock_item` csi
ON cpe.entity_id = csi.product_id
LEFT JOIN `test_live`.`catalog_product_entity_decimal` cped     /* default_delivery_time */
ON cpe.entity_id = cped.entity_id
AND cped.attribute_id = 392
LEFT JOIN `test_live`.`catalog_product_entity_decimal` cped2    /* min_qty_delivery_time */
ON cpe.entity_id = cped2.entity_id
AND cped2.attribute_id = 393
LEFT JOIN `test_live`.`stock_movement` sm
ON cpe.entity_id = sm.sm_product_id
AND sm.sm_type = "supply"
AND sm.sm_date > NOW() - Interval 90 DAY
LEFT JOIN `test_live`.`purchase_order` po
ON po.po_num = sm.sm_po_num

WHERE
csi.is_favorite_warehouse = 1
AND (csi.stock_id = 3 OR csi.stock_id = 4)
GROUP BY cpe.entity_id

我想在进一步的计算中使用dt_raw的结果。

例如：

concat ("ca. ", round(dt_raw), " weeks") as delivery_time
CASE
    WHEN dt_raw <= 30
    THEN 50
    ELSE 0
END AS amazon_qty

Answer 1

如果你想使用你的查询结果而不使用restiction，你应该把它保存在一个表中。之后，使用选择获取所需的数据。

创造

CREATE TABLE your_schema.dt_raw_temp
SELECT
cpe.entity_id AS product_id,
cpe.sku,
CASE
    WHEN (csi.qty - csi.stock_ordered_qty) >= cped2.value AND csi.stock_id = 3  /* on stock burgsdorgstrasse */
        THEN 3
    WHEN (csi.qty - csi.stock_ordered_qty) >= cped2.value AND csi.stock_id = 4  /* on stock hoppegarten */
        THEN 7
    WHEN sum(sm_qty) > 2 AND csi.stock_id = 3                       /* history burgsdorfstrasse */
        THEN round(avg(DATEDIFF(sm.sm_date, po.po_date)) + 3)
    WHEN sum(sm_qty) > 2 AND csi.stock_id = 4                       /* history hoppegarten */
        THEN round(avg(DATEDIFF(sm.sm_date, po.po_date)) + 4)
        ELSE cped.value                                             /* default_delivery_time */
    END AS dt_raw

FROM `test_live`.`catalog_product_entity` cpe
LEFT JOIN `test_live`.`cataloginventory_stock_item` csi
ON cpe.entity_id = csi.product_id
LEFT JOIN `test_live`.`catalog_product_entity_decimal` cped     /* default_delivery_time */
ON cpe.entity_id = cped.entity_id
AND cped.attribute_id = 392
LEFT JOIN `test_live`.`catalog_product_entity_decimal` cped2    /* min_qty_delivery_time */
ON cpe.entity_id = cped2.entity_id
AND cped2.attribute_id = 393
LEFT JOIN `test_live`.`stock_movement` sm
ON cpe.entity_id = sm.sm_product_id
AND sm.sm_type = "supply"
AND sm.sm_date > NOW() - Interval 90 DAY
LEFT JOIN `test_live`.`purchase_order` po
ON po.po_num = sm.sm_po_num

WHERE
csi.is_favorite_warehouse = 1
AND (csi.stock_id = 3 OR csi.stock_id = 4)
GROUP BY cpe.entity_id;

选择

SELECT CONCAT('ca. ', round(dt_raw), ' weeks') AS delivery_time, 
       IF(dt_raw <= 30, 50, 0) AS amazon_qty
FROM your_schema.dt_raw_temp;

使用表格的优点是您可以重现您的 只要您保留表格，就可以在任何给定时刻进行计算。

Answer 2

只需从查询中选择：

select
  product_id,
  sku,
  dt_raw,
  concat ("ca. ", round(dt_raw), " weeks") as delivery_time,
  case when dt_raw <= 30 then 50 else 0 end as amazon_qty
from
(
  SELECT
    cpe.entity_id AS product_id,
    cpe.sku,
    CASE
      WHEN (csi.qty - csi.stock_ordered_qty) >= cped2.value AND csi.stock_id = 3  /* on stock burgsdorgstrasse */
        THEN 3
      WHEN (csi.qty - csi.stock_ordered_qty) >= cped2.value AND csi.stock_id = 4  /* on stock hoppegarten */
        THEN 7
      WHEN sum(sm.sm_qty) > 2 AND csi.stock_id = 3                                /* history burgsdorfstrasse */
        THEN round(avg(DATEDIFF(sm.sm_date, po.po_date)) + 3)
      WHEN sum(sm.sm_qty) > 2 AND csi.stock_id = 4                                /* history hoppegarten */
        THEN round(avg(DATEDIFF(sm.sm_date, po.po_date)) + 4)
        ELSE cped.value                                                           /* default_delivery_time */
    END AS dt_raw
  FROM test_live.catalog_product_entity cpe
  LEFT JOIN test_live.cataloginventory_stock_item csi ON cpe.entity_id = csi.product_id AND csi.is_favorite_warehouse = 1 AND csi.stock_id in (3,4)
  LEFT JOIN test_live.catalog_product_entity_decimal cped     /* default_delivery_time */ ON cpe.entity_id = cped.entity_id AND cped.attribute_id = 392
  LEFT JOIN test_live.catalog_product_entity_decimal cped2    /* min_qty_delivery_time */ ON cpe.entity_id = cped2.entity_id AND cped2.attribute_id = 393
  LEFT JOIN test_live.stock_movement sm ON cpe.entity_id = sm.sm_product_id AND sm.sm_type = 'supply' AND sm.sm_date > NOW() - Interval 90 DAY
  LEFT JOIN test_live.purchase_order po ON po.po_num = sm.sm_po_num
  GROUP BY cpe.entity_id
) prod;