Magento SQL查询帮助进行大规模查询

问题描述

我正在尝试为Magento修改以下MySQL查询.现在要做的是为报告返回简单和可配置产品的产品信息.不幸的是，简单产品并没有父级可配置产品所具有的全部信息.

I'm trying to modify the following MySQL query for Magento. What it does now is returns product information for simple and configurable products for a report.Unfortunately, the simple products don't have all the info that the parent configurable products have.

我想要做的是使用表catalog_product_super_link查找每个产品的父产品ID，以便对于所有产品，我都可以获取父可配置产品的数据.超级链接表的一列中包含所有产品ID的值，另一列中包含对应的父级的值.对于可配置产品，产品ID和父ID相同.

What I'd like it to do is use the table catalog_product_super_link to look up the parent product id of every product so that for all products, I'm getting the data of the parent configurable product. The super link table has the values for all product id's in one column, and the corresponding parent in another. For configurable products, the product id and parent id are the same.

有道理吗?

我得到的查询是:

SELECT IFNULL(category_name.value, category_default_name.value) AS `primarycategory`
     , `product`.`sku`
     , IFNULL(product_name.value, product_default_name.value) AS `product_name`
     , `attribute_option_value`.`value` AS `story`
     , (
         select GROUP_CONCAT(category_name.value)
         from catalog_category_entity as category
            , catalog_category_entity_varchar as category_name
            , catalog_category_product as category_product
         WHERE category_name.entity_id = category.entity_id
           AND category_name.store_id = 0
           AND category_name.entity_type_id = 3
           AND category_name.attribute_id = 33
           AND category_product.product_id = order_item.product_id
           AND category_product.category_id = category.entity_id
           AND category_name.value<>'Default Category'
       ) AS `category`
     , round(sum(order_item.qty_ordered)) AS `unitssoldnum`
     , ROUND(sum(order_item.price-order_item.discount_amount-order_item.tax_amount)
           , 2
       ) AS `unitssoldcur`
     , ROUND((
               SUM(order_item.price-order_item.discount_amount-order_item.tax_amount)
               - (IFNULL(product_cost.value, product_default_cost.value) * SUM(order_item.qty_ordered))
             )
           , 2
       ) AS `grossmargincur`
     , ROUND((
               (
                 SUM(order_item.price-order_item.discount_amount-order_item.tax_amount)
                 - (IFNULL(product_cost.value, product_default_cost.value) * SUM(order_item.qty_ordered))
               )
               / (SUM(order_item.price-order_item.discount_amount-order_item.tax_amount))
             )
             * 100
           , 2
       ) AS `grossmarginpercent`
     , (
         SELECT round(SUM(qty))
         FROM cataloginventory_stock_item AS stock
            , catalog_product_super_link AS product_link
         WHERE product_link.product_id = stock.product_id
           AND product_link.parent_id=order_item.product_id
       ) AS `sohatstore`
     , (
         SELECT round(SUM(qty)/SUM(order_item.qty_ordered))
         FROM cataloginventory_stock_item AS stock
            , catalog_product_super_link AS product_link
         WHERE product_link.product_id = stock.product_id
           AND product_link.parent_id=order_item.product_id
       ) AS `storecover`
     , (
         SELECT round(SUM(order_item.qty_ordered)
                      /(SUM(qty)+SUM(order_item.qty_ordered))
                      *100
                    , 2
                )
         FROM cataloginventory_stock_item AS stock
            , catalog_product_super_link AS product_link
         WHERE product_link.product_id = stock.product_id
           AND product_link.parent_id=order_item.product_id
       ) AS `sellthrupercent`
     , ROUND(SUM(order_item.price),2) AS `originalretail`
     , ROUND(SUM(IFNULL(product_cost.value, product_default_cost.value))
           , 2
       ) AS `costextax`
     , (
         SELECT round(SUM(qty_ordered))
         FROM sales_flat_order_item AS order_total
         WHERE order_total.product_id = order_item.product_id
       ) AS `totalsoldtilldate`
     , (
         SELECT ROUND((
                        SELECT round(SUM(qty_ordered))
                        FROM sales_flat_order_item AS order_total
                        WHERE order_total.product_id = order_item.product_id
                      )
                      /(
                        (
                          SELECT round(SUM(qty_ordered))
                          FROM sales_flat_order_item AS order_total
                          WHERE order_total.product_id = order_item.product_id
                        )
                        +(
                          SELECT round(SUM(qty))
                          FROM cataloginventory_stock_item AS stock
                             , catalog_product_super_link AS product_link
                          WHERE product_link.product_id = stock.product_id
                            AND product_link.parent_id=order_item.product_id
                        )
                      )*100
                    , 2
                )
       ) AS `totalsellthru`
     , (
         SELECT ROUND((
                        (
                          sum(order_total.price-order_total.discount_amount-order_total.tax_amount)
                          - sum(order_total.base_cost * order_total.qty_ordered)
                        )
                        /sum(order_total.price-order_total.discount_amount-order_total.tax_amount)
                      )*100
                    , 2
                )
         FROM sales_flat_order_item AS order_total
         WHERE order_total.product_id = order_item.product_id
       ) AS `totalgrossmarginpercent`
