Magento 1.5版本,导出order详细订单的SQL语句
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时会自动消失的问题