命令SQL查询最佳匹配 [英] Order SQL query on best match

问题描述

我得到从我的查询返回的结果的列表，我想订购的最佳匹配。我会尝试尽可能清楚，但如果不够清楚，让我知道，我会尽力让它更清楚。

用户已经输入了一个设置列表，名为 findsettings 。用这些 findsettings 我正在搜索产品。

有几个字段，例如 min_review，max_price，permits_pets等etc 。

我想订购。例如，他需要首先订购 min_review为40％，然后 max_price = 10.00 的产品，然后 permits_pets = 0 。你可以用 OR 来做，但是我想要不安静匹配的结果也只显示在列表的底部。所以基本上他应该显示最好的匹配第一，然后第二个，第三个，等，直到产品匹配最少。

我不知道

解决方案

基本原理是创建一个相关性分数，

您希望每个条件具有相等的权重，在MySQL中，您只需添加每个布尔表达式（注意，标准SQL&;在其他RDBMS中，您可能必须使用 CASE 测试条件并产生一个数字）：

$ b b

  ORDER BY（review> = 40/100）+（price< = 10.00）+（permits_pets = 0）DESC 
 

如果条件不同等加权，您可以将每个表达式乘以其权重：

  ORDER BY 5 *（review> = 40/100）+ 3 *（price< = 10.00）+ 1 * （allowed_pets = 0）DESC 
  

和/或如果在某些子集上匹配的那些 $

 lang-sql prettyprint-override>  ORDER BY 5 *（review> = 40/100）+ 3 *（price< = 10.00）DESC，
 1 *（allowed_pets = 0）DESC 
  

如果您想查看结果中的相关性分数，可以类似地将任何上述表达式添加到 SELECT （然后使用 ORDER BY 子句中的结果列，以避免写入两次）：

  SELECT *，
（review> = 40/100）+（price< = 10.00）+（permitted_pets = 0）AS关联性，
 FROM my_table 
 ORDER BY相关性DESC 
  


$ b b

请注意，如果您想要获取具有最接近某个目标的值的记录（例如 min_review 接近40％，而不是精确），您可以采用它与您的目标值之间的（绝对？）差异：

  IF（review> = 40/100，review-40/100，NULL）
  / pre> 
 
 
但是，如果/当在单个标准中与其他表达式组合时，必须小心地对表达式进行适当加权。
 
I get a list of results returned from my query that I want to order on the best match. I will try to be as clear as I can be, but if something is not clear enough, let me know and I will try and make it more clear.

A user has already entered a list of settings, called findsettings. With these findsettings I am searching for products. This all goes well, until he should go sort out the best match.

There are several fields such as min_review, max_price, allows_pets, etc.

I want to order on. For example he needs to order first on products with a min_review of 40%, then max_price = 10.00 and then allows_pets = 0. You can do that with an OR, but I want the results that don't quiet match also to show, only at the bottom of the list. So basically he should show the best match first, then the 2nd one, the 3rd one etc., until the products that matches the least.

I'm not sure how to handle this, so I hope you can help me sort that out.
解决方案
The basic principle is to create a relevance score for each record and then sort by that.

Where you want each criterion to have equal weight, in MySQL you can just add each boolean expression together (note that this is non-standard SQL—in other RDBMS you may have to use CASE to test the condition and yield a number):
ORDER BY (review>=40/100) + (price<=10.00) + (allows_pets=0) DESC
If the criteria are not equally weighted, you can either multiply each expression by its weight:
ORDER BY 5*(review>=40/100) + 3*(price<=10.00) + 1*(allows_pets=0) DESC
And/or if those that match on some subset should always appear first irrespective of the later results, you can divide your ORDER BY clause:
ORDER BY 5*(review>=40/100) + 3*(price<=10.00) DESC,
         1*(allows_pets=0) DESC
Should you want to see the relevance score in your results, you can similarly add any of the above expressions to your SELECT (and then use the resulting column in your ORDER BY clause so as to avoid writing it twice):
SELECT   *,
         (review>=40/100) + (price<=10.00) + (allows_pets=0) AS relevance,
FROM     my_table
ORDER BY relevance DESC
Note that should you want to obtain records which have a value closest to some target (e.g. min_review close to 40%, rather than exact), you can take the (absolute?) difference between it and your target value:
IF(review>=40/100, review-40/100, NULL)
However, you must be careful to weight your expressions appropriately if/when combining with others in a single criterion.

                        
这篇关于命令SQL查询最佳匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！