34,837
社区成员




create table tb(id int,name varchar(10))
insert tb
select 1,'a' union all
select 2,'b' union all
select 5,'c' union all
select 6,'d' union all
select 7,'e' union all
select 8,'f' union all
select 9,'g'
go
declare @s varchar(1000)
set @s='2,3,4,5,7,9'
set @s = replace(@s,',', ' as id union all select ' )
exec('select * from (select '+@s+') aa
where not exists(select 1 from tb t where aa.id = t.id and t.id in(2,3,4,5,7,9))')
drop table tb
/*
id
-----------
3
4
*/
--结果
在@t表中没有的 1 a
在@t表中没有的 6 d
在@t表中没有的 8 f
在@s表中没有的 3 NULL
在@s表中没有的 4 NULL
declare @s table(id int,name varchar(10))
insert @s
select 1,'a' union all
select 2,'b' union all
select 5,'c' union all
select 6,'d' union all
select 7,'e' union all
select 8,'f' union all
select 9,'g'
--创建临时表记录你要查询产品的ID
declare @t table(id int)
insert @t
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 7 union all
select 9
select bz='在@t表中没有的',a.id,a.name from @s a where not exists(select id from @t where id=a.id)
union all
select bz='在@s表中没有的',b.id ,null from @t b where not exists(select id from @s where id=b.id)
create table tb(产品编号 int)
insert into tb values(2)
insert into tb values(5)
insert into tb values(7)
insert into tb values(9)
go
select 产品编号 from
(
select 2 产品编号 union select 3 union select 4
union select 5 union select 7 union select 9
) t
where 产品编号 not in
(
select distinct 产品编号 from tb where 产品编号 in (2,3,4,5,7,9)
)
drop table tb
/*
产品编号
-----------
3
4
(所影响的行数为 2 行)
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO
--3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
declare @Product table(id int,name varchar(10))
insert @Product
select 1,'a' union all
select 2,'b' union all
select 5,'c' union all
select 6,'d' union all
select 7,'e' union all
select 8,'f' union all
select 9,'g'
declare @s nvarchar(100)
set @s='2,3,4,5,7,9'
select
a.*
from
f_splitSTR(@s,',') a
left join
@Product b on a.Col=b.ID
where
b.ID is null
(所影响的行数为 7 行)
col
----------------------------------------------------------------------------------------------------
3
4
(所影响的行数为 2 行)
----创建产品库
declare @Product table(id int,name varchar(10))
insert @Product
select 1,'a' union all
select 2,'b' union all
select 5,'c' union all
select 6,'d' union all
select 7,'e' union all
select 8,'f' union all
select 9,'g'
----将要查询的ID添加到临时表中
declare @t table(id int)
insert @t
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 7 union all
select 9
----查询
select a.* from @t as a LEFT JOIN @Product as b on a.id = b.id where b.id IS NULL
/*结果
id
-----------
3
4
*/
create table tb (id int,name varchar(8000))
insert into tb select 2,'aaa'
insert into tb select 5,'aaa'
insert into tb select 9,'aaa'
select tempid from (
select * from
(select 1 as tempid
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) a
left join
tb b on a.tempid=b.id )c where c.id is null
select a.id from
(select id=2
union all select 3
union all select 4
union all select 5
union all select 7
union all select 9) a
left join join
(select distinct 产品编号 id from tb where 产品编号 in(2,3,4,5,7,9)) b
on a.id=b.id
where b.id is null
select * from 产品库 where 产品编号 in (2,3,4,5,7,9)
select * from 产品库 where 产品编号 not in (2,3,4,5,7,9)