SQL Server 2005:如何在使用 LIKE 运算符时对记录集进行排序

发布于 2024-10-21 13:09:06 字数 877 浏览 2 评论 0原文

这是我的问题。我有一个像这样的 select 语句:

select * from mytable where mycol like '%finance%'

结果看起来像这样:

id  mycol 
16  the finance department 
 8  i like the finance people 
43  chief finance officer 
22  finance 
68  finance trading

我希望订单像这样出来:

id  mycol 
22  finance 
68  finance trading 
43  chief finance officer 
16  the finance department 
 8  i like the finance people

本质上,要使用选择查询:

where mycol = 'finance'

then

where mycol = 'finance%'

then

where mycol = '%finance%'

1)我不能使用 id 列进行排序目的。

2)我的查询比这复杂得多(它是使用 SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY t.category, t.sequence) 对大型数据库上的记录集进行分页的一部分...

我想我可能会需要使用 UNION 或 RANK() 或其他东西,但我现在已经脱离了我的联盟,非常感谢任何指导:)

Here is my problem. I have a select statement something like this:

select * from mytable where mycol like '%finance%'

The results look something like this:

id  mycol 
16  the finance department 
 8  i like the finance people 
43  chief finance officer 
22  finance 
68  finance trading

I would like to have the order come out like so:

id  mycol 
22  finance 
68  finance trading 
43  chief finance officer 
16  the finance department 
 8  i like the finance people

In essence, to have the select query use:

where mycol = 'finance'

then

where mycol = 'finance%'

then

where mycol = '%finance%'

1) I cannot use the id column for ordering purposes.

2) My query is far more complex than this (it's part of a paging a recordset on a large db using SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY t.category, t.sequence) ...

I'm thinking I might need to use UNION or RANK() or something, but I'm getting out of my league now. Any guidance greatly appreciated :)

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

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

发布评论

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

评论(1

看透却不说透 2024-10-28 13:09:06

一个明显的解决方案:

Select Id, mycol
    , Case
        When mycol = 'finance' Then 1 
        When mycol Like 'finance%' Then 2
        When mycol Like '%finance%' Then 3
        End As Rnk
From MyTable
Where mycol Like '%finance%' 
Order By Rnk

当然,这对于前导和尾随通配符来说效果不佳。另一种方法是使用全文搜索,这将为您提供相关性排名。

CONTAINSTABLE (Transact-SQL)

One obvious solution:

Select Id, mycol
    , Case
        When mycol = 'finance' Then 1 
        When mycol Like 'finance%' Then 2
        When mycol Like '%finance%' Then 3
        End As Rnk
From MyTable
Where mycol Like '%finance%' 
Order By Rnk

Granted, this won't perform well with the leading and trailing wildcard. Another way would be to use full-text search which will give you a relevancy rank.

CONTAINSTABLE (Transact-SQL)

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