FROM `sales_flat_order` AS `order`
 INNER JOIN `sales_flat_order_item` AS `order_item` ON order_item.order_id = order.entity_id
 INNER JOIN `catalog_product_entity` AS `product`
    ON product.entity_id = order_item.product_id
   AND product.entity_type_id = 4
   AND product.type_id NOT IN('grouped', 'bundle')
 LEFT JOIN `catalog_product_entity_varchar` AS `product_name`
    ON product_name.entity_id = product.entity_id
   AND product_name.store_id = order.store_id
   AND product_name.entity_type_id = 4
   AND product_name.attribute_id = 60
 LEFT JOIN `catalog_product_entity_varchar` AS `product_default_name`
    ON product_default_name.entity_id = product.entity_id
   AND product_default_name.store_id = 0
   AND product_default_name.entity_type_id = 4
   AND product_default_name.attribute_id = 60
 LEFT JOIN `catalog_product_entity_decimal` AS `product_cost`
    ON product_cost.entity_id = product.entity_id
   AND product_cost.store_id = order.store_id
   AND product_cost.entity_type_id = 4
   AND product_cost.attribute_id = 68
 LEFT JOIN `catalog_product_entity_decimal` AS `product_default_cost`
    ON product_default_cost.entity_id = product.entity_id
   AND product_default_cost.store_id = 0
   AND product_default_cost.entity_type_id = 4
   AND product_default_cost.attribute_id = 68
 LEFT JOIN `catalog_category_entity` AS `category`
    ON category.entity_id =  (select MAX(category_id) from catalog_category_product where product_id = product.entity_id)
   AND category.entity_type_id = 3
 LEFT JOIN `catalog_category_entity_varchar` AS `category_name`
    ON category_name.entity_id = category.parent_id
   AND category_name.store_id = order.store_id
   AND category_name.entity_type_id = 3
   AND category_name.attribute_id = 33
 LEFT JOIN `catalog_category_entity_varchar` AS `category_default_name`
    ON category_default_name.entity_id = category.parent_id
   AND category_default_name.store_id = 0
   AND category_default_name.entity_type_id = 3
   AND category_default_name.attribute_id = 33
 LEFT JOIN `catalog_product_entity_int` AS `story_name`
    ON story_name.entity_id = product.entity_id
   AND story_name.store_id = 1
   AND story_name.entity_type_id = 4
   AND story_name.attribute_id = 138
 LEFT JOIN `catalog_product_entity_int` AS `story_default_name`
    ON story_default_name.entity_id = product.entity_id
   AND story_default_name.store_id = 0
   AND story_default_name.entity_type_id = 4
   AND story_default_name.attribute_id = 138
 LEFT JOIN `eav_attribute_option` AS `attribute_option`
    ON attribute_option.option_id = IFNULL(story_name.value, story_default_name.value)
 LEFT JOIN `eav_attribute_option_value` AS `attribute_option_value`
    ON attribute_option_value.option_id = attribute_option.option_id
   AND attribute_option_value.store_id = 0
WHERE (order.state <> 'canceled')
  AND (DATE(order.created_at) <= DATE('2012-07-03'))
  AND (DATE(order.created_at) >= DATE('2012-07-01'))
GROUP BY `product_name`
ORDER BY `category_name`.`value` ASC
       , `category_default_name`.`value` ASC
       , `product_name`.`value` ASC
       , `product_default_name`.`value` ASC

如果有人能指出我正确的方向，将不胜感激:)

If anyone could point me in the right direction it would be greatly appreciated :)

以下是我为可配置产品获得的输出示例:

Here's a sample of the output I get for configurable products:

