代码之家 › 专栏 › 技术社区 › BrynJ

MySQL是构造此查询的最佳方法吗?

database mysql

BrynJ · 技术社区 · 16 年前

用户可以通过这些属性的组合来过滤产品,这意味着如果选择了多个属性,则只返回具有所有这些属性的产品。不幸的是,这个规则现在有一个例外,用户选择两个特定属性中的一个需要包含其中一个(或两个)的结果。

查询当前如下所示(不是我的代码):

SELECT DISTINCT p.* FROM products AS p 
INNER JOIN attributes a ON p.product_id=a.property_id 
WHERE a.attribute_id IN (1,3,7) 
GROUP BY p.property_id 
HAVING COUNT(DISTINCT a.attribute_id) = 3

我怀疑以上是检索所需产品的一种特别有效的方法,但我不确定如何根据新的要求继续进行。

我现在创建了一些php代码,用于在选择两个“特殊”属性(3和7)时构造特殊查询:

SELECT DISTINCT p.* FROM products AS p 
INNER JOIN attributes a ON p.product_id=a.property_id 
WHERE a.attribute_id IN (1,3) OR a.attribute_id IN (1,7) 
GROUP BY p.property_id 
HAVING COUNT(DISTINCT a.attribute_id) = 2

是否可以使用某种子查询,或者有什么替代方法?

5 回复 | 直到 16 年前

eliego 16 年前

查询似乎很好,只是您可能可以删除DISTINCT修饰符,因为您已经按id分组了。关于新的需求,您不能在代码中解决它,然后再到达SQL查询吗?

编辑:另一种方法是为每个必需的属性构造一个内部联接的查询,但这可能会慢得多

benlumley 16 年前

ÊÉÄ±u 16 年前

这是执行原始查询的更好方法:

SELECT ... FROM products AS p 
INNER JOIN attributes a1 ON p.product_id=a1.property_id AND a1.attribute_id=1
INNER JOIN attributes a2 ON p.product_id=a2.property_id AND a2.attribute_id=3
INNER JOIN attributes a3 ON p.product_id=a3.property_id AND a3.attribute_id=7

并且,假设您需要属性_id 3,您希望使用属性_id 7,并且假设您还需要属性_id 1,而属性_id 1不是这些特殊属性之一:

SELECT ... FROM products AS p 
INNER JOIN attributes a1 ON p.product_id=a1.property_id AND a1.attribute_id=1
LEFT OUTER JOIN attributes a2 ON p.product_id=a2.property_id AND a2.attribute_id=3
LEFT OUTER JOIN attributes a3 ON p.product_id=a3.property_id AND a3.attribute_id=7
WHERE a2.attribute_id IS NOT NULL OR a3.attribute_id IS NOT NULL

我怀疑这两种方法中的任何一种都会比使用distinct/having/group by AGGRAME操作的原始方法快得多。attributes表应该在(property_id,attribute_id)或(attribute_id,property_id)上有一个多列唯一索引,尽管我假设property_id更具选择性,因此应该是索引中最左边的列。

dkretz 16 年前

选择

从产品中提取p

p.product_id=a1.property_id和a1.attribute_id=1上的内部连接属性a1

左连接p.product_id=a3.property_id和a3.attribute_id=7上的属性a3

在哪里(

+当a1.product_属性_id为空时,则0否则1结束
)>0

dotjoe 16 年前

其中(1,3)中的a1.attribute_id或(1,7)中的a1.attribute_id

与

SELECT p.* FROM products  
INNER JOIN (
    SELECT a1.property_id  
    FROM attributes a1 
    WHERE a1.attribute_id IN (1,3,7)
    GROUP BY a1.property_id 
    HAVING COUNT(DISTINCT a1.attribute_id) = 2
) as a ON p.product_id=a.property_id