mysql - 根据 EAV 中关联行中的值选择行

Question

我有两个表(EAV 关系)，Customer (id,name) 和 CustomerVarchar (id、customer_id、attribute_id、value)，关系如下。

客户 hasMany CustomerVarchar

CustomerVarchar 属于客户

我想根据 CustomreVarchar 中具有特定值的两行在 Customer 中选择一行。更具体地说，我想检索 Customer 中的一行，该行具有符合以下条件的这两个关联行(必须在 CustomerVarchar 中具有两个关联行):

row 1 has `attribute_id`= 5 and `value`=`John`

row 2 has `attribute_id`= 7 and `value`=`Doe`

这可能吗？

Answer 1

您可以使用以下命令从 customerVarchar 表返回 customer_id 以及所有属性和值:

select customer_id
from customerVarchar
group by customer_id
having 
  sum(case when attribute_id = 5 and value = 'John' then 1 else 0 end) > 0
  and sum(case when attribute_id = 7 and value = 'Doe' then 1 else 0 end) > 0;

然后您可以将其连接到您的 customer 表以返回包含该数据的客户:

select c.id, c.name
from customer c
inner join
(
  select customer_id
  from customerVarchar
  group by customer_id
  having 
    sum(case when attribute_id = 5 and value = 'John' then 1 else 0 end) > 0
    and sum(case when attribute_id = 7 and value = 'Doe' then 1 else 0 end) > 0
) v
  on c.id = v.customer_id;

参见SQL Fiddle with Demo