select * from a
where not exists(
select 1 from b
where a.列1=b.列1 and a.列2=b.列2
and a.列3=b.列3 and a.列4=b.列4 and a.列5=b.列5
)
where not exists(
select 1 from b
where a.列1=b.列1 and a.列2=b.列2
and a.列3=b.列3 and a.列4=b.列4 and a.列5=b.列5
)
and a.列3=b.列3 and a.列4=b.列4 and a.列5=b.列5
条件,你要自己选择的,
where not exists (select 1 from zzz o,mrequire_full f
where o.yearid=f.yearid
and o.clientid=f.clientid
and o.batchid=f.batchid
and o.mcode=f.mcode
and o.color=f.color
and o.asize=f.asize and o.mcode='b1225' and o.color='砖红')还是不行啊,没有返回任何数据。
(
f1 varchar(10),
f2 varchar(20),
f3 varchar(10),
f4 varchar(10),
f5 varchar(10),
)create table b
(
f1 varchar(10),
f2 varchar(20),
f3 varchar(10),
f4 varchar(10),
f5 varchar(10),
)insert into a values('A100','黑色面/深灰底','2006','686','销样')
insert into a values('A101','BLACK 黑色','2006','686','销样')
insert into a values('A101','COFFEE 咖啡色','2006','686','销样')insert into b values('A100','黑色面/深灰底','2006','686','销样')
insert into b values('A101','BLACK 黑色','2006','686','销样')
insert into b values('A101','RED 红色','2006','686','销样')select * from a
where not exists(
select 1 from b
where a.f1=b.f1 and a.f2=b.f2
and a.f3=b.f3 and a.f4=b.f4 and a.f5=b.f5
)
where not exists(
select 1 from b
where a.f1=b.f1 and a.f2=b.f2
and a.f3=b.f3 and a.f4=b.f4 and a.f5=b.f5
)这个方法能实现吧.
(
col1 varchar(10),
col2 varchar(20),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10)
)declare @B table
(
col1 varchar(10),
col2 varchar(20),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10)
)insert @A
select 'A100','黑色面/深灰底','2006','686','销样' union
select 'A101','BLACK 黑色','2006','686','销样' union
select 'A101','COFFEE 咖啡色','2006','686','销样'insert @B
select 'A100','黑色面/深灰底','2006','686','销样' union
select 'A101','BLACK 黑色','2006','686','销样' union
select 'A101','RED 红色','2006','686','销样'--查询
select * from @A t
where not exists(select 1
from @B
where col1=t.col1
and col2=t.col2
and col3=t.col3
and col4=t.col4
and col5=t.col5)
select A.*
from @A A
left join @B B on A.col1=B.col1
and A.col2=B.col2
and A.col3=B.col3
and A.col4=B.col4
and A.col5=B.col5
where B.col1 is null--结果
/*
col1 col2 col3 col4 col5
---------- -------------------- ---------- ---------- ----------
A101 COFFEE 咖啡色 2006 686 销样(1 row(s) affected)col1 col2 col3 col4 col5
---------- -------------------- ---------- ---------- ----------
A101 COFFEE 咖啡色 2006 686 销样(1 row(s) affected)
*/
========
create table tb1 (
code varchar(4) not null,
res1 nvarchar(20),
quality1 int,
quality2 int,
res2 nvarchar(10)
)
gocreate table tb2 (
code varchar(4) not null,
res1 nvarchar(20),
quality1 int,
quality2 int,
res2 nvarchar(10)
)
goinsert into tb1
values('A102','黑色面/深灰底','2006','686','销样')
insert into tb1
values('A101',N'BLACK 黑色','2006','686',N'销样')
insert into tb1
values('A101',N'COFFEE 咖啡色','2006','686',N'销样')
goinsert into tb2
values('A102','黑色面/深灰底','2006','686','销样')
insert into tb2
values('A101',N'BLACK 黑色','2006','686',N'销样')
insert into tb2
values('A101',N'RED 红色,'2006','686',N'销样')
goselect identity(int,1,1) as Cseq, * into tb3 from tb1
goselect * from tb3 where tb3.Cseq not in (
select tb3.Cseq from tb3 inner join tb2
on tb3.code = tb2.code and tb3.res1 = tb2.res1
and tb3.quality1 = tb2.quality1 and tb3.quality2 = tb2.quality2)
go测试结果:
========
Cseq code res1 quality1 quality2 res2
----------- ---- -------------------- ----------- ----------- ----------
3 A101 COFFEE 咖啡色 2006 686 销样(所影响的行数为 1 行)
where not exists(
select 1 from b
where a.f1=b.f1 and a.f2=b.f2
and a.f3=b.f3 and a.f4=b.f4 and a.f5=b.f5
)
可以查询出了。
但我不明白的是 select 1 from b 的 1 表示什么意思,有什么用处?谁能解释一下。
注意到里面是一个子查询 用到a表
但我不明白的是 select 1 from b 的 1 表示什么意思,有什么用处?谁能解释一下。
****************************************************************************
我是这样理解的:
select 1
from @B
where col1=t.col1
and col2=t.col2
and col3=t.col3
and col4=t.col4
and col5=t.col5)
选出的是满足
where col1=t.col1
and col2=t.col2
and col3=t.col3
and col4=t.col4
and col5=t.col5)
这个逻辑的集合,select 1只不过不取这个集合里的数据,而用常数1代替,如果不用select 1而用select *也是一样的。
然后用not exists
A not exists B 就是属于A不属于B的数据,也就是A有B没有的!