给出一批id,如何查出不在表中的id
比如我有4个id 1,2,3,4
id为2,3,4的数据在数据表里存在
不存在id为1的数据
怎么写sql返回的id是1
我写的sql是:
SELECT id from rent r1 where id in( 1,2,3,4) and EXISTS (SELECT id from rent r2 where r1.id = r2.id is null)
但是查不出来啊.
[解决办法]
create table t1( id int)insert into t1 (id) values (2),(3),(4)select * from t1create 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 '+@strexec (@str)select * from #t where id not in (select id from t1)drop table #t---------------id1
[解决办法]
--参考拆分表:--> --> (Roy)生成測試數據 if not object_id('Tab') is null drop table TabGoCreate table Tab([Col1] int,[COl2] nvarchar(5))Insert Tabselect 1,N'a,b,c' union allselect 2,N'd,e' union allselect 3,N'f'Go--SQL2000用辅助表:if object_id('Tempdb..#Num') is not null drop table #Numgoselect top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns bSelect a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) from Tab a,#Num bwhere 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.Col2from (select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)aouter apply (select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))bSQL05用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 Tabunion allselect 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 a1 b1 c2 d2 e3 f*/
[解决办法]
参考这个:
/*标题：分解字符串并查询相关数据作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 时间：2008-03-18地点：广东深圳说明：通过使用函数等方法分解字符串查询相关数据。问题：通过分解一个带某种符号分隔的字符串在数据库中查找相关数据。例如 @str = '1,2,3',查询下表得到记录1,4,5,6ID TypeID1 1,2,3,4,5,6,7,8,9,10,11,122 2,3 3 3,7,8,9 4 2,6 5 4,56 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 + ',') > 0select * 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 + ',') > 0select * from tb where ',' + typeid + ',' like '%,' + left(@str , charindex(',' , @str) - 1) + ',%' or ',' + typeid + ',' like '%,' + substring(@str , charindex(',' , @str) + 1 , len(@str)) + ',%'/*ID TypeID ----------- ------------------------------ 1 1,2,3,4,5,6,7,8,9,10,11,122 2,34 2,6（所影响的行数为 3 行）*/---------------------------------------------如果包含三个或四个，用PARSENAME函数来处理.declare @str as varchar(30)set @str = '1,2,3,4'select * from tb where charindex(',' + parsename(replace(@str , ',' , '.') , 4) + ',' , ',' + typeid + ',') > 0 or charindex(',' + parsename(replace(@str , ',' , '.') , 3) + ',' , ',' + typeid + ',') > 0 or charindex(',' + parsename(replace(@str , ',' , '.') , 2) + ',' , ',' + typeid + ',') > 0 or charindex(',' + parsename(replace(@str , ',' , '.') , 1) + ',' , ',' + typeid + ',') > 0 select * from tb where ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 4) + ',%' or ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 3) + ',%' or ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 2) + ',%' or ',' + typeid + ',' like '%,' + parsename(replace(@str , ',' , '.') , 1) + ',%'/*ID TypeID ----------- ------------------------------ 1 1,2,3,4,5,6,7,8,9,10,11,122 2,33 3,7,8,94 2,65 4,5（所影响的行数为 5 行）*/-----------------------------------------如果超过四个，则只能使用函数或动态SQL来分解并查询数据。/*名称：fn_split函数.功能：实现字符串分隔功能的函数*/create function dbo.fn_split(@inputstr varchar(8000), @seprator varchar(10))returns @temp table (a varchar(200))as begin declare @i int set @inputstr = rtrim(ltrim(@inputstr)) set @i = charindex(@seprator , @inputstr) while @i >= 1 begin insert @temp values(left(@inputstr , @i - 1)) set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i) set @i = charindex(@seprator , @inputstr) end if @inputstr <> '\' insert @temp values(@inputstr) return endgo--调用declare @str as varchar(30)set @str = '1,2,3,4,5'select distinct m.* from tb m,(select * from dbo.fn_split(@str,',')) nwhere charindex(',' + n.a + ',' , ',' + m.typeid + ',') > 0drop table tbdrop function dbo.fn_split /*ID TypeID ----------- ------------------------------ 1 1,2,3,4,5,6,7,8,9,10,11,122 2,33 3,7,8,94 2,65 4,5（所影响的行数为 5 行）*/--------------------------------------------使用动态SQL的语句。declare @str varchar(200)declare @sql as varchar(1000)set @str = '1,2,3,4,5'set @sql = 'select ''' + replace(@str , ',' , ''' as id union all select ''')set @sql = @sql + ''''set @sql = 'select distinct a.* from tb a , (' + @sql + ') b where charindex(' + ''','' + b.id + ' + ''',''' + ' , ' + ''','' + a.typeid + ' + ''',''' + ') > 0 'exec (@sql)/*ID TypeID ----------- ------------------------------ 1 1,2,3,4,5,6,7,8,9,10,11,122 2,33 3,7,8,94 2,65 4,5（所影响的行数为 5 行）*/