文档章节

突破SQL查询的逻辑壁垒:双重NOT EXISTS嵌套查询

源远流长-泉
 源远流长-泉
发布于 2017/06/19 08:50
字数 2534
阅读 418
收藏 1

http://mp.weixin.qq.com/s/EamKu2tppVVqoZzdVMeTJg

 

首先假设读者都学过SQL。万一你还没有学过,推荐阅读一本SQL权威经典传世之作,内容不多,两千多页,看个十年就差不多会了。


如果你看到这一段,说明你已经自学完毕。恭喜!你学会了一门即使十年前开始学十年后依旧流行的技术。

IT领域很多技术都是几年间就缘起缘灭,还有不少技术即使名称和体系尚在,内涵和细节也已经跟当初大相径庭。能够像SQL这样历经数十年都保持稳定而且长盛不衰的,实在不多。


懂SQL的你,想必对于SELECT … FROM … WHERE … 这样的句式早已了如指掌,倒背如流。

为了文章的完整性,我还是啰里八嗦地给出一个例子吧。例如要找年龄大于30的客户,查询这么写(表结构就不赘述了,要猜也是易如反掌):
    SELECT CustomerName
    FROM Customers
    WHERE Age > 30

懂SQL的你,想必对于Aggregation也是驾轻就熟的。例如要找各个省份的男性顾客的平均年龄,而且只要看大于35的那些(为啥?平均年龄大于35是壮年中产,优质客户,钻石王老五啊),再按省份排个序:
    SELECT Province, AVG(age)
    FROM Customers
    WHERE Gender = 'Male'
    GROUP BY Province
    HAVING AVG(age) > 35
    ORDER BY Province

这个查询就比较牛了,数一数,常用的六个子句全有。能写出这个,说明你已经练就了六层的功力。

不过即便这样的查询,逻辑上也并不复杂。只要熟悉每种子句的功能和用法,一个一个堆上去就行。

有一类查询,要写出来就不只是掌握语法这么简单,更需要厘清内在的逻辑,这就是用到EXISTS/NOT EXISTS嵌套的查询。你离九层功力还差的三层,就是这个。想练个功提升境界不?


决定豁出去的话,一起来练一下。

听说现在TFBoys很流行,咱就用他们来作个例子。你把TFBoys换成张学友刘德华周杰伦蔡依林席琳迪翁火星哥也可以说明问题。


假设我们有一个TFBoys歌迷会的数据库,里面存放了歌迷的个人信息、TFBoys活动信息以及歌迷参加活动的记录。多参加活动才是真爱,支持爱豆要有实际行动的哟!

作为拥有丰富的数据库设计经验的你,想都不用想就可以写出如下所示的三个表结构来存放这些数据:
Fans 表和示例数据
FanID
FanName
Birthday
City
1
李雷
1992-06-24
北京
2
韩梅梅
1994-08-12
上海
 
Events表和示例数据
EventID
EventName
EventTime
Location
1
江苏卫视跨年
2014-12-31
南京
2
湖南卫视跨年
2015-12-31
长沙
3
央视中秋晚会
2016-09-15
北京
4
天猫2016双11狂欢夜
2016-11-10
杭州
 
Attendance表和示例数据
FanID
EventID
1
1
1
2
2
3
2
1
 
那么问题来了:挖掘机技术…… 啊不,如何查找参加过TFBoys所有活动的铁粉?如果用通常的方法把几个表JOIN起来,可以找到参加过活动的粉丝,但是没法找出来参加过所有活动的粉丝。一个“所有”害死多少脑细胞啊。

这个问题还有另一面:把答案放在面前也往往看不懂……比如下面这个就是答案:
SELECT FanName FROM Fans
WHERE NOT EXISTS
(
        SELECT EventID FROM Events
        WHERE NOT EXISTS
        (
                SELECT * FROM Attendance
                WHERE Attendance.FanID=Fans.FanID
                AND Attendance.EventID=Events.EventID
        )
)

选择粉丝名字,当不存在选择活动ID,当不存在选择参与记录……这是什么鬼?


下面,大侠就手把手带你突破SQL查询的逻辑壁垒。

首先把逻辑想清楚。“所有”这个要求按常规去想会把你带进沟里,要么写不出来要么写出来是错的。这里我们需要发挥中文的强大逻辑威力:双重否定加强肯定。

