上个帖子问题不清楚，重新发，请见谅，分统一结
现有的标结构为 docid,doctitle,docname,docdesc,docsmark
1, hello,a,b,c
2, nihao,,w,e,f
3,你好,g,t,h
...
1200,喂,o,p,u
如果输入参数 docid 的串为 1,3,3,1200,2,1
那么返回的结果为：
1, hello,a,b,c
3,你好,g,t,h
3,你好,g,t,h
1200,喂,o,p,u
2, nihao,,w,e,f
1, hello,a,b,c
一是数据需要重复出现， 二是结果的排列按照 传入的参数串 数字排列
上个帖子问题不清楚，重新发，请见谅
------解决方案--------------------
- SQL code
create table tb(docid int,doctitle nvarchar(10))insert into tb select 1,'hello'insert into tb select 2,'nihao'insert into tb select 3,'你好'insert into tb select 120,'你好aaaa'declare @str varchar(2000)='1,3,3,120,2,1'set @str='select a.* from tb a,(select '+REPLACE(@str,',',' as id union all select ') +') b where a.docid=b.id'print @strexec (@str)/*docid doctitle----------- ----------1 hello3 你好3 你好120 你好aaaa2 nihao1 hello
------解决方案--------------------
- SQL code
use tempdb go --测试数据 declare @s varchar(1000) set @s='ak47,mp5,1,23' /*要求输出结果 S ---- ak47 mp5 1 23 */ --3种方法对比: --1.[朴实]动态Exec方法: declare @s1 varchar(1000) set @s1=right(replace(',[email protected],',',''' as S union select '''),len(replace(',[email protected],',',''' as S union select '''))-12)+'''' exec(@s1) --2.[变通]表交叉方法: select replace(reverse((left(s,charindex(',',s)))),',','') as S from( select r,reverse(left(@s,r))+',' as s from( select (select count(*) from sysobjects where name<=t.name ) as r from sysobjects t )a where r<=len(@s) and left(@s+',',r+1) like '%,' )t order by r --3.[高级]XML方法: DECLARE @idoc int; DECLARE @doc xml; set @doc=cast('<Root><item><S>'+replace(@s,',','</S></item><item><S>')+'</S></item></Root>' as xml) EXEC sp_xml_preparedocument @Idoc OUTPUT, @doc SELECT * FROM OPENXML (@Idoc, '/Root/item',2) WITH ( [S] varchar(10) )