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

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

原贴地址:http://community.csdn.net/Expert/topic/3693/3693091.xml?temp=.6086542

测试table
createtabletable1(idint,namechar)
insertintotable1
select1,'q'
unionallselect2,'r'
unionallselect3,'3'
unionallselect4,'5'

要求 按指定的id顺序(比如2,1,4,3)排列获取table1的数据

方法1:使用unionall,但是有256条数据的限制
selectid,namefromtable1whereid=2
unionall
selectid,namefromtable1whereid=1
unionall
selectid,namefromtable1whereid=4
unionall
selectid,namefromtable1whereid=3

方法2:在orderby中使用casewhen
selectid,namefromtwhereidin(2,1,4,3)
orderby(caseid
                     when2then'A'
                     when1then'B'
                     when4then'C'
                     when3then'D'end)

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

方法3:使用游标和临时表
先建一个辅助表,里面你需要的顺序插入,比如2,1,4,3
createtablet1(idint)
insertintot1
select2
unionallselect1
unionallselect4
unionallselect3

declare@idint                             --定义游标
declarec_testcursorfor
selectidfromt1                       

select*into#tmpfromtable1where1=2    --构造临时表的结构

OPENc_test

FETCHNEXTFROMc_test
INTO@id
WHILE@@FETCH_STATUS=0
BEGIN
--按t1中的id顺序插数据到临时表
insertinto#tmpselectid,namefromtable1whereid=@id  
FETCHNEXTFROMc_test INTO@id
End
Closec_test                  
deallocatec_test

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

方法4:分割字符串参数
select*into#tmpfromtable1where1=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 
      insertinto#tmpselectid,namefromtable1whereid=convert(int,@id)
      set @n=@m+1 
      set @m=CHARINDEX(',',@str,@n) 
END 上一页 
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>