mysql - SQL查询仅返回1个结果

Question

查询仅返回 1 个结果，请帮忙

select 
p.products_id, pd.products_name
from
products_description pd, 
products_to_categories p2c,
products p
where 
EXISTS (SELECT p2pef.products_id 
                  FROM products_to_products_extra_fields p2pef
                  WHERE p2pef.products_extra_fields_id = '78' 
                 and p2pef.products_extra_fields_value = 'tak' 
                   and p.products_id = p2pef.products_id )
and 
EXISTS (SELECT p2pef.products_id 
                  FROM products_to_products_extra_fields p2pef
                  WHERE p2pef.products_extra_fields_id = '94' 
                  and p2pef.products_extra_fields_value = 'tak'
                    and p.products_id = p2pef.products_id )          


and p.products_status = '1'
and p.products_id = pd.products_id
and p.products_id = p2c.products_id
and pd.language_id = '4'
and p2c.categories_id = '26'
group by p.products_id

您对使用 EXISTS 的此查询有何看法？

您可能有另一个想法，即使用 products_to_products_extra_fields 表中的附加字段来显示产品表中满足多个条件的产品。

表架构:

产品(4行)

 _________________________
 products_id
 _________________________

 1
 2
 3
 4

products_to_products_extra_fields(8 行)

 ___________________________________________________________________
 products_id products_extra_fields_id products_extra_fields_value
 ___________________________________________________________________
 1 94 tak

 1 78 tak

 2 94 tak

 2 78 tak

 3 94 tak

 3 78 tak

 4 94 nie

 4 78 tak

有效的查询应返回(3 行)

 _______________
 products_id
 _______________

 1

 2

 3

Answer 1

您也可以使用 having 子句来实现此目的:

select      p.products_id
from        products p
inner join  products_description pd
         on p.products_id = pd.products_id
inner join  products_to_categories p2c
         on p.products_id = p2c.products_id
inner join  products_to_products_extra_fields p2pef
         on p.products_id = p2pef.products_id
where       p.products_status = '1'
and         pd.language_id = '4'
and         p2c.categories_id = '26'
and         p2pef.products_extra_fields_value = 'tak'
and         p2pef.products_extra_fields_id in ('78', '94')
group by    p.products_id
having      count(distinct p2pef.products_extra_fields_id) = 2

..但结果是一样的。如果您的记录少于预期，请确保您希望输出的所有产品也满足状态、语言和类别方面的其他条件。

注意:使用 join 语法作为表达式连接条件。