日期:2014-05-18 浏览次数:20474 次
create table t1 ( id int ) insert into t1 (id) values (2),(3),(4) select * from t1 create table #t ( id int ) declare @str varchar(4000)='1,2,3,4' set @str=REPLACE(@str,',',' union all select ') set @str='insert into #t select '+@str exec (@str) select * from #t where id not in (select id from t1) drop table #t --------------- id 1
------解决方案--------------------
--参考 拆分表: --> --> (Roy)生成測試數據 if not object_id('Tab') is null drop table Tab Go Create table Tab([Col1] int,[COl2] nvarchar(5)) Insert Tab select 1,N'a,b,c' union all select 2,N'd,e' union all select 3,N'f' Go --SQL2000用辅助表: if object_id('Tempdb..#Num') is not null drop table #Num go select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b Select a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) from Tab a,#Num b where charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=',' --2000不使用辅助表 Select a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number) from Tab a join master..spt_values b ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2) where substring(','+a.COl2,b.number,1)=',' SQL2005用Xml: select a.COl1,b.Col2 from (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a outer apply (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b SQL05用CTE: ;with roy as (select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab union all select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>'' ) select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0) 生成结果: /* Col1 COl2 ----------- ----- 1 a 1 b 1 c 2 d 2 e 3 f */
------解决方案--------------------
参考这个:
/* 标题:分解字符串并查询相关数据 作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 时间:2008-03-18 地点:广东深圳 说明:通过使用函数等方法分解字符串查询相关数据。 问题:通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。 例如 @str = '1,2,3',查询下表得到记录1,4,5,6 ID TypeID 1 1,2,3,4,5,6,7,8,9,10,11,12 2 2,3 3 3,7,8,9 4 2,6 5 4,5 6 6,7 */ ----------------------------- create table tb (ID int , TypeID varchar(30)) insert into tb values(1 , '1,2,3,4,5,6,7,8,9,10,11,12') insert into tb values(2 , '2,3') insert into tb values(3 , '3,7,8,9') insert into tb values(4 , '2,6') insert into tb values(5 , '4,5') insert into tb values(6 , '6,7') go ----------------------------- --如果仅仅是一个,如@str = '1'. declare @str as varchar(30) set @str = '1' select * from tb where charindex(',' + @str + ',' , ',' + TypeID + ',') > 0 select * from tb where ',' + TypeID + ',' like '%,' + @str + ',%' /* ID TypeID ----------- ------------------------------ 1 1,2,3,4,5,6,7,8,9,10,11,12 (所影响的行数为 1 行) */ ----------------------------- --如果包含两个,如@str = '1,2'. declare @str as varchar(30) set @str = '1,2' select * from tb where charindex(',' + left(@str , charindex(',' , @str) - 1) + ',' , ',' + typeid + ',') > 0 or charindex(',' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',' , ',' + typeid + ',') > 0 select * from tb where ',' + typeid + ',' like '%,' + left(@str , charindex(',' , @str) - 1) + ',%' or ',' + typeid +