oracle xunhuan

²âÊÔ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µÄÊý¾Ý
·½·¨1:ʹÓÃunion all,µ«ÊÇÓÐ256ÌõÊý¾ÝµÄÏÞÖÆ
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)
*ÒÔÉÏÁ½ÖÖ·½·¨ÊʺÏÔÚÊý¾ÝÁ¿·Ç³£Ð¡µÄÇé¿öÏÂʹÓÃ
·½·¨3:ʹÓÃÓαêºÍÁÙʱ±í
ÏȽ¨Ò»¸ö¸¨Öú±í,ÀïÃæÄãÐèÒªµÄ˳Ðò²åÈë,±ÈÈç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 --¶¨ÒåÓαê
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µÄ˳ÐòʱʹÓÃ
(¼´¸¨Öú±íÒѾ­´æÔÚµÄÇé¿ö)
·½·¨4:·Ö¸î×Ö·û´®²ÎÊý
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
*¸Ã·½·¨±È½ÏÓÐͨÓÃÐÔ
²âÊÔ½á¹û
id name
----------- ----
2 r
1 q
4 5
3 3