这里有一个复杂的 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
So...
匹配 1 - 如果字段 2 和字段 3 匹配,则分数将为 1400
匹配 2 - 如果字段 1 和字段 4 匹配,则分数将为 1010
第 1 场比赛将位于结果的顶部。
任何有关 SQL 的帮助来实现这一点将不胜感激。
TRY:
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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)