计算匹配单词数 [英] Counting number of matched words

问题描述

我有两个表，数据填充在 sqlFiddle

i have two tables, with data populated in this sqlFiddle

现在，我有一个如下所示的查询，当我搜索"George Tabuki Street Fighter Miley Cyrus"时，我有了php explode搜索字符串，并通过添加+ CASE WHEN ... END动态地构建了查询

right now, I have a query that looks like the below, when i search for "George Tabuki Street Fighter Miley Cyrus", I have the php explode the search string and dynamically build the query by adding the + CASE WHEN ... END

SELECT id,word,LEFT(description,100)as description, 
             IFNULL((SELECT sum(vote)
                     FROM vote v
                     WHERE v.definition_id = d.id),0) as votecount,
         0
    + CASE WHEN LOCATE('George',CONCAT(word,description,`usage`))>0 THEN 1 ELSE 0 END
    + CASE WHEN LOCATE('Tabuki',CONCAT(word,description,`usage`))>0 THEN 1 ELSE 0 END
    + CASE WHEN LOCATE('Street',CONCAT(word,description,`usage`))>0 THEN 1 ELSE 0 END
    + CASE WHEN LOCATE('Fighter',CONCAT(word,description,`usage`))>0 THEN 1 ELSE 0 END
    + CASE WHEN LOCATE('Miley',CONCAT(word,description,`usage`))>0 THEN 1 ELSE 0 END
    + CASE WHEN LOCATE('Cyrus',CONCAT(word,description,`usage`))>0 THEN 1 ELSE 0 END
as `match`
FROM definition d
HAVING `match` > 0
ORDER BY `match` DESC,votecount DESC

上面的查询返回的正是我想要的.

The query above returns exactly what i want.

问题:有没有更好的方法，或者mySQL中是否有一个函数返回匹配单词数的计数?

Question: is there a better way, or is there a function in mySQL that returns a count of number of matched words?

我找到了一种更好的方法，但并不是更好的方法，但是它返回了匹配项出现的次数

I have found a better way, well not better way but it returns a count of occurences of matching terms

SELECT id,word,LEFT(description,100)as description, 
             IFNULL((SELECT sum(vote)
                     FROM vote v
                     WHERE v.definition_id = d.id),0) as votecount,
         0
        + IFNULL(ROUND((LENGTH(CONCAT(word,description,`usage`,`by`)) - LENGTH(REPLACE(CONCAT(word,description,`usage`,`by`), 'George', '')))/LENGTH('George')),0)
        + IFNULL(ROUND((LENGTH(CONCAT(word,description,`usage`,`by`)) - LENGTH(REPLACE(CONCAT(word,description,`usage`,`by`), 'Tabuki', '')))/LENGTH('Tabuki')),0)
        + IFNULL(ROUND((LENGTH(CONCAT(word,description,`usage`,`by`)) - LENGTH(REPLACE(CONCAT(word,description,`usage`,`by`), 'Street', '')))/LENGTH('Street')),0)
        + IFNULL(ROUND((LENGTH(CONCAT(word,description,`usage`,`by`)) - LENGTH(REPLACE(CONCAT(word,description,`usage`,`by`), 'Fighter', '')))/LENGTH('Fighter')),0)
        + IFNULL(ROUND((LENGTH(CONCAT(word,description,`usage`,`by`)) - LENGTH(REPLACE(CONCAT(word,description,`usage`,`by`), 'Miley', '')))/LENGTH('Miley')),0)
        + IFNULL(ROUND((LENGTH(CONCAT(word,description,`usage`,`by`)) - LENGTH(REPLACE(CONCAT(word,description,`usage`,`by`), 'Cyrus', '')))/LENGTH('Cyrus')),0)
as `match`
FROM definition d
HAVING `match` > 0
ORDER BY `match` DESC,votecount DESC;

推荐答案

不确定这是否是更好的方法，但这是我的方法:

Not sure if it is a better way but this is how I would do it:

SELECT d.id, d.word, LEFT(d.description, 100) description,
  COALESCE(sum(v.vote), 0) votecount,
    (CONCAT(word, description, `usage`) LIKE '%George%')
  + (CONCAT(word, description, `usage`) LIKE '%Tabuki%')
  + (CONCAT(word, description, `usage`) LIKE '%Street%')
  + (CONCAT(word, description, `usage`) LIKE '%Fighter%')
  + (CONCAT(word, description, `usage`) LIKE '%Miley%')
  + (CONCAT(word, description, `usage`) LIKE '%Cyrus%') `match`
FROM definition d
LEFT JOIN vote v ON v.definition_id = d.id
GROUP BY d.id
HAVING `match` > 0
ORDER BY `match` DESC, votecount DESC

如果字符串足够长，则重复连接可能比创建派生表花费更多的时间(不太可能，但是值得尝试一下):

If the strings are long enough maybe the repeated concatenation might take more time than creating a derived table (unlikely, but it's worth giving it a try):

SELECT id, word, description, votecount,
    (fullDesc LIKE '%George%')
  + (fullDesc LIKE '%Tabuki%')
  + (fullDesc LIKE '%Street%')
  + (fullDesc LIKE '%Fighter%')
  + (fullDesc LIKE '%Miley%')
  + (fullDesc LIKE '%Cyrus%') `match`
FROM (
  SELECT d.id, d.word, LEFT(d.description, 100) description,
    COALESCE(sum(vote), 0) votecount, CONCAT(word, description, `usage`) fullDesc
  FROM definition d
  LEFT JOIN vote v ON v.definition_id = d.id
  GROUP BY d.id
) s
HAVING `match` > 0
ORDER BY `match` DESC, votecount DESC

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