SQL顺序结果按匹配的字段数 [英] SQL order results by number of fields matched

问题描述

这里有一个复杂的SQL问题.

Bit of a complicated SQL question here.

我目前有一个与多个字段匹配的SELECT语句，就像这样.

I currently have a SELECT statement which matches several fields, like this.

SELECT field1, field2, field3, field4, field5
FROM table
WHERE field1 = 'variable 1'  
AND field2 = 'variable 2' 
AND field3 = 'variable 3' 
AND field4 = 'variable 4' 
AND field5 = 'variable 5' 

我想修改该语句，使其使用OR而不是AND，以便选择与任何字段匹配的所有记录.

I would like to modify the statement so that it uses OR's instead of AND's so that it selects all records which match any of the fields.

下一步是使用评分系统对结果进行排名.

The next step is to rank the results using a scoring system.

If field 1 was matched then 1000 is added to the score
If field 2 was matched then 800 is added to the score
If field 3 was matched then 600 is added to the score
If field 4 was matched then 10 is added to the score
If field 5 was matched then 1 is added to the score

所以...

匹配1-如果field2和field 3匹配，那么得分将为1400

Match 1 - If field2 and field 3 match then the score would be 1400

匹配2-如果field1和field 4匹配，那么得分将为1010

Match 2 - If field1 and field 4 match then the score would be 1010

匹配1将位于结果的顶部.

Match 1 would be at the top of the results.

对于使用某些SQL实现这一目标的任何帮助，我们将不胜感激.

Any help with some SQL to achieve this would really be appreciated.

推荐答案

尝试:

SELECT
    ....
    FROM ....

    ORDER BY
        (CASE WHEN field1 = 'variable 1' THEN 1000 ELSE 0 END
        +CASE WHEN field2 = 'variable 2' THEN 800 ELSE 0 END
        +CASE WHEN field3 = 'variable 3' THEN 600 ELSE 0 END
        +CASE WHEN field4 = 'variable 4' THEN 10 ELSE 0 END
        +CASE WHEN field5 = 'variable 5' THEN 1 ELSE 0 END
        ) DESC

这篇关于SQL顺序结果按匹配的字段数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！