asp.net - 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 场比赛 - 如果第 2 场和第 3 场比赛，则得分为 1400

第 2 场 - 如果第 1 场和第 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