1:create table ##
(
id int
)insert ## select 12
union all select 1
union all select 7
union all select 4
union all select 5
godeclare @s1 varchar(20)
declare @s2 varchar(1000)
set @s1 = '12,4,7,5'
set @s1 = ''',' + @s1 + ','''
set @s2 = '
select *
from ##
where charindex('',''+ convert(varchar(10),id) + '','','+@s1+') > 0
order by charindex('',''+ convert(varchar(10),id) + '','','+@s1+')
'print @s2
exec(@s2)godrop table ##
go
(
id int
)insert ## select 12
union all select 1
union all select 7
union all select 4
union all select 5
godeclare @s1 varchar(20)
declare @s2 varchar(1000)
set @s1 = '12,4,7,5'
set @s1 = ''',' + @s1 + ','''
set @s2 = '
select *
from ##
where charindex('',''+ convert(varchar(10),id) + '','','+@s1+') > 0
order by charindex('',''+ convert(varchar(10),id) + '','','+@s1+')
'print @s2
exec(@s2)godrop table ##
go
(
id int
)insert ## select 12
union all select 1
union all select 7
union all select 4
union all select 5
goselect * from ##
where id in (12,4,7)
order by case id when 12 then 1
when 4 then 2
else 3 end
drop table ##
id
-----------
12
4
7(3 行受影响)
function ArrayToTable(@arrayList varchar(2000),@split varchar(2))
returns @result table(subscript int,value char(200))
as
begin
declare @i int,
@index int
set @i = 0
set @index = charindex(@split,@arrayList)
while(@index <> 0)
begin
insert into @result(subscript,value)
values(@i,substring(@arrayList,1,@index-1))
set @arrayList = stuff(@arrayList,1,@index,'')
set @index = charindex(@split,@arrayList)
set @i = @i+1
end
insert into @result(subscript,value)
values(@i,@arrayList)
return
end这是一个把单独的字符串变成表:select * from dbo.ArrayToTable('1,3,4,44,23,65',',')
改写一下可以实现问题2了