在EAV中选择不同的值对

Question

我正在开发一个用户数据库，其中的配置文件数据已从简单表更改为实体属性值表。

与以前一样，结构沿着这些线：

userid (int)
address 1 (varchar)   
city (varchar)
country (varchar)

现在是这样的：

userid (int)
key (varchar)  
value (varchar) 

例如

userid key      value
150    city     London
150    country  UK
151    city     New York
151    country  USA
152    country  Mexico   

我需要得到一个不同的城市/国家对列表和每个国家的所有用户的计数：

city      country  count
London    UK       18
New York  USA      25

不能保证每个用户都存在每个键值对，即可能有城市或国家，也可能两者都有，或者两者都没有，以及任何数量的其他键值对。

这对旧结构来说很简单，但我甚至想不出如何开始，如果能提供一些指针

Answer 1

您的最佳解决方案是回到传统的表，因为EAV使大多数查询比它应该做的要困难得多-在这里见证您的问题。您将一直进行自联接，直到厌倦为止，再制造允许您执行合理查询的表结构。

每个用户ID的城市和国家：

SELECT a.userID, a.value AS city, b.value AS country
  FROM EAV AS a
  JOIN EAV AS b ON a.UserID = b.UserID
 WHERE a.key = 'city'
   AND b.key = 'country';

所以，你最终会得到：

SELECT city, country, count(*)
  FROM (SELECT a.userID, a.value AS city, b.value AS country
          FROM EAV AS a
          JOIN EAV AS b ON a.UserID = b.UserID
         WHERE a.key = 'city'
           AND b.key = 'country'
       ) AS c
 GROUP BY city, country;

如果有人可能有两个城市或两个国家的记录，这将为您提供一个笛卡尔乘积，该乘积中该用户的行数与该用户的城市和国家记录数的乘积一样多。

这相当有意和有意识地忽略了那些有城市而没有国家或国家而没有城市的用户（更不用说那些两者都没有的用户）。扩展解决方案来处理这些问题只是有点痛苦——我认为，您最终会得到一个3向UNION，尽管您可能能够设计出具有多个左外部联接的东西。但是，数据可以在没有必要限制的情况下输入EAV系统，以确保用户有一个城市和一个国家，这只是拒绝EAV的众多原因之一。

很抱歉你把这件事强加给了你。我建议查看http://careers.stackoverflow.com/作为一种摆脱痛苦的方法，因为这只是痛苦的开始。

---

与没有城市或国家或两者的用户打交道。我认为这或多或少会做到：

SELECT a.userID, b.value AS city, c.value AS country
  FROM (SELECT DISTINCT UserID FROM EAV) AS a
  LEFT JOIN EAV AS b ON a.UserID = b.UserID
  LEFT JOIN EAV AS c ON a.UserID = c.UserID
 WHERE b.key = 'city'
   AND c.key = 'country';

这应该为每个用户提供一条记录，只要该用户没有多个城市或国家的记录。a扫描为您提供EAV表中存在的唯一用户ID列表；两个外部联接为每个这样的用户ID提供相应的一个或多个城市和相应的国家/地区，如果给定的用户ID没有城市记录或国家/地区记录（或两者都没有），则会生成null。

Answer 2

re:我需要获得一个不同的城市/国家对列表

SELECT DISTINCT country,city
FROM
(SELECT DISTINCT userid, VALUE AS country FROM TABLE WHERE KEY = 'country') country INNER JOIN
(SELECT DISTINCT userid, VALUE AS city FROM TABLE WHERE KEY = 'city') city ON
country.userid = city.userid
--count of all users for each country
SELECT VALUE AS country, 
COUNT(DISTINCT userid) AS user_count 
FROM TABLE 
WHERE KEY = 'country'
GROUP BY 
VALUE