我正在构建一个客户的数据库模式&我更喜欢使用EAV(实体属性值(Magento风格,将这些数据存储在其他表中并链接起来,而不是随着时间的推移永远添加列。
我的SQL Fiddle将更好地了解结构http://sqlfiddle.com/#!2/82a70
在这种情况下,我想生成一个查询,显示所有客户及其存储在其他表中的相关信息,例如,如果客户没有获得特定实体的值,则只显示NULL。
例如,我想显示的输出如下:
customer_id | first_name | surname | profession | club
1 bob geldof singer NULL
2 lionel messi footballer barcelona fc
有人能在以后可能添加更多实体或属性的基础上,建议使用最优化的MySQL查询来生成此记录集吗?
这种类型的SQL查询将生成您要查找的表,但您必须根据"eav_attribute"表的内容动态生成此查询。
正如KM所说,每个属性都需要另一个LEFT JOIN
SELECT
customer_entity.customer_id AS customer_id,
name.value AS firstname,
surname.value AS surname,
profession.value AS profession,
club.value AS club
FROM customer_entity
LEFT JOIN customer_varchar AS name
ON name.entity_id = customer_entity.customer_id AND name.attribute_id = 1
LEFT JOIN customer_varchar AS surname
ON surname.entity_id = customer_entity.customer_id AND surname.attribute_id = 2
LEFT JOIN customer_varchar AS profession
ON profession.entity_id = customer_entity.customer_id AND profession.attribute_id = 3
LEFT JOIN customer_varchar AS club
ON club.entity_id = customer_entity.customer_id AND club.attribute_id = 4
;