[topvip]

I want to get rows for all customer details from magento tables.

Can anyone give me the query?

Tables are:
•customer_entity
•eav_attribute
•customer_entity_varchar'



Answer A:

SELECT 
`firstname`.`value` as `First_Name`, 
`surname`.`value` as `Surname`, 
`telephone`.`value` as `Telephone`,
`customer_entity`.`created_at`,
`customer_entity`.`updated_at` 
FROM 
`customer_address_entity_varchar` as `country` 
INNER JOIN 
`customer_address_entity_varchar` as  `firstname` USING (`entity_id`) 
INNER JOIN 
`customer_address_entity_varchar` as  `surname` USING (`entity_id`) 
INNER JOIN 
`customer_address_entity_varchar` as  `telephone` USING (`entity_id`) 
INNER JOIN 
`customer_entity` USING (`entity_id`) 
WHERE 
`country`.`attribute_id` = 26 && 
`country`.`value`="GB" && 
`firstname`.`attribute_id` = 19  && 
`surname`.`attribute_id` = 21  && 
`telephone`.`attribute_id` = 30  && 
`telephone`.`value` LIKE "07%"  
GROUP BY `telephone`.`value` 
limit 0,10;

useful code a: GROUP BY `customer_entity`.`email`

SELECT 
`firstname`.`value` as `First_Name`, 
`surname`.`value` as `Surname`, 
`customer_entity`.`email`
FROM 
`customer_address_entity_varchar` as `country` 
INNER JOIN 
`customer_address_entity_varchar` as  `firstname` USING (`entity_id`) 
INNER JOIN 
`customer_address_entity_varchar` as  `surname` USING (`entity_id`)  
INNER JOIN 
`customer_entity` USING (`entity_id`) 
WHERE 
`country`.`attribute_id` = 26 && 
`country`.`value`="US" && 
`firstname`.`attribute_id` = 19  && 
`surname`.`attribute_id` = 21  
GROUP BY `customer_entity`.`email`
limit 0,10;

code b: GROUP BY `entity_id`

SELECT 
`entity_id`,
`firstname`.`value` as `First_Name`, 
`surname`.`value` as `Surname`, 
`customer_entity`.`email`
FROM 
`customer_address_entity_varchar` as `country` 
INNER JOIN 
`customer_address_entity_varchar` as  `firstname` USING (`entity_id`) 
INNER JOIN 
`customer_address_entity_varchar` as  `surname` USING (`entity_id`)  
INNER JOIN 
`customer_entity` USING (`entity_id`) 
WHERE 
`country`.`attribute_id` = 26 && 
`country`.`value`="US" && 
`firstname`.`attribute_id` = 19  && 
`surname`.`attribute_id` = 21  
GROUP BY `entity_id`
limit 0,10;

useful code c, 将查出的结果保存到一个新表

create table aaa_custoemr SELECT 
`entity_id`,
`firstname`.`value` as `First_Name`, 
`surname`.`value` as `Surname`, 
`customer_entity`.`email`
FROM 
`customer_address_entity_varchar` as `country` 
INNER JOIN 
`customer_address_entity_varchar` as  `firstname` USING (`entity_id`) 
INNER JOIN 
`customer_address_entity_varchar` as  `surname` USING (`entity_id`)  
INNER JOIN 
`customer_entity` USING (`entity_id`) 
WHERE 
`country`.`attribute_id` = 26 && 
`country`.`value`="US" && 
`firstname`.`attribute_id` = 19  && 
`surname`.`attribute_id` = 21  
GROUP BY `entity_id`
limit 0,10000;

answer 01: create table dust select * from student;//用于复制前未创建新表dust的情况下 answer 02: insert into dust select * from student;//已经创建了新表dust的情况下