不知道这个SQL该怎么写,请指教

BurmeHill 2010-10-01 09:29:46
遇到一个问题,需要只用一条SQL得到数据,请各位高手指点:
表AA有列E,G,数据如下,_表示空格(不知道怎么发表格,空格总是被自动删除)
E___G
1___m
2___m
3___m
4___n

表BB有列F,H,数据如下
F___H
1___x
1___y
2___x
2___z
3___x
3___y
3___z
4___x
4___z

表CC有列I,K,数据如下
I___K
11__x
11__z
22__..
22__..
33__..

表中AA.E与BB.F是主外键关系,BB.H与CC.K没有关系,但是其中的值可能是一样的。

现已知 AA.G=m, CC.I=11, 想用一条SQL得到AA.E=2的结果。

注意:当BB.H和CC.K进行比较时,这两列的记录数和值需要完全一样才是符合要求的。也就是说链接BB和CC的时候只有F=2,4才是符合要求的数据,再通过已知的AA.G=m链接到AA得到AA.E=2的结果。
...全文
87 13 点赞 打赏 收藏 举报
写回复
13 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
claro 2010-10-02
没看懂,学习。
  • 打赏
  • 举报
回复
SQLCenter 2010-10-02
双向否定

--> 测试数据:#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
*/
  • 打赏
  • 举报
回复
SQLCenter 2010-10-02
[Quote=引用 5 楼 dawugui 的回复:]

--不考虑你有重复数据如下:
SQL code
表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



--需要使用双重否定.
SQL code
create table aa(E int, G varchar(8))
insert into aa
select ……
[/Quote]

还不一样仅仅是 CC.K 包含 BB.H,不是全匹配

是需要双重否定,但不是单向,而是双向否定。
  • 打赏
  • 举报
回复
dawugui 2010-10-02
[Quote=引用 4 楼 burmehill 的回复:]
非常感谢各位的回复,但是

1楼和2楼都没有完全匹配BB和CC表。在构造BB表的时候,如果去掉下面红色的那一行

表BB有列F,H,数据如下
F___H
1___x
1___y
2___x
2___z
3___x
3___y
3___z
4___x
4___z

1楼和2楼的SQL都会返回两行数据
E G
----------- -
1 m
2 m


现在正在研究3楼的回复。虽然还没有得到答案,仍然非常感谢。[/Quote]

按照你的需求,如果去掉1___y这行数据,结果就应该是:

E G   
---
1 m
2 m
  • 打赏
  • 举报
回复
BurmeHill 2010-10-02
[Quote=引用 12 楼 sqlcenter 的回复:]
引用 11 楼 burmehill 的回复:

请教一下SQLCenter同学, 你觉得是3楼count方法效率高还是8楼的双向否定效率高啊。如果三个表的数据量都有10万左右的话。

AA表主键为E,BB表F,H为联合主键,CC表主键为I.


这个我也拿不准,感觉应该是8楼,你实测一下。
[/Quote]


在数据库实际跑了一下,8楼的双向否定效率高,快了不只一点半点,有数量级的区别。看来我还要加强对not exists的学习。
  • 打赏
  • 举报
回复
SQLCenter 2010-10-02
[Quote=引用 11 楼 burmehill 的回复:]

请教一下SQLCenter同学, 你觉得是3楼count方法效率高还是8楼的双向否定效率高啊。如果三个表的数据量都有10万左右的话。

AA表主键为E,BB表F,H为联合主键,CC表主键为I.
[/Quote]

这个我也拿不准,感觉应该是8楼,你实测一下。
  • 打赏
  • 举报
回复
BurmeHill 2010-10-02
请教一下SQLCenter同学, 你觉得是3楼count方法效率高还是8楼的双向否定效率高啊。如果三个表的数据量都有10万左右的话。

AA表主键为E,BB表F,H为联合主键,CC表主键为I.
  • 打赏
  • 举报
回复
BurmeHill 2010-10-02
非常感谢你,SQLCenter同学,我觉得你3楼和8楼的回复应该都满足我的要求,我自己思考的结果和你3楼的类似但是没有你的简洁,我构造了3个子查询来实现count的比较。你在8楼的回复是我不怎么会用的not exists,我完全理解not exists的意思和用法,但是一旦用在复杂的SQL中我就有点拿不准,不自信 ,总害怕有点问题,所以一般情况下能不用就不用。

6楼的dawugui同学,按照我题目的要求“当BB.H和CC.K进行比较时,这两列的记录数和值需要完全一样才是符合要求的”,如果去掉1___y这行数据,结果仍然应该只有一行返回。SQLCenter同学很好地实现了这一点。

我结贴的时候准备把分都给SQLCenter同学。谢谢!
  • 打赏
  • 举报
回复
dawugui 2010-10-01
--不考虑你有重复数据如下:
表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
  • 打赏
  • 举报
回复
BurmeHill 2010-10-01
非常感谢各位的回复,但是

1楼和2楼都没有完全匹配BB和CC表。在构造BB表的时候,如果去掉下面红色的那一行

表BB有列F,H,数据如下
F___H
1___x
1___y
2___x
2___z
3___x
3___y
3___z
4___x
4___z

1楼和2楼的SQL都会返回两行数据
E G
----------- -
1 m
2 m


现在正在研究3楼的回复。虽然还没有得到答案,仍然非常感谢。
  • 打赏
  • 举报
回复
SQLCenter 2010-10-01
BB.H 和 CC.K 完全匹配的用这条

--> 测试数据:#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)
)
  • 打赏
  • 举报
回复
喜-喜 2010-10-01
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 行受影响)
*/
  • 打赏
  • 举报
回复
SQLCenter 2010-10-01
--> 测试数据:#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
*/
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2010-10-01 09:29
社区公告
暂无公告