计算每个条目的LIKE匹配数 [英] Count number of LIKE-matches per Entry

问题描述

在一个简单的例子中，我尝试解释一下比赛的意思:

I try to explain what I mean by matches on a simple example:

我有一个这样的表myprods:

id | name
1    galaxy s4 mini
2    samsung galaxy s4
3    galaxy galaxy s3
4    iphone 4s
5    apple iphone 4s

到目前为止，我会进行以下查询:

I'd have this query so far:

SELECT *
FROM   myprods
WHERE  name LIKE "%samsung%" OR name LIKE "%galaxy%" OR name LIKE "%s4%"

我的查询结果是:

id | name
1    galaxy s4 mini
2    galaxy s4
3    galaxy galaxy s3

现在，我不仅要返回匹配的行，而且还要返回命中数.如果"LIKE"短语中的一个适用于该行，则将被命中一次.因此，在这种情况下，我希望匹配数为0、1、2或3.这意味着:对于id = 3，LIKE短语"%galaxy%"不应被视为两个匹配项，而应被视为匹配"或没打".

and now I'd not only return the matched rows but also the number of hits. ONE hit would be if ONE of the LIKE-phrases applies to the row. So in this case I would expect the hits to be 0, 1, 2 or 3. That means: the LIKE phrase "%galaxy%" should NOT be counted as two hits for the id=3, it just counts as "hit" or "not hit".

现在的预期结果将是:

id | name               | hits
1    galaxy s4 mini       2
2    samsung galaxy s4    3
3    galaxy galaxy s3     1

可能会出现高性能mysql短语吗? 还是我应该只选择上面查询中的行，然后通过PHP中的strpos将单个子字符串与返回的名称进行匹配?

Possible in a performant mysql phrase? Or should I rather select only the rows with my query above and then match the single substrings to the returned names via strpos in PHP?

推荐答案

在MySQL中，布尔表达式可以用作整数，其中0表示false，1表示true.因此，以下工作原理:

In MySQL, boolean expressions can be used as integers -- with 0 for false and 1 for true. So, the following works:

SELECT p.*,
       ((name LIKE '%samsung%') + (name LIKE '%galaxy%') + (name LIKE '%s4%')) as hits
FROM   myprods p
WHERE  name LIKE '%samsung%' OR name LIKE '%galaxy%' OR name LIKE '%s4%';

如果是MySQL，您还可以将该查询表示为:

If MySQL, you can also express this query as:

SELECT p.*,
       ((name LIKE '%samsung%') + (name LIKE '%galaxy%') + (name LIKE '%s4%')) as hits
FROM   myprods p
HAVING hits > 0;

在此上下文中使用having是SQL扩展，在其他数据库中不起作用.但是，它确实允许查询引用列别名进行过滤，而无需使用子查询.

The use of having in this context is a SQL extension and doesn't work in other databases. But, it does allow a query to refer to a column alias for filtering, without using a subquery.

这篇关于计算每个条目的LIKE匹配数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！