Sql server按值排序，而不是按字段名排序

suppose my table structure is like

假设我的表结构是这样的

ID       OEReference
---      ------------
1         00000634B9
2         00000634B6
3         0005000053
4         0002855071
5         0000940148
6         0001414825
7         00000634B9

i want that they way i supply OEReference that order should maintain in output. my sql is like

我想让他们知道我所提供的所有信息，即订单应该在产出中保持。我的sql是像

Select * from mytable where OEReference in ('00000634B9','0001414825','00000634B6')

the above statement did not return resultset according to the order of IN clause. i know that it is not possible by ORDER BY CLAUSE

上述声明并没有按照条款的顺序返回结果集。我知道按条款订购是不可能的

how can i do it with simple sql statement in sql server. thanks

如何在sql server中使用简单的sql语句。谢谢

2 个解决方案

#1

You could use a temporary table as a filter. Aninner join will enforce the filter, and you can sort on the identity column:

您可以使用临时表作为过滤器。Aninner join将执行筛选，您可以在identity列上进行排序:

declare @filter table (id int identity, ref varchar(50))
insert @filter values ('00000634B9')
insert @filter values ('0001414825')
insert @filter values ('00000634B6')

select  *
from    YourTable yt
join    @filter filter
on      filter.ref = yt.OEReference
order by
        filter.id

#2

Please here is my solution for you:

这是我的解决方案:

SELECT [id], [OEReference]      

  FROM [Tbl]

  where [OEReference] in ('002', '001')

  order by case [OEReference]
      when '002' then 1
      when '001' then 2
      end

Please note: this can decrease performance of your server. It depends on how many rows in table you have. However, you can easily add index for OEReference. Off course, you should generate such query dynamically. My solution is not close ideal. Maybe you found it useful for you.

请注意:这会降低服务器的性能。这取决于表中有多少行。但是，您可以很容易地添加OEReference索引。当然，您应该动态地生成这样的查询。我的解决方法不是很理想。也许你觉得它对你有用。

Happy coding!

编码快乐!

#1