通过在字符串中出现一组关键字来对sql结果进行排序 [英] Order sql result by occurrence of a set of keywords in a string
问题描述
对于每行,我想将每个描述的相关性与未定义数量的关键字进行比较.我知道"THEN +1"无效,但是我想得出这个结果(...为每个存在的关键字增加一个数字(每行从0开始)
For each rows, I want to get the relevance of each description compared to an undefined number of keywords. I know that "THEN +1" does not work, but I would like to come to this result (...to have a number (starting from 0 each rows) that is incremented for each keyword present)
SELECT *,
(CASE description LIKE '%keyword1%' THEN +1
description LIKE '%keyword2%' THEN +1
(...)
ELSE 0
END) as relevance_description
FROM (...)
ORDER BY relevance_description DESC
因此,如果描述包含"keyword1"和"keyword2",则此行的relevance_description应该为2.
So, if a description contains "keyword1" and "keyword2", relevance_description should be 2 for this row.
推荐答案
您可以使用单独的子句执行此操作,并将它们添加在一起:
You can do this with separate clauses, and add them together:
SELECT *,
((CASE description LIKE '%keyword1%' THEN 1 else 0 end) +
(case description LIKE '%keyword2%' THEN 1 else 0 end) +
. . .
) as relevance_description
FROM (...)
ORDER BY relevance_description DESC;
在某些数据库中,布尔值被视为整数,因此您可以编写:
In some databases, booleans are treated as integers, so you could just write:
SELECT *,
((description LIKE '%keyword1%') +
(description LIKE '%keyword2%') +
. . .
) as relevance_description
FROM (...)
ORDER BY relevance_description DESC;
这篇关于通过在字符串中出现一组关键字来对sql结果进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!