在mysql中,我有一个充满属性的表,看起来像这样:
| 用户ID| ATTR_NAME|属性值|
| --|--|--|
| 1 |名称|Jess|
| 1 |年龄| 23 |
| 1 |性|M|
| 2 |名称|Jess|
| 2 |年龄| 23 |
| 3 |名称|安|
| 3 |性|F|
(Note不是每个属性都必须为每个用户提供)
我想找到所有USER_ID
的一个或多个属性匹配,例如:
显示所有用户的名字是'Jess'和年龄是'23'。
返回:1, 2
我该如何用SQL来表达呢?
编辑:当人们要求尝试时,这是我的第一次尝试:
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行话,我甚至无法正确地谷歌可能的提示。
6条答案
按热度按时间c7rzv4ha1#
您的表模式是"EAV" or Entity-Attribute-Value。如果每个实体的属性数量未知或不稳定,则这是应用程序使用的常见模式。如果这是您拥有的模式,并且
user_id
的属性不会经常更改,因此需要EAV表,则您可能需要考虑更改它,因为SQL和计算成本可能会变得很难看。对于普通的
user
表,这将非常简单,字符串
但是使用EAV,你的属性列被存储为值,在某种程度上颠覆了RDBMS的关系概念。这不是一个“坏”的设计,只是你在用计算/成本来换取灵活性。
在您非常合理的要求中,有几种方法可以解决。最具成本效益的方法可能是收集所有与您的属性/值配对匹配的记录:
型
使用
OR
子句,因为表中的任何一条记录都不能有多个属性,然后使用HAVING
子句聚合和过滤聚合。由于您正在搜索两个属性的组合,
HAVING COUNT(*) = 2
将把您的结果限制为仅包含您所搜索的两个属性的user_id
s。型
dbfiddle here的
还有其他的方法来剥这只猫的皮,但它们通常涉及到通过case表达式或多个连接来透视数据,结果可能会导致非常繁重的计算。
EAV的致命弱点是难以处理大量的EAV数据。通常需要暂时或永久地在同一数据的列和行或EV模型表示之间进行相互转换;如果手动完成,这可能容易出错,也可能是CPU密集型的。[.]转换操作称为旋转。
数据透视表的开销很快就变得很大,所以任何限制数据透视表或多表扫描需求的方法都是首选。这个答案中使用的方法有点冒险,因为它假设每个
user_id
不会有超过一个name
或age
条目。你可以,也应该,实现主键/约束来防止这种情况。fiei3ece2#
首先创建临时表
在第一个选择查询中替换
user_attributes
,以匹配您的表名字符串
temp_grouped_attribute将是这样的
| 用户ID|名称|年龄|性|
| --|--|--|--|
| 1 |Jess| 24 |M|
| 2 |Jess| 23 |Null|
| 3 |安|null| F|
最后一个选择查询将是这样的
| 用户ID|名称|年龄|性|
| --|--|--|--|
| 2 |Jess| 23 |Null|
a9wyjsp73#
下面是一个使用self join的方法:
字符串
Demo here
oyxsuwqo4#
让我们翻译您的请求:
显示我=>
SELECT
all users =>
*
或具体列出所需的字段其中=>
WHERE
标准将遵循.名称为'Jess' =>
ATTR_NAME = 'Name' AND ATTR_VALUE = 'Jess'
并且=>
AND
年龄是'23' =>ATTR_NAME = 'Age' AND ATTR_VALUE = '23'
使这个结果集复杂化的是,你想要选择的实体被拆分到多个行中,第一步是转置值(作为一个动态模式,有几个选项),下面使用自连接来做不同的事情:
字符串
kyks70gy5#
据我所知,你想得到的用户的USER_ID取决于他们的属性表
字符串
qv7cva1a6#
这是使用
group by
和having
子句的另一种方式:字符串
Demo here