从Magento 1.9数据库中获取客户姓名、电子邮件和电话号码
从Magento 1.9数据库中获取客户姓名、电子邮件和电话号码
提问于 2018-06-16 20:08:00
我有两个不同的SQL代码，一个用于获取用户名和电子邮件，另一个用于获取电话号码。
您能否帮助将其与一个脚本相结合，以便可以获得用户名、电子邮件和电话号码的列表？
下面是获取用户名和电子邮件的查询( @Steve Robbins)：
select ce.entity_id, concat(cevf.value, ' ', cevl.value) fullname, ce.email
from customer_entity ce
inner join customer_entity_varchar cevf
on ce.entity_id = cevf.entity_id
inner join eav_attribute eaf
on eaf.attribute_id = cevf.attribute_id
inner join customer_entity_varchar cevl
on ce.entity_id = cevl.entity_id
inner join eav_attribute eal
on eal.attribute_id = cevl.attribute_id
inner join eav_entity_type eet
on eet.entity_type_id = eal.entity_type_id = eaf.entity_type_id
where
eet.entity_type_code = 'customer'
and eaf.attribute_code = 'firstname'
and eal.attribute_code = 'lastname'
order by ce.entity_id这个只用于电话号码(通过@zaka47)：
select customer_address_entity_varchar.value from customer_address_entity_varchar
left join customer_entity on customer_entity.entity_id = "your_customer_id"
left join customer_address_entity on customer_address_entity.parent_id = customer_entity.entity_id
join eav_attribute on eav_attribute.attribute_code="telephone"
where customer_address_entity_varchar.attribute_id = eav_attribute.attribute_id我的目标是获得以下表结构：
------------------------------------------------------
Full Name | Email Address | Phone Number
------------------------------------------------------
David Ros | david@email.com | 02-30493929
Joe Pratt | joe@email.com | 03-20392030
------------------------------------------------------谢谢。
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-06-17 02:03:47
在这里，您的第一个查询包含另外两个连接(customer_address_entity_varchar和eav_attribute)，以获取属性电话
select ce.entity_id,
concat(cevf.value, ' ', cevl.value) fullname,
ce.email,
caev.value telephone
from customer_entity ce
inner join customer_entity_varchar cevf
on ce.entity_id = cevf.entity_id
inner join eav_attribute eaf
on eaf.attribute_id = cevf.attribute_id
inner join customer_entity_varchar cevl
on ce.entity_id = cevl.entity_id
inner join eav_attribute eal
on eal.attribute_id = cevl.attribute_id
inner join eav_entity_type eet
on eet.entity_type_id = eal.entity_type_id = eaf.entity_type_id
inner join customer_address_entity_varchar caev
on ce.entity_id = caev.entity_id
inner join eav_attribute eat
on caev.attribute_id = eat.attribute_id
where
eet.entity_type_code = 'customer'
and eaf.attribute_code = 'firstname'
and eal.attribute_code = 'lastname'
and eat.attribute_code='telephone'
order by ce.entity_idStack Overflow用户
发布于 2020-06-04 17:19:15
上面的答案仍然是错误的，客户实体id是customer_address_entity中的link而不是customer_address_varchar。
对应查询如下：
select ce.entity_id,
concat(cevf.value, ' ', cevl.value) fullname,
ce.email,
caev.value telephone from customer_entity ce
inner join customer_entity_varchar cevf
on ce.entity_id = cevf.entity_id
inner join eav_attribute eaf
on eaf.attribute_id = cevf.attribute_id
inner join customer_entity_varchar cevl
on ce.entity_id = cevl.entity_id
inner join eav_attribute eal
on eal.attribute_id = cevl.attribute_id
inner join eav_entity_type eet
on eet.entity_type_id = eal.entity_type_id = eaf.entity_type_id
inner join customer_address_entity cae
on cae.parent_id = ce.entity_id
inner join customer_address_entity_varchar caev
on cae.entity_id = caev.entity_id
inner join eav_attribute eat
on caev.attribute_id = eat.attribute_id
where
eet.entity_type_code = 'customer'
and eaf.attribute_code = 'firstname'
and eal.attribute_code = 'lastname'
and eat.attribute_code='telephone'
order by ce.entity_id desc limit 1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50887767
复制相关文章
相似问题