網(wǎng)站運(yùn)營(yíng)seo文章大全提供全面的站長(zhǎng)運(yùn)營(yíng)經(jīng)驗(yàn)及seo技術(shù)!
原貼地址:http://community.csdn.net/expert/topic/3693/3693091.xml?temp=.6086542
測(cè)試table
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'
要求按指定的id順序(比如2,1,4,3)排列獲取table1的數(shù)據(jù)
方法1:使用union all,但是有256條數(shù)據(jù)的限制
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
方法2:在order by中使用case when
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)
*以上兩種方法適合在數(shù)據(jù)量非常小的情況下使用
方法3:使用游標(biāo)和臨時(shí)表
先建一個(gè)輔助表,里面你需要的順序插入,比如2,1,4,3
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 --定義游標(biāo)
declare c_test cursor for
select id from t1
select * into #tmp from table1 where 1=2 --構(gòu)造臨時(shí)表的結(jié)構(gòu)
open c_test
fetch next from c_test
into @id
while @@fetch_status = 0
begin
--按t1中的id順序插數(shù)據(jù)到臨時(shí)表
insert into #tmp select id,name from table1 where [email protected]
fetch next from c_test into @id
end
close c_test
deallocate c_test
*該方法適合需要按照輔助表的順序重排table的順序時(shí)使用
(即輔助表已經(jīng)存在的情況)
方法4:分割字符串參數(shù)
select * into #tmp from table1 where 1=2 --構(gòu)造臨時(shí)表的結(jié)構(gòu)
declare @str varchar(300),@id varchar(300),@m int,@n int
set @str='2,1,4,3,' ---注意后面有個(gè)逗號(hào)
set @m=charindex(',',@str)
set @n=1
while @m>0
begin
set @id=substring(@str,@n,@[email protected])
--print @id
insert into #tmp select id,name from table1 where id=convert(int,@id)
set @[email protected]+1
set @m=charindex(',',@str,@n)
end
*該方法比較有通用性
測(cè)試結(jié)果
id name
----------- ----
2 r
1 q
4 5
3 3
（所影響的行數(shù)為 4 行）
