欢迎来到Doc100.Net免费学习资源知识分享平台!
您的位置:首页 > 程序异常 >

magento 1.5版本,导出order详细订单的sql话语

更新时间: 2014-01-05 02:07:53 责任编辑: Author_N1

 

Magento 1.5版本,导出order详细订单的SQL语句
SELECT 
DATE_FORMAT(OD.`created_at`,'%Y-%m-%d') as Update_Time,
OAD1.`customer_id` as 客户编号,
CONCAT(OAD1.`firstname`,' ',OAD1.`lastname`) as Shipping_name,
OAD1.`telephone` as 联系电话,
OD.`customer_email` as Email,
if(SUBSTRING(OAD1.`postcode`,1,1)='0',CONCAT('Zip:0',SUBSTRING(OAD1.`postcode`,2)),OAD1.`postcode`) as 邮编,
OAD1.`street` as 街道,
OAD1.`city` as 城市, 
OAD1.`region` as 省,
OAD1.`company` as 公司,
CONCAT(OAD1.`street`,',',OAD1.`city`,',',OAD1.`region`,',',OAD1.`postcode`,',',OAD1.`company`) as 地址2,
CO.`country_name`  as 国家,
OD.`shipping_method` as 付款方式,
PRO.`sku` as 产品,
TRUNCATE(PRO.`base_row_total`/PRO.`base_price`,0) as 数量,
OD.`order_currency_code` as 货币,
TRUNCATE(PRO.`base_price`,2) as 销售单价,
TRUNCATE(PRO.`base_row_total`,2) as Total_Price,
TRUNCATE(OD.`base_shipping_amount`,2) as 运费,
OD.`increment_id` as 平台交易ID,
PRO.`product_id` as ItemNumber,
PRO.`name` as ItemTitle,
OD.`store_id`,
OM.`message` as 备注
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 OAD1
ON OD.`shipping_address_id`=OAD1.`entity_id`
LEFT JOIN `directory_country` as CO
ON OAD1.`country_id`=CO.`country_id`
LEFT JOIN `gift_message` as OM
ON OD.`gift_message_id`=OM.`gift_message_id`
LEFT JOIN `sales_flat_order_status_history` as OS
ON  OD.`entity_id`= OS.`parent_id` AND  OS.`status` = 'complete'
WHERE DATE_FORMAT(OD.`created_at`,'%Y-%m-%d')=current_date
OR  DATE_FORMAT(OD.`created_at`,'%Y-%m-%d')=DATE_FORMAT(adddate(now(),-1), '%Y-%m-%d')
OR  DATE_FORMAT(OD.`created_at`,'%Y-%m-%d')=DATE_FORMAT(adddate(now(),-2), '%Y-%m-%d')

group by PRO.`item_id`



有个if条件判断,解决了数据导出到excel里面时,Zip开头为0时会自动消失的问题

上一篇:上一篇
下一篇:下一篇

 

随机推荐程序问答结果

 

 

如对文章有任何疑问请提交到问题反馈,或者您对内容不满意,请您反馈给我们DOC100.NET论坛发贴求解。
DOC100.NET资源网,机器学习分类整理更新日期::2014-01-05 02:07:53
如需转载,请注明文章出处和来源网址:http://www.doc100.net/bugs/t/5989/
本文WWW.DOC100.NET DOC100.NET版权所有。