按最大条件匹配排序

发布于 2024-09-10 18:06:03 字数 367 浏览 3 评论 0原文

请帮助我创建一个包含 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

野の 2024-09-17 18:06:03
SELECT *
  FROM (SELECT (CASE WHEN cond1 THEN 1 ELSE 0 END +
                CASE WHEN cond2 THEN 1 ELSE 0 END +
                CASE WHEN cond2 THEN 1 ELSE 0 END +
                ...
                CASE WHEN cond10 THEN 1 ELSE 0 END
               ) AS numMatches,
               other_columns...
          FROM mytable
       ) xxx
 WHERE numMatches > 0
 ORDER BY numMatches DESC
SELECT *
  FROM (SELECT (CASE WHEN cond1 THEN 1 ELSE 0 END +
                CASE WHEN cond2 THEN 1 ELSE 0 END +
                CASE WHEN cond2 THEN 1 ELSE 0 END +
                ...
                CASE WHEN cond10 THEN 1 ELSE 0 END
               ) AS numMatches,
               other_columns...
          FROM mytable
       ) xxx
 WHERE numMatches > 0
 ORDER BY numMatches DESC
茶色山野 2024-09-17 18:06:03

编辑:这个答案是在用具体示例修改问题之前发布的。 Marcelo 的解决方案解决了实际问题。另一方面,我的答案是优先考虑特定领域的匹配。


您可能想尝试如下操作,在 ORDER BY 子句中使用与 WHERE 子句中相同的表达式:

SELECT    *
FROM      your_table
WHERE     field_1 = 100 OR
          field_2 = 200 OR
          field_3 = 300
ORDER BY  field_1 = 100 DESC,
          field_2 = 200 DESC,
          field_3 = 300 DESC;

我最近在 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 your WHERE clause:

SELECT    *
FROM      your_table
WHERE     field_1 = 100 OR
          field_2 = 200 OR
          field_3 = 300
ORDER BY  field_1 = 100 DESC,
          field_2 = 200 DESC,
          field_3 = 300 DESC;

I've recently answered a similar question on Stack Overflow which you might be interested in checking out:

泛滥成性 2024-09-17 18:06:03

有很多可能的选项/答案。最佳答案取决于数据大小、非功能要求等。

也就是说,我要做的是这样的(易于阅读/调试):

  Select * from 
    (Select *, iif(condition1 = bla, 1, 0) as match1, ..... , match1+match2...+match10 as totalmatchscore from sourcetable
    where 
      condition1 = bla or
      condition2 = bla2
      ....) as helperquery
    order by helperquery.totalmatchscore desc

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):

  Select * from 
    (Select *, iif(condition1 = bla, 1, 0) as match1, ..... , match1+match2...+match10 as totalmatchscore from sourcetable
    where 
      condition1 = bla or
      condition2 = bla2
      ....) as helperquery
    order by helperquery.totalmatchscore desc
弥枳 2024-09-17 18:06:03

我无法让它在 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

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文