在magento中使用mysql提取客户账单和送货地址

假设这些是eav_attribute表中客户地址属性的属性ID

default_billing 7
default_shipping 8
firstname 9
lastname 10
street 16
region_id 13
city 15
postcode 14

mysql查询从Magento中提取所有客户账单和送货地址

SELECT ce.entity_id AS ID, 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 Posecode,
IF(count(*)=1, IF(addrId.attribute_id=7, 'yes', 'no'), 'yes') AS defalut_billing,
IF(count(*)=1, IF(addrId.attribute_id=8, 'yes', 'no'), 'yes') AS defalut_shipping
FROM customer_entity ce
LEFT JOIN customer_entity_int addrId ON (addrId.entity_id = ce.entity_id AND addrId.attribute_id IN (7,8)) 
LEFT JOIN customer_address_entity adr ON (addrId.value = adr.entity_id) 
LEFT JOIN customer_address_entity_varchar fn ON (fn.entity_id = adr.entity_id AND fn.attribute_id = 9)
LEFT JOIN customer_address_entity_varchar ln ON (ln.entity_id = adr.entity_id AND ln.attribute_id = 10) 
LEFT JOIN customer_address_entity_text str ON (str.entity_id = adr.entity_id AND str.attribute_id = 16) 
LEFT JOIN customer_address_entity_varchar region ON (region.entity_id = adr.entity_id AND region.attribute_id = 13)
LEFT JOIN customer_address_entity_varchar city ON (city.entity_id = adr.entity_id AND city.attribute_id = 15) 
LEFT JOIN customer_address_entity_varchar postcode ON (postcode.entity_id = adr.entity_id AND postcode.attribute_id = 14)
GROUP BY(addrId.value);