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

2010-10-01 09:29:46 162 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

还在写慢SQL？你不知道慢SQL，占用了你程序多少的性能一前言不管是开发同学还是DBA,想必大家都遇到慢查询(select,update,insert,delete 语句慢)，影响业务稳定性。这里说的慢，有两个含义一是比正常的慢，有可能正常执行时间是10ms，异常的是100ms 。二是sql执行时间超过设置的慢查询标准比如500ms。本文从IT架构以及数据库纬度来分析导致sql执行慢的原因/场景，抛砖引玉，有不足之处还请大家多多提建议。二基础知识分析慢查询之前，我们先看看sql执行的路径,理清楚可能会影响sql执行速度的相关因素。执行路径阅读详情

遇到一个问题，需要只用一条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的结果。

...全文

Dr_can Kalman Filter学习笔记(一) 本文是在b站Dr_can的卡尔曼滤波课程学习过程中的学习笔记阅读详情

BurmeHill

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

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

*/

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

相关推荐