asp.net – 按顺序匹配的字段数量生成SQL顺序

发布时间：2020-12-16 09:16:16 所属栏目：asp.Net 来源：网络整理

导读：这里有一个复杂的SQL问题. 我目前有一个SELECT语句,它匹配几个字段,像这样. SELECT field1,field2,field3,field4,field5FROM tableWHERE field1 = 'variable 1' AND field2 = 'variable 2' AND field3 = 'variable 3' AND field4 = 'variable 4' AND field5

这里有一个复杂的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都非常感激.

解决方法

尝试：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!