mysql - 使用包含多个 EXIST() 语句的查询计算索引存在的次数

Question

我的查询根据这些产品是否存在于单独的表索引中来获取这些产品的结果。我正在尝试计算它们存在的所有实例，以便我可以按相关性对结果进行排序。我尝试的所有操作似乎都将变量@priority 返回为 0。有什么想法吗？

也许使用 join 语句更好？

感谢您的帮助。这是我的 MySQL 查询:

SELECT  `products` . * , @priority
    FROM  `products`
    LEFT JOIN productstypes_index ON productstypes_index.product_id = products.id
WHERE (

EXISTS (

SELECT *
    FROM  `productstypes_index`
    WHERE  `productstypes_index`.`product_id` =  `products`.`id`
    AND  `productstypes_index`.`_type_id` =  '1'
)
AND (
(
(

EXISTS (

SELECT @priority := COUNT( * )
    FROM  `producthashtags_index`
    WHERE  `producthashtags_index`.`product_id` =  `products`.`id`
    AND  `producthashtags_index`.`producthashtag_id` =  '43'
)
)
AND (

EXISTS (

SELECT @priority := COUNT( * )
    FROM  `producthashtags_index`
    WHERE  `producthashtags_index`.`product_id` =  `products`.`id`
    AND  `producthashtags_index`.`producthashtag_id` =  '11'
)
)
)
)
)
ORDER BY  `updated_at` DESC;

Answer 1

您可以不使用那些 exists，也可以不使用变量。此外，如果联接表上有 exists 条件，则 left join 没有任何意义。那么你不妨做更高效的inner join，将额外的类型条件放在join条件中。

可以通过哈希标签的计数来计算优先级，但仅限于 id in ('43', '11')。

SELECT     products.* 
           count(distinct producthashtags_index.producthashtag_id) priority
FROM       products
INNER JOIN productstypes_index 
        ON productstypes_index.product_id = products.id
       AND productstypes_index._type_id =  '1'
INNER JOIN producthashtags_index 
        ON producthashtags_index.product_id = products.id
       AND producthashtags_index.producthashtag_id in ('43', '11')
GROUP BY   products.id  
ORDER BY   updated_at DESC;