仅限于相关结果 - MYSQL [英] Limit to only relevant results - MYSQL

问题描述

<前>SELECTrideid, year, model, rating, SUM(Relevance) as SearchRelevance从(SELECTrideid, year, model, rating, 1 as Relevance FROM carsWHERE cat LIKE '%$keyword%'联合所有SELECTrideid, year, model, rating, 1 as Relevance FROM carsWHERE cat2 LIKE '%$keyword2%')AS t GROUP BYrideid ORDER BY SUM(Relevance) DESC";

大家好，我在其他成员的帮助下得到了这个很棒的查询，它在基于相关性系统对我的结果进行排序方面非常有效.因此，当我的两个搜索条件都被满足时，这些结果会被首先排序.

问题在于，不符合两个条件(仅符合 1 个条件)的结果显然会显示在结果中，但顺序较低.我实际上想删除这些结果，有没有办法优化此查询，以便返回的唯一结果是同时满足两个条件的结果?

将 LIKE 更改为 = 不是一种选择，因为结果字段是 textareas，因此 mysql 需要在 textareas 中搜索关键字.

谢谢

解决方案

您可以将 HAVING SUM(Relevance) = 2 添加到外部 SELECT，就在GROUP BYrideid，因为 Relevance 是匹配条件的计数.(如果您稍后添加更多条件，则需要将 2 替换为更大的数字以进行匹配.)

但是，您也可以用更简单的方式编写整个查询:去掉子选择和 UNION ALL，只需执行 WHERE cat LIKE '%$keyword%' AND cat2 LIKE '%$keyword2%'.子选择和 UNION 的全部意义在于可以得到只匹配一个或另一个的结果.

SELECT rideid, year, model, rating, SUM(Relevance) as SearchRelevance 
FROM( 
SELECT rideid, year, model, rating, 1 as Relevance FROM cars 
WHERE cat LIKE '%$keyword%' 
UNION ALL 
SELECT rideid, year, model, rating, 1 as Relevance FROM cars 
WHERE cat2 LIKE '%$keyword2%') 
AS t GROUP BY rideid ORDER BY SUM(Relevance) DESC ";

Hi all, I got this awesome query with the help of other members, which works very well in sorting my results based on a relevance system. So that when both of my search criteria are filled those results are ordered first.

The problem is that the results which don't match both criteria (only match 1 criteria) obviously show up in the results, but in a lower order. I want to actually trash those results, is there a way to refine this query so that the only results that come back are the ones that fill both criteria?

Changing the LIKE to = is not an option because the result fields are textareas so mysql needs to search inside the textareas for the keyword.

Thank you

解决方案

You could just add HAVING SUM(Relevance) = 2 to the outer SELECT, just after the GROUP BY rideid, since Relevance is a count of how many criteria matched. (If you add more criteria later, you'll need to replace the 2 with a larger number to match.)

However, you could also write the whole query in a much simpler way: get rid of the sub-select and UNION ALL, and just do WHERE cat LIKE '%$keyword%' AND cat2 LIKE '%$keyword2%'. The whole point of the sub-select and UNION was to make it possible to get results that match only one or the other.

这篇关于仅限于相关结果 - MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！