假如你有一个朋友是TFBoys的真爱粉,你在生活中会怎么评价她呢?你可能会说:她参加了TFBoys所有的活动。不过这么说情感不够热烈,正确的说法是:这家伙脑残粉啊,没有哪次活动她没去!

对,要的就是这个感觉和逻辑:没有哪次活动她没去。没有就是不存在,这么说没毛病吧?根据这个思路,我们再细化一下:
不存在一个活动,她没有去过。

结合我们的数据库表,没有去过就是不存在参与记录,对不对?  那么接着细化:
不存在一个活动,对这个活动来说,不存在她的参与记录。

好了,到这里我们有了一个完整的逻辑:
我们要找一个粉丝,一个什么样的粉丝呢?对这个粉丝来说,不存在一个活动,什么样的活动呢?对这个活动来说,不存在这个粉丝的参与记录。

翻译成人话,不还是这家伙没有哪次活动她没去吗?


此外,我们还需要做一个逻辑上的转换。众所周知,SQL的SELECT语句一般都会返回多行满足条件的数据,或者说SELECT本质上是查找集合。但是我们在写SELECT语句的时候可以想象成或者理解成我们在找一个具体的个体,同时描述这个个体需要满足的条件。我们要是能把满足条件的个体一个一个都找出来,不就得到一个集合了吗?

逻辑准备都做好了,接下来大侠带你一步一步攻克这个难题。

(表情包好像哪里有点不对……)

1
第一步:找一个粉丝

找一个粉丝,一般来说就是要知道粉丝的名字对吧。这个太容易了:
SELECT FansName FROM Fans

2
第二步:对这个粉丝来说,不存在某种东西

是什么东西先不管,把“不存在”的语法框架先写出来:
SELECT FansName FROM Fans
WHERE NOT EXISTS
(

)

3
第三步:找一个活动

前面的逻辑里说,对这个粉丝来说不存在一个活动,所以“不存在”后面是“一个活动”。找一个活动也很容易,就找它的ID吧:
SELECT FansName FROM Fans
WHERE NOT EXISTS
(
        SELECT EventID FROM Events
)

4
对这个活动来说,不存在某种东西

是什么东西先不管,把“不存在”的语法框架先写出来:
SELECT FansName FROM Fans
WHERE NOT EXISTS
(
        SELECT EventID FROM Events
        WHERE NOT EXISTS
        (

        )
)

5
第五步:找一个参与记录

逻辑里说不存在这个粉丝的参与记录,所以“不存在”后面是“参与记录”。参与记录正是Attendance表中的内容,所以找一个参与记录也容易写:
SELECT FansName FROM Fans
WHERE NOT EXISTS
(
        SELECT EventID FROM Events
        WHERE NOT EXISTS
        (
                 SELECT * FROM Attendance
        )
)

6
第六步:说明这个参与记录要满足的条件

这里我们就只需要通过外键相等关系把Attendance中的FanID和EventID与外层两个表关联起来,说明是这个参与记录是属于“这个人”的,参与的是“这个活动”:
SELECT FansName FROM Fans
WHERE NOT EXISTS
(
        SELECT EventID FROM Events
        WHERE NOT EXISTS
        (
                SELECT * FROM Attendance
                WHERE Attendance.FanID=Fans.FanID
                AND Attendance.EventID=Events.EventID
        )
)
注意,最内层用到的Fans表和最外层用到的Fans表是同一个,说明是同一个人;最内层用到的Events表和次外层用到的Events表也是同一个,说明是同一个活动。

怎么样,现在理解了吧?有没有一种荡气回肠一气呵成的感觉?


顺着同样的思路,你就可以流畅准确地写出回答下列问题的查询:
找出所有伪粉丝。所谓伪粉丝,就是挂了名却一次都没去过,非真爱。从来没去过,就是没有哪个活动他去过,就是对这个人来说,不存在一个活动,对这个活动来说,存在他参与的记录。这个要用到NOT EXISTS内嵌EXISTS作为查询条件。
找出所有广义粉。所谓广义粉,就是至少去过一次的。至少去过一次,就是对这个人来说,存在一个活动,对这个活动来说,存在他参与的记录。这个要用到EXISTS内嵌EXISTS作为查询条件。这个查询也会找到铁粉,因为铁粉肯定是广义粉。
找出可以挽救的非铁粉。非铁粉就是去了一些活动但是没有全去,不够铁但是属于可以挽救的对象。首先要去过,这个用Fans表关联Attendance表就可以找到,或者用查找广义粉的方法。然后,对这个人来说,存在一个活动,对这个活动来说,不存在他参与的记录。这个要用到EXISTS内嵌NOT EXISTS作为查询条件。

