Magento系统：导出完整的Order数据

公司做了个ERP系统，要求要把Magento数据库的Order数据导出excel数据，然后再导入到ERP中。但是Magento的数据库表很散，所以要导出完整的Order数据，要使用多表连接查询。

 首先：修改数据库

对数据库表 `directory_country`添加了一列`country_name`，这列允许行为空！再把对应的国家名字添加上去。

country_name	varchar(100)	utf8_general_ci		是	NULL

这里附上 `directory_country`表的导入数据，大家可以下载来直接导入到这张表里！

各国简称对应国家名字大全：

http://download.csdn.net/detail/huangqiaoling/4922211

以下是具体SQL:

 

+++++++++++++++++++   除了配件信息其他数据都完整 ++++++++++++++++++++

SELECT
OD.`increment_id` as Order_ID,
CONCAT(OAD.`firstname`,' ',OAD.`lastname`)as Bill_Name,
CONCAT(OAD.`company`,'\n',OAD.`street`,',',OAD.`city`,',',OAD.`region`,',',OAD.`postcode`) as Billing_Address,
CONCAT('F: ',OAD.`fax`,'\n','T: ',OAD.`telephone`) as Bill_Fax_Telephone,
CONCAT(OAD1.`firstname`,' ',OAD1.`lastname`)as Shipping_Name,
CONCAT(OAD1.`company`,'\n',OAD1.`street`,',',OAD1.`city`,',',OAD1.`region`,',',OAD1.`postcode`) as Shipping_Address,
CONCAT('F: ',OAD1.`fax`,'\n ','T: ',OAD1.`telephone`) as Shipping_Fax_Telephone,
CO.`country_name` as Country,
OD.`customer_email` as Email,
TRUNCATE(PRO.`base_row_total`/PRO.`base_price`,0) as Qty,
OD.`shipping_method` as Shipping_Method,
TRUNCATE(PRO.`base_price`,2) as Price,
TRUNCATE(OD.`base_shipping_amount`,2) as Shipping_Price,
TRUNCATE(PRO.`base_row_total`,2) as Total_Price,
OD.`status` as Order_Status,
OD.`state`  as Order_Status2,
OD.`shipping_description`  as Shipping_Description,
OD.`created_at` as Create_Time,
OD.`updated_at` as Update_Time,
PRO.`sku` as SKU,
PRO.`name` as Product_Title,
M.`message`  as Message
FROM  `sales_flat_order` as OD
LEFT JOIN  `sales_flat_order_item` as PRO
ON OD.`entity_id`=PRO.`order_id`
LEFT JOIN `sales_flat_order_address` as OAD
ON OD.`billing_address_id`=OAD.`entity_id`
LEFT JOIN `sales_flat_order_address` as OAD1
ON OD.`shipping_address_id`=OAD1.`entity_id`
LEFT JOIN `directory_country` as CO
ON OAD.`country_id`=CO.`country_id`
LEFT JOIN `gift_message` as M
ON OD.`gift_message_id`=M.`gift_message_id`
group by PRO.`item_id`

  最后一行：group by PRO.`item_id`很重要，group by排除重复数据，这样结果集才会正确！