SQL Server 2005:如何在使用 LIKE 运算符时对记录集进行排序
这是我的问题。我有一个像这样的 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 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
一个明显的解决方案:
当然,这对于前导和尾随通配符来说效果不佳。另一种方法是使用全文搜索,这将为您提供相关性排名。
CONTAINSTABLE (Transact-SQL)
One obvious solution:
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)