Magento 根据SKU查询订单信息的SQL语句

最新推荐文章于 2015-12-29 11:37:50 发布

原创最新推荐文章于 2015-12-29 11:37:50 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

Magento 专栏收录该内容

133 篇文章

订阅专栏

本文展示了如何通过SKU查询特定时间段内的订单信息，包括订单ID、创建时间、商品SKU、收货人姓名、送货地址等详细信息。

/*根据SKU,查询订单信息*/
 SELECT  
  order_table.increment_id,
item_table.created_at,item_table.sku,  
order_table.customer_email, CONCAT_WS('',order_table.customer_firstname,order_table.customer_middlename,order_table.customer_lastname) AS `BilltoName`, CONCAT_WS('',address_table.firstname,address_table.middlename,address_table.lastname) AS `ShiptoName`, CONCAT_WS(', ',address_table.street,address_table.city,address_table.region) AS `ShippingAddress`,  
 address_table.city,region_table.code,address_table.postcode,  
 address_table.telephone  
FROM sales_flat_order_item AS item_table  
LEFT JOIN sales_flat_order AS order_table ON order_table.entity_id=item_table.order_id  
LEFT JOIN sales_flat_order_address AS address_table ON address_table.parent_id=order_table.entity_id  
LEFT JOIN directory_country_region AS region_table ON region_table.default_name=`address_table`.region  
WHERE item_table.created_at >= '2015-06-01' AND address_table.address_type='shipping'  and item_table.sku='SP30191BK'