Count和Order By Where子句匹配 [英] Count and Order By Where clause Matches

问题描述

我正在为FAQ列表编写一些非常简单的搜索功能。我将搜索字符串分割为各种字符，包括空格。然后按照

  SELECT * 
 FROM faq 
 WHERE 
（（LOWER（问题）像'％what％'
或LOWER（问题）像'％is％'
或LOWER（ Question）像'％a％'
或LOWER（问题）喜欢'％duck％'））
  

我已经必须对其稍作修改，使其由我们的数据访问层生成，但是它应该使您了解发生了什么。

上面的查询已很好地说明了该问题大多数问题中可能都有a或在其中，但是我无法将其过滤掉，因为首字母缩写对搜索者来说很重要。建议的是，我们根据匹配关键字的数量进行排序。但是，我一直找不到在SQL中执行此操作的方法（我们没有时间创建带有关键字索引等的简单搜索引擎）。有谁知道是否有一种方法可以计算SQL语句中LIKE匹配项的数量并以此排序，以使关键字最多的问题出现在结果的顶部？

  SELECT * 
 FROM faq 
 WHERE 
（（LOWER（ Question））像'％what％'
或LOWER（ Question）像'％is％'
或LOWER（ Question）像'％a ％'
 OR LOWER（ Question）LIKE'％duck％'））
以
的情况下单，当LOWER（ Question）LIKE'％what％'然后1 else 0结束+ LOWER（ Question）像'％is％'时
的情况，然后1否则0结束+ LOWER（ Question）像'％a％'则
情况，则1否则0结束+ 
情况，当LOWER（ Question）像'％duck％'时，则1否则0结尾
下降； 
  

这甚至可以让您加权每个选择项的重要性，并假设用户（或一种算法）可以为每个术语分配权重。

一个警告：如果查询是动态构建的，是否意识到 SQL插入 攻击？

I'm writing some very simple search functionality for a list of FAQ's. I'm splitting the search string on a variety of characters, including spaces. Then performing a select along the lines of

SELECT *
FROM "faq"
WHERE
    ((LOWER("Question") LIKE '%what%'
   OR LOWER("Question") LIKE '%is%'
   OR LOWER("Question") LIKE '%a%'
   OR LOWER("Question") LIKE '%duck%'))

I've had to edit this slightly as its generated by our data access layer but it should give you an idea of whats going on.

The problem is demonstrated well with the above query in that most questions are likely to have the words a or is in them, however I can not filter these out as acronyms may well be important for the searcher. What has been suggested is we order by the number of matching keywords. However I have been unable to find a way of doing this in SQL (we do not have time to create a simple search engine with an index of keywords etc). Does anyone know if there's a way of counting the number of LIKE matches in an SQL statement and ordering by that so that the questions with the most keywords appear at the top of the results?

解决方案

I assume the list of matching keywords is being entered by the user and inserted into the query dynamically by the application, immediately prior to executing the query. If so, I suggest amending the query like so:

SELECT *
FROM "faq"
WHERE
    ((LOWER("Question") LIKE '%what%'
   OR LOWER("Question") LIKE '%is%'
   OR LOWER("Question") LIKE '%a%'
   OR LOWER("Question") LIKE '%duck%'))
order by
    case when LOWER("Question") LIKE '%what%' then 1 else 0 end +
    case when LOWER("Question") LIKE '%is%' then 1 else 0 end +
    case when LOWER("Question") LIKE '%a%' then 1 else 0 end +
    case when LOWER("Question") LIKE '%duck%' then 1 else 0 end
descending;

This would even enable you to "weight" the importance of each selection term, assuming the user (or an algorithm) could assign a weighting to each term.

One caveat: if your query is being constructed dynamically, are you aware of the risk of SQL Insertion attacks?

这篇关于Count和Order By Where子句匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！