select distinct EQU_GROUP
from a as t
where not exists(select 1 from a left join b on a.EQU_GROUP=t.EQU_GROUP and a.LOCATIONID=b.EQPID where b.EQPID is null)
from a as t
where not exists(select 1 from a left join b on a.EQU_GROUP=t.EQU_GROUP and a.LOCATIONID=b.EQPID where b.EQPID is null)
--try:
select EQU_GROUP from ta inner join tb on ta.LOCATIONID=tb.EQPID
group by EQU_GROUP having count(ta.LOCATIONID)=count(tb.EQPID)
go
create table [a]([EQU_GROUP] varchar(6),[LOCATIONID] varchar(3))
insert [a]
select 'Group1','dd1' union all
select 'Group1','dd2' union all
select 'Group2','kk5' union all
select 'Group2','pp3'
go
if object_id('[b]') is not null drop table [b]
go
create table [b]([OPERATORID] int,[EQPID] varchar(3))
insert [b]
select 1,'dd1' union all
select 2,'dd2' union all
select 3,'pp3'
go
--select * from [a]
--select * from [b]select distinct EQU_GROUP
from a as t
where EQU_GROUP not in (select a.EQU_GROUP from a left join b on a.EQU_GROUP=t.EQU_GROUP and a.LOCATIONID=b.EQPID where b.EQPID is null)/*
EQU_GROUP
---------
Group1(1 行受影响)
*/
insert @ta select 'Group1' , 'dd2'
insert @ta select 'Group2' , 'kk5'
insert @ta select 'Group2' , 'pp3' declare @tb table(OPERATORID int, EQPID varchar(10) ) insert @tb select 1 , 'dd1'
insert @tb select 2 , 'dd2'
insert @tb select 3 , 'pp3'select distinct EQU_GROUP from @ta where EQU_GROUP not in(select EQU_GROUP from @ta where LOCATIONID not in (select EQPID from @tb))
/*
EQU_GROUP
----------
Group1(所影响的行数为 1 行)
*/
EQU_GROUP OPERATORID
Group1 1
Group1 2
from a join b on a.LOCATIONID=b.EQPID
where EQU_GROUP not in (select t1.EQU_GROUP from a t1 left join b t2 on t1.EQU_GROUP=a.EQU_GROUP and t1.LOCATIONID=t2.EQPID where t2.EQPID is null)/*
EQU_GROUP OPERATORID
--------- -----------
Group1 1
Group1 2(2 行受影响)
*/
EQU_GROUP
from
a as t
where
not exists(select 1 from a left join b on a.EQU_GROUP=t.EQU_GROUP and a.LOCATIONID=b.EQPID where b.EQPID is null)
select a.EQU_GROUP,b.OPERATORID
from a join b on a.LOCATIONID=b.EQPID
where EQU_GROUP not in (select t1.EQU_GROUP from a t1 left join b t2 on t1.LOCATIONID=t2.EQPID where t2.EQPID is null)
insert @ta select 'Group1' , 'dd2'
insert @ta select 'Group2' , 'kk5'
insert @ta select 'Group2' , 'pp3' declare @tb table(OPERATORID int, EQPID varchar(10) ) insert @tb select 1 , 'dd1'
insert @tb select 2 , 'dd2'
insert @tb select 3 , 'pp3'select a.EQU_GROUP,b.OPERATORID from @ta a inner join @tb b on a. LOCATIONID=b.EQPID where EQU_GROUP not in(select EQU_GROUP from @ta where LOCATIONID not in (select EQPID from @tb))
/*
EQU_GROUP OPERATORID
---------- -----------
Group1 1
Group1 2(所影响的行数为 2 行)
*/
select a.EQU_GROUP,b.OPERATORID
from a join b on a.LOCATIONID=b.EQPID
where not exists(select 1 from a t1 left join b t2 on t1.LOCATIONID=t2.EQPID where t1.EQU_GROUP=a.EQU_GROUP and t2.EQPID is null)
go
create table [a]([id] int)
insert [a]
select 1 union all
select 2 union all
select null
go
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int)
insert [b]
select 1 union all
select null
go
select * from [a]
select * from [b]--1.并未得到预期结果,因为in后面的结果集中包含null
select * from a where id not in (select id from b)
/*
id
-----------(0 行受影响)
*/--2.得到非null的结果,可见in忽略null值的比较
select * from a where id in (select id from b)
/*
id
-----------
1(1 行受影响)
*/
SQL codeif object_id('[a]') is not null drop table [a]
go
create table [a]([id] int)
insert [a]
select 1 union all
select 2 union all
select null
go
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int)
insert [b]
select 1 union all
select null
go
select * from [a]
select * from [b]--1.并未得到预期结果,因为in后面的结果集中包含null
select * from a wher…
select * from a where id not in (select id from b)
/*
id
-----------(0 行受影响)
*/--2.得到非null的结果,可见in忽略null值的比较
select * from a where id in (select id from b)
/*
id
-----------
1(1 行受影响)
*/了解了谢谢
为什么后面结果集包含NULL 他就无法正确得到预期效果
insert @ta select 'Group1' , 'dd2'
insert @ta select 'Group2' , 'kk5'
insert @ta select 'Group2' , 'pp3' declare @tb table(OPERATORID int, EQPID varchar(10) ) insert @tb select 1 , 'dd1'
insert @tb select 2 , 'dd2'
insert @tb select 3 , 'pp3'SELECT T2.EQU_GROUP,OPERATORID FROM @ta T3,@TB T4, (select EQU_GROUP
from @ta T
LEFT join @tb T1
on T.LOCATIONID=T1.EQPID
group by EQU_GROUP
having count(T.LOCATIONID)=count(T1.EQPID))AS T2
WHERE T3.LOCATIONID=T4.EQPID
AND T2.EQU_GROUP=T3.EQU_GROUPEQU_GROUP OPERATORID
---------- -----------
Group1 1
Group1 2(所影响的行数为 2 行)
俺也学习一下,呵呵!!!以前听过,但不知道是什么具体原因