mysql - 如何使用 SQL 从单独的表中检索 'postcode'

Question

目标

从 Magento 1.9 的 sales_flat_order_address 表中提取订单详细信息，并包含来自 sales_flat_order_address 表的订单邮政编码

到目前为止我的查询(有效)

SELECT `increment_id` AS 'Order Number', SUM(ROUND(`base_grand_total`,2)) AS 'Total Value', created_at AS 'Order Date', customer_firstname AS 'First Name', customer_lastname AS 'Surname'
FROM `sales_flat_order`
WHERE `shipping_description` LIKE '%next day%'
AND `created_at` BETWEEN '2015-11-01' AND '2015-11-05'
GROUP BY `increment_id`
ORDER BY `created_at` DESC

迄今为止的结果

订单号 |总值(value)|订购日期 |名字 |姓氏

问题

如您所见，我可以从 sales_flat_order 中提取数据，但很难从 sales_flat_order_address 中提取数据。

解决方案

以下查询返回了我的结果，但我觉得效率不高。如果有更有效的方法，请告诉我。谢谢

SELECT `increment_id` AS 'Order Number', SUM(ROUND(`base_grand_total`,2)) AS 'Total Value', created_at AS 'Order Date', customer_firstname AS 'First Name', customer_lastname AS 'Surname', `postcode` AS 'Postcode'
FROM `sales_flat_order`, `sales_flat_order_address`
WHERE `shipping_description` LIKE '%next day%'
AND sales_flat_order.billing_address_id = sales_flat_order_address.entity_id
AND `created_at` BETWEEN '2015-11-01' AND '2015-11-05'
GROUP BY `increment_id`
ORDER BY `created_at` DESC

Answer 1

现在首选的语法是使用 JOIN .... ON，但据我所知，效率上没有差异。

SELECT `increment_id` AS 'Order Number', SUM(ROUND(`base_grand_total`,2)) AS 'Total Value', created_at AS 'Order Date', customer_firstname AS 'First Name', customer_lastname AS 'Surname', `postcode` AS 'Postcode'
FROM `sales_flat_order`
JOIN `sales_flat_order_address` ON sales_flat_order.billing_address_id = sales_flat_order_address.entity_id
WHERE `shipping_description` LIKE '%next day%'
AND `created_at` BETWEEN '2015-11-01' AND '2015-11-05'
GROUP BY `increment_id`
ORDER BY `created_at` DESC