### 如何按指定的顺序获取数据

http://community.csdn.net/Expert/TopicView.asp?id=3724743

create table table1 (id int,name char)
insert into table1
select 1,'q'
union all select 2,'r'
union all select 3,'3'
union all select 4,'5'

select id,name from table1 where id=2
union all
select id,name from table1 where id=1
union all
select id,name from table1 where id=4
union all
select id,name from table1 where id=3

select id ,name from t where id in (2,1,4,3)
order by (case id
when 2 then 'A'
when 1 then 'B'
when 4 then 'C'
when 3 then 'D' end)

*以上两种方法适合在数据量非常小的情况下使用

create table t1(id int)
insert into t1
select 2
union all select 1
union all select 4
union all select 3

declare @id int                              --定义游标
declare c_test cursor for
select id from t1

select * into #tmp from table1 where 1=2     --构造临时表的结构

OPEN c_test

FETCH NEXT FROM c_test
INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
--按t1中的id顺序插数据到临时表
insert into #tmp select id,name from table1 where id=@id
FETCH NEXT FROM c_test  INTO @id
End
Close c_test
deallocate c_test

*该方法适合需要按照辅助表的顺序重排table的顺序时使用
(即辅助表已经存在的情况)

select * into #tmp from table1 where 1=2 --构造临时表的结构

declare  @str  varchar(300),@id  varchar(300),@m  int,@n  int
set  @str='2,1,4,3,'      ---注意后面有个逗号
set  @m=CHARINDEX(',',@str)
set  @n=1
WHILE  @m>0
BEGIN
set  @id=substring(@str,@n,@m-@n)
--print  @id
insert into #tmp select id,name from table1 where id=convert(int,@id)
set  @n=@m+1
set  @m=CHARINDEX(',',@str,@n)
END

select * from t1  order by charindex(convert(varchar(2),id),'2,1,4,3')

id          name
----------- ----
2           r
1           q
4           5
3           3

（所影响的行数为 4 行）

0 0