--测试环境 declare @Ta table(k1 varchar(10),k2 varchar(10),e3 varchar(10)) insert into @Ta select '1','1','1' union all select '1','2','3' union all select 'd','3','f' union all select 'sdf','1','1' declare @Tb table(k1 varchar(10),k2 varchar(10),f3 varchar(10)) insert into @Tb select '1','1','1' union all select '1','2','3' union all select 'f','f','f' union all select 'd','d','s' union all select 'sdf','sdf','s'--查询 select * from @Tb B where not exists (select 1 from @Ta where k1=B.K1 and K2=B.K2 and e3=B.f3) --结果 k1 k2 f3 ---------- ---------- ---------- f f f d d s sdf sdf s(所影响的行数为 3 行)
--测试环境 declare @Ta table(k1 varchar(10),k2 varchar(10),e3 varchar(10)) insert into @Ta select '1','1','1' union all select '1','2','3' union all select 'd','3','f' union all select 'sdf','1','1' declare @Tb table(k1 varchar(10),k2 varchar(10),f3 varchar(10)) insert into @Tb select '1','1','1' union all select '1','2','3' union all select 'f','f','f' union all select 'd','d','s' union all select 'sdf','sdf','s'--查询 select * from @Tb B where not exists (select 1 from @Ta where k1=B.K1 and K2=B.K2 and e3=B.f3) --结果 k1 k2 f3 ---------- ---------- ---------- f f f d d s sdf sdf s(所影响的行数为 3 行)
请问 select 1 是什么意思?
declare @Ta table(k1 varchar(10),k2 varchar(10),e3 varchar(10)) insert into @Ta select '1','1','1' union all select '1','2','3' union all select 'd','3','f' union all select 'sdf','1','1' declare @Tb table(k1 varchar(10),k2 varchar(10),f3 varchar(10)) insert into @Tb select '1','1','1' union all select '1','2','3' union all select 'f','f','f' union all select 'd','d','s' union all select 'sdf','sdf','s'select * from @tb where (cast(k1 as varchar) +cast(k2 as varchar)) not in (select (cast(k1 as varchar) +cast(k2 as varchar)) from @ta)
declare @Ta table(k1 varchar(10),k2 varchar(10),e3 varchar(10)) insert into @Ta select '1','1','1' union all select '1','2','3' union all select 'd','3','f' union all select 'sdf','1','1' declare @Tb table(k1 varchar(10),k2 varchar(10),f3 varchar(10)) insert into @Tb select '1','1','1' union all select '1','2','3' union all select 'f','f','f' union all select 'd','d','s' union all select 'sdf','sdf','s'select * from @tb b where not exists(select 1 from @ta a where a.k1=b.k1 and a.k2=b.k2 )結果: k1 k2 f3 ---------- ---------- ---------- f f f d d s sdf sdf s
declare @Ta table(k1 varchar(10),k2 varchar(10),e3 varchar(10))
insert into @Ta select '1','1','1'
union all select '1','2','3'
union all select 'd','3','f'
union all select 'sdf','1','1'
declare @Tb table(k1 varchar(10),k2 varchar(10),f3 varchar(10))
insert into @Tb select '1','1','1'
union all select '1','2','3'
union all select 'f','f','f'
union all select 'd','d','s'
union all select 'sdf','sdf','s'--查询
select * from @Tb B
where not exists (select 1 from @Ta where k1=B.K1 and K2=B.K2 and e3=B.f3)
--结果
k1 k2 f3
---------- ---------- ----------
f f f
d d s
sdf sdf s(所影响的行数为 3 行)
declare @Ta table(k1 varchar(10),k2 varchar(10),e3 varchar(10))
insert into @Ta select '1','1','1'
union all select '1','2','3'
union all select 'd','3','f'
union all select 'sdf','1','1'
declare @Tb table(k1 varchar(10),k2 varchar(10),f3 varchar(10))
insert into @Tb select '1','1','1'
union all select '1','2','3'
union all select 'f','f','f'
union all select 'd','d','s'
union all select 'sdf','sdf','s'--查询
select * from @Tb B
where not exists (select 1 from @Ta where k1=B.K1 and K2=B.K2 and e3=B.f3)
--结果
k1 k2 f3
---------- ---------- ----------
f f f
d d s
sdf sdf s(所影响的行数为 3 行)
select 1 是什么意思?
insert into @Ta select '1','1','1'
union all select '1','2','3'
union all select 'd','3','f'
union all select 'sdf','1','1'
declare @Tb table(k1 varchar(10),k2 varchar(10),f3 varchar(10))
insert into @Tb select '1','1','1'
union all select '1','2','3'
union all select 'f','f','f'
union all select 'd','d','s'
union all select 'sdf','sdf','s'select * from @tb where (cast(k1 as varchar) +cast(k2 as varchar)) not in (select (cast(k1 as varchar) +cast(k2 as varchar)) from @ta)
insert into @Ta select '1','1','1'
union all select '1','2','3'
union all select 'd','3','f'
union all select 'sdf','1','1'
declare @Tb table(k1 varchar(10),k2 varchar(10),f3 varchar(10))
insert into @Tb select '1','1','1'
union all select '1','2','3'
union all select 'f','f','f'
union all select 'd','d','s'
union all select 'sdf','sdf','s'select * from @tb b where not exists(select 1 from @ta a where a.k1=b.k1 and a.k2=b.k2 )結果:
k1 k2 f3
---------- ---------- ----------
f f f
d d s
sdf sdf s