使所需的匹配数直接对应于列的char_length [英] Make number of matches required directly corresponds with char_length of column

问题描述

我有一个工作的SELECT语句。但是，我想添加一些东西。我想让它使所需的匹配数，直接对应的列的输入的char_length。例如：

I have a working SELECT statement. However, I would like to add something to it. I would like to make it so that the number of matches required, directly corresponds with the char_length of the column 'input'. So, for example:

if (char_length(input) <= 5) { matches required is 1 }
if (char_length(input) > 5 && char_length(input) <= 10) { matches required is 2 }
if (char_length(input) > 10 && char_length(input) <= 15) { matches required is 3 }

and ect...

如何将^^^添加到下面的SELECT语句？

$text = "one";
$textLen = strlen($text);

SELECT response, ( input LIKE  '% $text %' ) as matches
FROM allData
WHERE (char_length(input) >= '$textLen'-($textLen*.1) 
AND char_length(input) <= '$textLen'+($textLen*.1)) 
HAVING matches > 0 
AND matches = (select max(( input LIKE  '% $text %' )) from allData) limit 30;

推荐答案

先单独运行以下查询：

SELECT @limit := 0;

然后修改您的查询，如下所示：

Then modify your query to look like this:

SELECT response, ( input LIKE  '% $text %' ) as matches, @limit := @limit + 1
FROM allData
WHERE (char_length(input) >= '$textLen'-($textLen*.1) 
AND char_length(input) <= '$textLen'+($textLen*.1)) 
AND @limit < CEIL(CHAR_LENGTH(input) / 5)
HAVING matches > 0 
AND matches = (select max(( input LIKE  '% $text %' )) from allData) limit 30;

这应该将匹配限制为所需的值

That should limit your matches to the needed values

这篇关于使所需的匹配数直接对应于列的char_length的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！