再开点脑洞,我们还可以做些更有趣的查询:
查查哪些人在暗恋韩梅梅,也即谁去过韩梅梅去过的所有活动,他们不是去追TFBoys的,他们是去追韩梅梅的。
查查哪些妹子和韩梅梅不对付,也即只要韩梅梅去的活动(假设韩梅梅不是所有活动都去)她们都不去。
查找所有北京歌迷都去过的活动。
查找所有上海歌迷都不去的活动。

这些都是很有难度的查询。能写查询回答这样的问题,就达到了SQL查询的最高境界。掌握了这样的能力,就等于有了神功护体,秒杀各种面试、工作中的SQL问题不在话下。

只要有实力,一定能掌握时代给予你的机会!

 

本文转载自:http://mp.weixin.qq.com/s/EamKu2tppVVqoZzdVMeTJg

源远流长-泉
粉丝 0
博文 25
码字总数 3661
作品 0
私信 提问
(七)MySQL数据库-嵌套查询

版权声明:转载请注明原文地址 https://blog.csdn.net/Super_RD/article/details/89763694 (七)MySQL数据库-嵌套查询 我的系统版本为CentOS7.5,MySQL版本为5.7.26 为了更清楚的说明各个连...

Super_RD
05/02
0
0
写一个“特殊”的查询构造器 - (四、条件查询:复杂条件)

复杂的条件 在 SQL 的条件查询中,不只有 where、or where 这些基本的子句,还有 where in、where exists、where between 等复杂一些的子句。而且即使是 where 这种基础的子句,也有多个条件...

MrQ被抢注了
2018/05/19
0
0
MySQL(八)|MySQL中In与Exists的区别(2)

关于In与Exists的比较,先说结论,归纳出IN 和Exists的适用场景: 1)IN查询在内部表和外部表上都可以使用到索引。 2)Exists查询仅在内部表上可以使用到索引。 3)当子查询结果集很大,而外...

小怪聊职场
2018/05/28
0
0
数据库知识整理 - 关系数据库标准语言SQL(二)- 超良心的数据查询整理!

版权声明: https://blog.csdn.net/Ha1f_Awake/article/details/83957857 主要内容 数据查询 基本语法 单表查询 1. 选择表中的若干列 2. 选择表中的若干元组 3. ORDER BY子句 4. 聚集函数 5....

恰少年
2018/11/14
0
0
MySQL查询语句中的IN 和Exists 对比分析

背景介绍 最近在写SQL语句时,对选择IN 还是Exists 犹豫不决,于是把两种方法的SQL都写出来对比一下执行效率,发现IN的查询效率比Exists高了很多,于是想当然的认为IN的效率比Exists好,但本...

lilugoodjob
2018/07/02
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Giraph源码分析(八)—— 统计每个SuperStep中参与计算的顶点数目

作者|白松 目的:科研中,需要分析在每次迭代过程中参与计算的顶点数目,来进一步优化系统。比如,在SSSP的compute()方法最后一行,都会把当前顶点voteToHalt,即变为InActive状态。所以每次...

数澜科技
今天
4
0
Xss过滤器(Java)

问题 最近旧的系统,遇到Xss安全问题。这个系统采用用的是spring mvc的maven工程。 解决 maven依赖配置 <properties><easapi.version>2.2.0.0</easapi.version></properties><dependenci......

亚林瓜子
今天
10
0
Navicat 快捷键

操作 结果 ctrl+q 打开查询窗口 ctrl+/ 注释sql语句 ctrl+shift +/ 解除注释 ctrl+r 运行查询窗口的sql语句 ctrl+shift+r 只运行选中的sql语句 F6 打开一个mysql命令行窗口 ctrl+l 删除一行 ...

低至一折起
今天
10
0
Set 和 Map

Set 1:基本概念 类数组对象, 内部元素唯一 let set = new Set([1, 2, 3, 2, 1]); console.log(set); // Set(3){ 1, 2, 3 } [...set]; // [1, 2, 3] 接收数组或迭代器对象 ...

凌兮洛
今天
4
0
PyTorch入门笔记一

张量 引入pytorch,生成一个随机的5x3张量 >>> from __future__ import print_function>>> import torch>>> x = torch.rand(5, 3)>>> print(x)tensor([[0.5555, 0.7301, 0.5655],......

仪山湖
今天
6
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部