Magento的订单相关的SQL（订单编号、创建时间、总金额、收货人、收货人地址、收货人城市、州或省(简称)、收货邮编、收货人电话、付款人邮箱）

最新推荐文章于 2021-10-26 16:46:30 发布

原创最新推荐文章于 2021-10-26 16:46:30 发布 · 4.2k 阅读

0 ·

CC 4.0 BY-SA版权

Magento 专栏收录该内容

133 篇文章

订阅专栏

本文深入解析了用于获取电商订单详细信息的SQL查询语句，包括订单编号、创建时间、总金额、收货人信息等关键字段。通过左连接操作，实现了订单与收货地址、付款人邮箱的关联，确保数据的一致性和完整性。

/*
订单编号、创建时间、总金额、收货人、收货人地址、收货人城市、州或省(简称)、收货邮编、收货人电话、付款人邮箱
*/
SELECT `main_table`.increment_id, `main_table`.created_at,`main_table`.base_grand_total,`main_table`.shipping_name,
CONCAT_WS(", ",sales_flat_order_address.street,sales_flat_order_address.city,sales_flat_order_address.region) AS `ShippingAddress`,
 sales_flat_order_address.city, directory_country_region.code,sales_flat_order_address.postcode,sales_flat_order_address.telephone,
 `customer_entity`.`email` AS `customer_email`
FROM `sales_flat_order_grid` AS `main_table`
LEFT JOIN `sales_flat_order_address` ON main_table.entity_id = sales_flat_order_address.parent_id
LEFT JOIN `customer_entity` ON main_table.customer_id = customer_entity.entity_id
LEft JOIN `directory_country_region` ON directory_country_region.default_name=sales_flat_order_address.region
WHERE (sales_flat_order_address.address_type="shipping")