"Category Name",Product,Name,Story,"Category Description","Units Sold (#)","Units Sold ($)","GM ($)","GM (%)","SOH at Store","Store Cover","Sell through %","Original Retail Price","Cost Ex Tax","Total Units Sold (#)","Total Sell Thru %","GM% Total"
Bottoms,31875,"Denim Babe Jean",DENIM,"Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$59.84,$39.25,65.59,118,59,1.67,$68.00,$10.30,81,40.70,69.38
Bottoms,34475,"Diamante Rip Boot Leg Jean",DENIM,"Bottoms,SALE,Denim,Jeans,Bootleg Jean,Pants",2,$29.92,-$7.56,-25.25,28,14,6.67,$34.00,$18.74,5,15.15,19.90
Bottoms,35853,"Embroidered Back Wide Leg Jean",DENIM,"Bottoms,SALE,Denim,Jeans,Wideleg Jean,Pants",2,$29.92,-$9.08,-30.35,11,6,15.38,$34.00,$19.50,22,66.67,23.93
Bottoms,34278,"Firecracker Bootleg Jean",DENIM,"Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$59.84,$29.92,50.00,9,5,18.18,$68.00,$14.96,39,81.25,43.99
Bottoms,28450,"Glamourzon Jean","CHIC","Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$59.84,$59.84,100.00,101,51,1.94,$68.00,$0.00,51,33.55,100.00

这是我从简单产品中得到的样本.注意所有丢失的数据.这就是为什么我要在报告中查找父产品ID并使用其数据的原因.

And here's a sample of what I get with simple products. Notice all the missing data. That's why for my report I want to look up the parent product id and use it's data.

"Category Name",Product,Name,Story,"Category Description","Units Sold (#)","Units Sold ($)","GM ($)","GM (%)","SOH at Store","Store Cover","Sell through %","Original Retail Price","Cost Ex Tax","Total Units Sold (#)","Total Sell Thru %","GM% Total"
Bottoms,2000004614430,"Denim Babe Jean - Dark Denim - 24",DENIM,"Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$0.00,-$20.59,,,,,$0.00,$10.30,15,,
Bottoms,2000004901523,"Diamante Rip Boot Leg Jean - Black - 14",DENIM,"Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$0.00,-$37.48,,,,,$0.00,$18.74,2,,
Bottoms,2000004883881,"Firecracker Bootleg Jean - Denim Dark - 16",DENIM,"Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$0.00,-$29.92,,,,,$0.00,$14.96,9,,
Bottoms,2000004159955,"Glamourzon Jean - Dark Denim - 24","CHIC","Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$0.00,$0.00,,,,,$0.00,$0.00,11,,
Bottoms,2005010849432,"Glamourzon Night Jean - Black - 28","CHIC","Bottoms,Denim,Jeans,Bootleg Jean,Pants",2,$0.00,-$32.80,,,,,$0.00,$16.40,7,,-68.82

我希望能回答您的问题.谢谢！

I hope that answered your question. Thanks!

推荐答案

我们正在做一些类似的报告.您需要做什么:

we are doing some similar reports. What you need to do:

• 从简单的产品开始
• 在可配置产品上的catalog_product_super_link表上加入
• 在查询的SELECT部分​​中，如果简单产品的值在那里，则使用IF =>，否则，请使用IF =>(否则，请使用可配置产品的值)

示例

SELECT
    o.increment_id,
    oi.order_id,
    o.STATUS AS order_status,
    o.created_at,
    ROUND(SUM(IF(oi2.qty_ordered IS NOT NULL, IF(oi2.qty_ordered > oi.qty_ordered, oi2.qty_ordered, oi.qty_ordered), oi.qty_ordered)), 0) AS cqty_ordered,
    ROUND(SUM(IF(oi2.qty_canceled IS NOT NULL, IF(oi2.qty_canceled > oi.qty_canceled, oi2.qty_canceled, oi.qty_canceled), oi.qty_canceled)), 0) AS cqty_canceled,
    ROUND(SUM(IF(oi2.qty_shipped IS NOT NULL, IF(oi2.qty_shipped > oi.qty_shipped, oi2.qty_shipped, oi.qty_shipped), oi.qty_shipped)), 0) AS cqty_shipped,
    ROUND(SUM(IF(oi2.qty_ordered IS NOT NULL, IF(oi2.qty_ordered > oi.qty_ordered, oi2.qty_ordered, oi.qty_ordered), oi.qty_ordered)) - SUM(IF(oi2.qty_canceled IS NOT NULL, IF(oi2.qty_canceled > oi.qty_canceled, oi2.qty_canceled, oi.qty_canceled), oi.qty_canceled)) - SUM(IF(oi2.qty_shipped IS NOT NULL, IF(oi2.qty_shipped > oi.qty_shipped, oi2.qty_shipped, oi.qty_shipped), oi.qty_shipped)), 0) AS cqty_missing
FROM sales_flat_order_item AS oi
INNER JOIN sales_flat_order AS o ON oi.order_id=o.entity_id
LEFT JOIN sales_flat_order_item AS oi2 ON oi.parent_item_id=oi2.item_id AND oi2.product_type='configurable'
WHERE oi.product_type='simple'
GROUP BY oi.order_id
HAVING cqty_missing=0 AND order_status NOT IN ('complete','canceled','closed');

这篇关于Magento SQL查询帮助进行大规模查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！