mysql - 按 IN() 子句中的大多数匹配项对结果进行排序

Question

我正在寻找重写使用 IN() 子句的查询的最佳方法。 我想按在 products_adjectives 和 products_interests 中匹配最多的产品对结果进行排序。

我有 5 张 table 。

- products
    title, price, description, etc.
- interests
    interest_id, interest_name
- adjectives
    adjective_id, adjective_name
- products_interests
    product_id, interest_id
- products_adjectives
    product_id, adjective_id

我目前正在使用此查询返回与任何形容词或兴趣相匹配的任何产品。这是一个例子:

SELECT *
  FROM products
    LEFT JOIN products_adjectives
      ON products_adjectives.product_id = products.id
    LEFT JOIN products_interests
      ON products_interests.product_id = products.id
  WHERE products_adjectives.adjective_id IN (3,6,9,12,13) OR products_interests.interest_id IN (2,5,8,12,16,18)
  GROUP BY products.id ORDER BY ABS(products.price)

目前，返回存在于任何 adjective_ids 或 interest_ids 中的任何产品。该查询将返回任何具有任何 adjective_id 3,6,9,12,13 或任何 interest_id 2,5,8,12,16,18 的产品我想继续返回所有结果，但我希望它们按哪些产品包含形容词或兴趣的最匹配项进行排序。所以，如果一个产品有形容词 3,6,9,10,11，我希望在一个有形容词 3,4,5,7,8 的产品之前>

任何想法或方向将不胜感激。如果我的做法完全错误(使用 IN() 子句)，请告诉我!

谢谢!

Answer 1

我在想下面这样的事情。您遗漏的关键思想是按 count(*) DESC 排序 此查询的缺点是，如果兴趣表或形容词表中没有匹配记录，则不会提取任何产品。然而，因为我认为你是根据它们的相关性来拉东西，你不应该想要零相关性的产品。

SELECT *, count(*) as relevance 
FROM products, products_adjectives, products_interests
WHERE products.id = products_interests.product_id 
  AND products.id = products_adjectives.product_id 
  AND (interest_id IN (14, 22, 78) OR adjective_id IN (8, 17, 26))
GROUP BY products.id
ORDER BY count(*) DESC, price ASC

参见 fiddle :http://sqlfiddle.com/#!3/838571/2