MySQL 查询导出 Magento 订单和送货地址

Question

我需要从 Magento 导出订单详细信息(SKU 和数量)和送货地址(姓名、街道、电话等)

我或多或少明白了，但是，我无法加入表格。我想我必须找到所有表的共同属性，但不知道是哪一个。

有什么想法吗？

这是到目前为止的代码:

SELECT ce.entity_id AS ID,adr.created_at as Fecha ,fn.value as Nombre,ln.value as Apellido,telephone.value as Telefono,ce.email AS EMAIL, ce.store_id AS Store_ID, 
-- concat(fn.value, ' ' , ln.value) AS Addressee, 
REPLACE(
SUBSTRING(
SUBSTRING_INDEX(str.value, '\n', 1),
CHAR_LENGTH(SUBSTRING_INDEX(str.value, '\n', 0)) + 1),
'\n', '') AS street1,

REPLACE(
SUBSTRING(
SUBSTRING_INDEX(str.value, '\n', 2),
CHAR_LENGTH(SUBSTRING_INDEX(str.value, '\n', 1)) + 1),
'\n', '') AS street2,
region.value AS Region,
city.value AS City,
postcode.value AS Postcode -- ,
-- IF(count(*)=1, IF(addrId.attribute_id=13, 'yes', 'no'), 'yes') AS defalut_billing,
-- IF(count(*)=1, IF(addrId.attribute_id=14, 'yes', 'no'), 'yes') AS defalut_shipping
FROM mage_customer_entity ce
LEFT JOIN mage_customer_entity_int addrId ON (addrId.entity_id = ce.entity_id )-- AND addrId.attribute_id IN (14)) 
LEFT JOIN mage_customer_address_entity adr ON (addrId.value = adr.entity_id) 
LEFT JOIN mage_customer_address_entity_varchar fn ON (fn.entity_id = adr.entity_id AND fn.attribute_id = 20)
LEFT JOIN mage_customer_address_entity_varchar ln ON (ln.entity_id = adr.entity_id AND ln.attribute_id = 22) 
LEFT JOIN mage_customer_address_entity_text str ON (str.entity_id = adr.entity_id AND str.attribute_id = 25) 
LEFT JOIN mage_customer_address_entity_varchar region ON (region.entity_id = adr.entity_id AND region.attribute_id = 28)
LEFT JOIN mage_customer_address_entity_varchar city ON (city.entity_id = adr.entity_id AND city.attribute_id = 26) 
LEFT JOIN mage_customer_address_entity_varchar telephone ON (telephone.entity_id = adr.entity_id AND telephone.attribute_id = 31) 
LEFT JOIN mage_customer_address_entity_varchar postcode ON (postcode.entity_id = adr.entity_id AND postcode.attribute_id = 30)
-- 'where 
-- cast(adr.created_at as date)= current_date()'
GROUP BY(addrId.value);

谢谢!

Answer 1

我最近从Magento的订单中获取了一些数据进行分析，所以我可能会帮助你一点。

首先，您可以在数据库中的 sales_flat_order 表中找到更多详细信息，该表包含许多用于连接其他表的信息，例如客户 id、日期和实体 id(在 eva 模型中更有用)。

还有其他表前缀以:sale_flat_order_开头，例如 sales_flat_order_item 和 sales_flat_order_address

我运行了你的 SQL，它只返回客户信息。看来您需要更多数据才能实现您的目标。但您需要告诉我们您想要什么样的输出格式。