| topvip |
2012-11-30 04:15 PM |
getting customer info from magento table structure
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:
Code:
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`
Code:
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`
Code:
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, 将查出的结果保存到一个新表
Code:
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的情况下 |
