Mysql 基于多行从表中 Select

发布于11月07日

在MySQL中，我有一个充满属性的表，如下所示:

USER_ID	ATTR_NAME	ATTR_VALUE
1	Name	Jess
1	Age	23
1	Sex	m
2	Name	Jess
2	Age	23
3	Name	Ann
3	Sex	f

(请注意，并非每个用户的每个属性都必须存在)

我要查找一个或多个属性确实匹配的所有USER_ID，例如:

显示名称为‘Jess’、年龄为‘23’的所有用户.

这应该返回:1, 2

我该如何在SQL中表达这一点？

编辑:当人们要求try 的时候，这是我的第一次try :

SELECT DISTINCT USER_ID 
FROM ATTR_TABLE 
WHERE 
  ( ATTR_NAME = 'Name' AND ATTR_VALUE = 'Jess' ) AND 
  ( ATTR_NAME = 'Age' AND ATTR_VALUE = '23' )

这肯定不会返回任何内容，因为没有一行有Attr_name Name和Attr_name Age...

这可能是基本的SQL，但是学习曲线是存在的，我无法提出一个工作解决方案，因为我还没有进入SQL行话，我甚至无法正确地谷歌可能的提示.

推荐答案

您的表架构是"EAV" or Entity-Attribute-Value.如果每个实体的属性数量未知或不稳定，则这是应用程序要使用的常见模式.如果这是您拥有的模式，并且user_id的属性不会频繁更改，因此需要使用EAV表，那么您可能需要考虑更改它，因为SQL和计算成本可能会变得很糟糕.

对于一个正常的user表，这将非常简单

SELECT user_id FROM users WHERE name='Jess' and Age='23';

但使用EAV时，属性列被存储为值，这在一定程度上颠覆了RDBMS的关系概念.这并不是一个"糟糕"的设计，只是您在用灵活性来换取计算/成本.

在你非常合理的要求中，有几种方法可以解决.最具成本效益的方法可能是收集与您的属性/值配对匹配的所有记录:

(attr_name = 'Name' AND attr_value = 'Jess') 
OR (attr_name = 'Age' AND attr_value = '23')

使用OR子句，因为表中的任何一条记录都不能具有多个属性，然后使用HAVING子句聚合和过滤聚合.

由于您正在搜索两个属性的组合，因此HAVING COUNT(*) = 2将把您的结果限制为仅包含您要查找的两个属性的user_id.

SELECT user_id
FROM mytable
WHERE (attr_name = 'Name' AND attr_value = 'Jess') 
  OR (attr_name = 'Age' AND attr_value = '23') 
GROUP BY user_id 
HAVING count(*) = 2

dbfiddle here个

还有其他方法可以剥离这只cat 的皮肤，但它们通常涉及通过case表达式或多个联接来透视数据，结果可能会导致非常繁重的计算.正如维基百科的那篇文章所说

EAV的致命弱点是难以与大型大量的EAV数据.通常有必要暂时或在列模型和行模型或EAV模型之间永久相互转换表示相同的数据；如果这样做，这两种方法都很容易出错手动以及CPU密集型.[.]转换操作为这叫旋转.

旋转很快就会变得昂贵，因此任何限制旋转或多表扫描需求的方法都是首选的.这个答案中使用的方法有一点风险，因为它假设每个不同的user_id不会有超过一个name或age个条目.您可以而且应该实现主键/约束以防止出现这种情况.

Mysql 基于多行从表中 Select

推荐答案

Mysql相关问答推荐

创建从表中筛选数据的过程时出错

如何在Sequelize中将查询作为选项？

SQL：CAST与窗口函数组合导致语法错误

根据上一行S列结果计算列的查询

如何为Oracle DB查询获得所需的GROUP BY结果？

插入时发生日期时间字段溢出错误

MySQL 8.0.33 Select json列时出现错误：排序内存不足，请考虑增加服务器排序缓冲区大小

Travis 构建失败并出现错误 LOAD DATA LOCAL INFILE 文件请求因访问限制而被拒绝

for each 查询设置 MySQL @@session.time_zone 而不是 CONVERT_TZ 的缺点？

完全匹配 IN 子句中的所有值

如何同时从同一个表中 Select 从 SQL 表中删除行

有人可以帮我优化这个查询吗？

MySql中的可见索引和不可见索引是什么

mysql 执行注释部分

SQL 查找边界之间的值

如何使用 C++ 连接 mySQL 数据库

MySQL Group By 和 Sum 其他列的总值

是否可以在内部连接期间重命名连接列？

如何在sequelize中定义多列的唯一索引

为什么在 MySQL 中使用外键约束？