22,300
社区成员




--> 测试数据:#aa
if object_id('tempdb.dbo.#aa') is not null drop table #aa
create table #aa(E int, G varchar(8))
insert into #aa
select 1, 'm' union all
select 2, 'm' union all
select 3, 'm' union all
select 4, 'n'
--> 测试数据:#bb
if object_id('tempdb.dbo.#bb') is not null drop table #bb
create table #bb(F int, H varchar(8))
insert into #bb
select 1, 'x' union all
--select 1, 'y' union all
select 2, 'x' union all
select 2, 'z' union all
select 3, 'x' union all
select 3, 'y' union all
select 3, 'z' union all
select 4, 'x' union all
select 4, 'z'
--> 测试数据:#cc
if object_id('tempdb.dbo.#cc') is not null drop table #cc
create table #cc(I int, K varchar(8))
insert into #cc
select 11, 'x' union all
select 11, 'z' union all
select 22, '..' union all
select 22, '..' union all
select 33, '..'
-- 双向否定
select * from #aa a where G='m' and exists
(
select 1 from #bb b where F=a.E
and not exists (select 1 from #bb where F=b.F and H not in (select K from #cc where I=11))
and not exists (select 1 from #cc where I=11 and K not in (select H from #bb where F=b.F))
)
/*
E G
----------- --------
2 m
*/
E G
---
1 m
2 m
表BB有列F,H,数据如下
F___H
1___x
1___x
1___y
2___x
2___x
2___z
3___x
3___y
3___z
4___x
4___z
create table aa(E int, G varchar(8))
insert into aa
select 1, 'm' union all
select 2, 'm' union all
select 3, 'm' union all
select 4, 'n'
create table bb(F int, H varchar(8))
insert into bb
select 1, 'x' union all
select 1, 'y' union all
select 2, 'x' union all
select 2, 'z' union all
select 3, 'x' union all
select 3, 'y' union all
select 3, 'z' union all
select 4, 'x' union all
select 4, 'z'
create table cc(I int, K varchar(8))
insert into cc
select 11, 'x' union all
select 11, 'z' union all
select 22, '..' union all
select 22, '..' union all
select 33, '..'
select distinct t1.* from aa t1 , bb t2 where t1.e = t2.f and t1.g = 'm' and t2.f not in
(
select m.f from bb m where not exists (select 1 from
(select bb.*,cc.* from bb , cc where bb.H = cc.K) n where n.k = m.h)
)
/*
E G
----------- --------
2 m
(所影响的行数为 1 行)
*/
drop table aa , bb , cc
--> 测试数据:#aa
if object_id('tempdb.dbo.#aa') is not null drop table #aa
create table #aa(E int, G varchar(8))
insert into #aa
select 1, 'm' union all
select 2, 'm' union all
select 3, 'm' union all
select 4, 'n'
--> 测试数据:#bb
if object_id('tempdb.dbo.#bb') is not null drop table #bb
create table #bb(F int, H varchar(8))
insert into #bb
select 1, 'x' union all
select 1, 'y' union all
select 2, 'x' union all
select 2, 'z' union all
select 3, 'x' union all
select 3, 'y' union all
select 3, 'z' union all
select 4, 'x' union all
select 4, 'z'
--> 测试数据:#cc
if object_id('tempdb.dbo.#cc') is not null drop table #cc
create table #cc(I int, K varchar(8))
insert into #cc
select 11, 'x' union all
select 11, 'z' union all
select 22, '..' union all
select 22, '..' union all
select 33, '..'
-- bb.H 和 cc.K 完全匹配的用这条
select * from #aa a where G='m' and exists
(
select 1 from #bb b left join (select K from #cc where I=11) c on a.E=b.F and b.H=c.K
group by b.F having count(1)=count(c.K) and count(c.K)=(select count(1) from #cc where I=11)
)
use test
go
if object_id('test.dbo.AA') is not null drop table AA
-- 创建数据表
create table AA
(
E int,
G char(2)
)
go
--插入测试数据
insert into AA select 1,'m'
union all select 2,'m'
union all select 3,'m'
union all select 4,'n'
go
if object_id('test.dbo.BB') is not null drop table BB
-- 创建数据表
create table BB
(
F int,
H char(2)
)
go
--插入测试数据
insert into BB select 1,'x'
union all select 1,'y'
union all select 2,'x'
union all select 2,'z'
union all select 3,'x'
union all select 3,'y'
union all select 3,'z'
union all select 4,'x'
union all select 4,'z'
go
if object_id('test.dbo.CC') is not null drop table CC
-- 创建数据表
create table CC
(
I int,
K char(2)
)
go
--插入测试数据
insert into CC select 11,'x'
union all select 11,'z'
union all select 22,'x'
union all select 22,'y'
union all select 33,'y'
go
--代码实现
select distinct a.*
from AA a join BB b
on a.G='m' and a.E=b.F and not exists(
select 1
from BB
where F=b.F and H not in(
select K
from CC
where CC.I=11
)
)
/*测试结果
E G
-------
2 m
(1 行受影响)
*/
--> 测试数据:#aa
if object_id('tempdb.dbo.#aa') is not null drop table #aa
create table #aa(E int, G varchar(8))
insert into #aa
select 1, 'm' union all
select 2, 'm' union all
select 3, 'm' union all
select 4, 'n'
--> 测试数据:#bb
if object_id('tempdb.dbo.#bb') is not null drop table #bb
create table #bb(F int, H varchar(8))
insert into #bb
select 1, 'x' union all
select 1, 'y' union all
select 2, 'x' union all
select 2, 'z' union all
select 3, 'x' union all
select 3, 'y' union all
select 3, 'z' union all
select 4, 'x' union all
select 4, 'z'
--> 测试数据:#cc
if object_id('tempdb.dbo.#cc') is not null drop table #cc
create table #cc(I int, K varchar(8))
insert into #cc
select 11, 'x' union all
select 11, 'z' union all
select 22, '..' union all
select 22, '..' union all
select 33, '..'
-- 这条语句求出F=2,4
select * from #bb b where not exists (select 1 from #bb where F=b.F and H not in (select K from #cc where I=11))
/*
F H
----------- --------
2 x
2 z
4 x
4 z
*/
-- 联合起来一条SQL
select * from #aa a where G='m' and exists
(
select 1 from #bb b where a.E=b.F and not exists (select 1 from #bb where F=b.F and H not in (select K from #cc where I=11))
)
/*
E G
----------- --------
2 m
*/