在 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 没有城市记录或国家记录（或两者），则会生成空值。

Answer 2

回复：我需要获取城市/国家对的不同列表

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