SQL顺序结果按匹配的字段数

Question

这里有点复杂的SQL问题。

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

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，从而选择与任何字段匹配的所有记录。

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

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

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

比赛1将在结果顶部。

对于使用SQL实现此目标的任何帮助，我们将不胜感激。

Answer 1

尝试：

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