按最大条件匹配排序
请帮助我创建一个包含 10 个“where”子句的选择查询,顺序应如下所示: 结果应按照从匹配最多的关键字(条件)到最少匹配的顺序显示。
注意:所有 10 个条件均带有“OR”。
请帮助我创建此查询。 我正在使用 ms-sql server 2005
像:
Select *
from employee
where empid in (1,2,4,332,434)
or empname like 'raj%'
or city = 'jodhpur'
or salary >5000
在上面的查询中,所有匹配最大条件的记录应该位于顶部,较少匹配条件的记录应该位于底部。
Please help me to create a select query which contains 10 'where' clause and the order should be like that:
the results should be displayed in order of most keywords(where conditions) matched down to least matched.
NOTE: all 10 condition are with "OR".
Please help me to create this query.
i am using ms-sql server 2005
Like:
Select *
from employee
where empid in (1,2,4,332,434)
or empname like 'raj%'
or city = 'jodhpur'
or salary >5000
In above query all those record which matches maximum conditions should be on top and less matching condition record should be at bottom.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
编辑:这个答案是在用具体示例修改问题之前发布的。 Marcelo 的解决方案解决了实际问题。另一方面,我的答案是优先考虑特定领域的匹配。
您可能想尝试如下操作,在
ORDER BY
子句中使用与WHERE
子句中相同的表达式:我最近在 Stack Overflow 上回答了类似的问题您可能有兴趣查看:
EDIT: This answer was posted before the question was modified with a concrete example. Marcelo's solution addresses the actual problem. On the other hand, my answer was giving priority to matches of specific fields.
You may want to try something like the following, using the same expressions in the
ORDER BY
clause as in yourWHERE
clause:I've recently answered a similar question on Stack Overflow which you might be interested in checking out:
有很多可能的选项/答案。最佳答案取决于数据大小、非功能要求等。
也就是说,我要做的是这样的(易于阅读/调试):
There are many options/answers possible. Best answer depends on size of the data, non-functional requirements, etc.
That said, what I would do is something like this (easy to read / debug):
我无法让它在 Oracle 上为我工作。
如果使用 Oracle,那么这个按最大条件匹配排序是一个很好的解决方案。
利用语言特征时的大小写
I could not get this to work for me on Oracle.
If using oracle, then this Order by Maximum condition match is a good solution.
Utilizes the case when language feature