having 订阅
n.所有,持有v.有( have的现在分词 );(亲属关系中)接受;拿;买到 [1] 展开全文
n.所有,持有v.有( have的现在分词 );(亲属关系中)接受;拿;买到 [1]
信息
外文名
having
包    含
聚合函数
解    释
n所有,持有
having简介
使用 HAVING 子句选择行 [2]  HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用;而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似,但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。下面的查询得到本年度截止到目前的销售额超过 $40,000 的出版商:下面是结果集:pub_id total------ -----------0877 44219(1 row(s) affected)为了确保对每个出版商的计算中至少包含六本书,下面示例使用 HAVING COUNT(*) > 5 消除返回的总数小于六本书的出版商:下面是结果集:pub_id total------ -----------0877 442191389 24941(2 row(s) affected)理解应用 WHERE、GROUP BY 和 HAVING 子句的正确序列对编写高效的查询代码会有所帮助:WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。GROUP BY 子句用来分组 WHERE 子句的输出。HAVING 子句用来从分组的结果中筛选行。对于可以在分组操作之前应用的搜索条件,在 WHERE 子句中指定它们更有效。这样可以减少必须分组的行数。应当在 HAVING 子句中指定的搜索条件只是那些必须在执行分组操作之后应用的搜索条件。因为HAVING是在查询出的结果集中进行筛选,这个操作需要排序、总计等操作。如果先通过WHERE子句限制查询的记录数,可以减少这方面的开销。Microsoft® SQL Server™ 2000 查询优化器可处理这些条件中的大多数。如果查询优化器确定 HAVING 搜索条件可以在分组操作之前应用,那么它就会在分组之前应用。查询优化器可能无法识别所有可以在分组操作之前应用的 HAVING 搜索条件。建议将所有这些搜索条件放在 WHERE 子句中而不是 HAVING 子句中。以下查询显示包含聚合函数的 HAVING 子句。该子句按类型分组 titles 表中的行,并且消除只包含一本书的组:下面是结果集:type------------------businessmod_cookpopular_comppsychologytrad_cook(5 row(s) affected)以下是没有聚合函数的 HAVING 子句的示例。该子句按类型分组 titles 表中的行,并且消除不是以字母 p 开头的那些类型。下面是结果集:type------------------popular_comppsychology(2 row(s) affected)如果 HAVING 中包含多个条件,那么这些条件将通过 AND、OR 或 NOT 组合在一起。以下示例显示如何按出版商分组 titles,只包括那些标识号大于 0800、支付的总预付款已超过 $15,000 且销售书籍的平均价格小于 $20 的出版商。ORDER BY 可以用来为 GROUP BY 子句的输出排序。下面的示例显示使用 ORDER BY 子句以定义返回 GROUP BY 子句中的行为递减顺序:
收起全文
精华内容
下载资源
问答
  • having的用法 having字句可以让我们筛选成组后的各种数据,where字句在聚合前先筛选记录,也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。SQL实例: 一、显示每个地区的总人口数和...
  • GROUP BY语句和HAVING语句,经过研究和练习,终于明白如何使用了,在此记录一下同时添加了一个自己举的小例子,通过写这篇文章来加深下自己学习的效果,还能和大家分享下,同时也方便以后查阅,一举多得,下面由小编...
  • 2.HAVING 是跟GROUP BY 连在一起用的,放在GROUP BY 后面,此时的作用相当于WHERE 3.WHERE 后面的条件中不能有聚集函数,比如SUM(),AVG()等,而HAVING 可以  Where和Having都是对查询结果的一种筛选,说的书面点...
  • 主要介绍了sql中的 where 、group by 和 having 用法解析,需要的朋友可以参考下
  • HAVING是先分组再筛选记录,WHERE在聚合前先筛选记录.也就是说作用在GROUP BY 子句和HAVING子句前;而 HAVING子句在聚合后对组记录进行筛选
  • 对于初学者来说,一般用学生及其选课等信息这些基本的操作入手。 就以学生选课表为例: 最基本的查询格式: select 查询项 ...having 后跟附加条件, 4、其他聚集函数的应用: 最大值max()、最
  • 主要介绍了MySQL中无GROUP BY情况下直接使用HAVING语句的问题探究,同时探究了该情况下MAX与MIN功能的使用情况,需要的朋友可以参考下
  • Having”是一个过滤声明,是在查询返回结果集以后对查询结果进行的过滤操作,在Having中可以使用聚合函数。  聚合函数,SQL基本函数,聚合函数对一组值执行计算,并返回单个值。除了 COUNT 以外,聚合函数都会...
  • mysql中group by语句用于分组查询,可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表, 经常和having一起使用,需要的朋友可以参考下
  • 何时使用having where,请遵照下面的说明:WHERE语句在GROUP BY语句之前;SQL会在分组之前计算WHERE语句。HAVING语句在GROUP BY语句之后;SQL会在分组之后计算HAVING语句。
  • 主要介绍了实例详解Group by和Having子句的相关资料,需要的朋友可以参考下
  • 摘要:C#源码,数据库应用,分组数据 C#源码实例——利用having语句过滤分组数据,按部门计算平均工资,对having语句不熟悉的C#朋友,可参考一下,having用法也是值得学习的,作为一名优秀的程序员,熟悉各种数据库...
  • 主要给大家介绍了关于SQL中where子句与having子句的区别的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧
  • 在mysql中group by分组查询我们经常会用到,并且还同时会与having合用,下面我介绍group by用法与having合用注意事项,希望此教程对各位朋友有所帮助
  • Consumer behavior - buying, having, and being, 10th ed by Michael R Solomon, 2013.pdf
  • 本篇文章是对Mysql中order by、group by、having的区别进行了详细的分析介绍,需要的朋友参考下
  • 如果一个查询中使用了分组函数,任何不在分组函数中的列或表达式必须要在group by中,下面为大家简要介绍下group by,having,order by的用法
  • 摘要:C#源码,数据库应用,多表查询 Visual C# having语句应用在多表查询中,可用于综合数据统计中,多表查询使用广泛,仅供参考。
  • 主要介绍了MySQL无GROUP BY直接HAVING返回空的问题分析,学习MYSQL需要注意这个问题
  • 主要介绍了SQL中的group by 和 having 用法浅析,需要的的朋友参考下吧
  • sql语句中where、group by、having、order by 是否可以使用别名.pdf
  • SQL中被忽视的HAVING你知道它到底有多重要吗?

    千次阅读 多人点赞 2020-12-24 15:17:56
    初识 HAVING  关于 SQL 中的 HAVING,相信大家都不陌生,它往往与 GROUP BY 配合使用,为聚合操作指定条件  说到指定条件,我们最先想到的往往是 WHERE 子句,但 WHERE 子句只能指定行的条件,而不能指定组的...

    初识 HAVING

      关于 SQL 中的 HAVING,相信大家都不陌生,它往往与 GROUP BY 配合使用,为聚合操作指定条件

      说到指定条件,我们最先想到的往往是 WHERE 子句,但 WHERE 子句只能指定行的条件,而不能指定组的条件,因此就有了 HAVING 子句,它用来指定组的条件。我们来看个具体示例就清楚了。

      我们有 学生班级表(tbl_student_class) 以及 数据如下 :

    DROP TABLE IF EXISTS tbl_student_class;
    CREATE TABLE tbl_student_class (
      id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
      sno varchar(12) NOT NULL COMMENT '学号',
      cno varchar(5) NOT NULL COMMENT '班级号',
      cname varchar(50) NOT NULL COMMENT '班级名',
      PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生班级表';
    
    -- ----------------------------
    -- Records of tbl_student_class
    -- ----------------------------
    INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190607001', '0607', '影视7班');
    INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190607002', '0607', '影视7班');
    INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190608003', '0608', '影视8班');
    INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190608004', '0608', '影视8班');
    INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609005', '0609', '影视9班');
    INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609006', '0609', '影视9班');
    INSERT INTO tbl_student_class(sno, cno, cname) VALUES ('20190609007', '0609', '影视9班');

      我们要查询  学生人数为 3 的班级 ,这就需要用到 HAVING 了,相信大家都会写

    SELECT cno, COUNT(*) nums FROM tbl_student_class GROUP BY cno HAVING COUNT(*) = 3;

      

      如果我们不使用 HAVING,会是什么样呢

      

      可以看到,除了数量等于 3 的班级之前,其他的班级也被查出来了

      我们可以简单总结下:WHERE 先过滤出行,然后 GROUP BY 对行进行分组,HAVING 再对组进行过滤,筛选出我们需要的组

      HAVING 子句的构成要素

        既然 HAVING 操作的对象是组,那么其使用的要素是有一定限制的,能够使用的要素有 3 种: 常数 、 聚合函数 和 聚合键 ,聚合键也就是 GROUP BY 子句中指定的列名

        示例中的 HAVING COUNT(*) = 3 , COUNT(*) 是聚合函数,3 是常数,都在 3 要素之中;如果有 3 要素之外的条件,会是怎么样呢

    SELECT cno, COUNT(*) nums FROM tbl_student_class GROUP BY cno HAVING cname = '影视9班';

        执行如上 SQL 会失败,并提示:

    [Err] 1054 - Unknown column 'cname' in 'having clause'

        在使用 HAVING 子句时,把 GROUP BY 聚合后的结果作为 HAVING 子句的起点,会更容易理解;示例中通过 cno 进行聚合后的结果如下:

        

        聚合后的这个结果并没有 cname 这个列,那么通过这个列来进行条件处理,当然就报错了啦

        细心的小伙伴应该已经发现,HAVING 子句的构成要素和包含 GROUP BY 子句时的 SELECT 子句的构成要素是一样的,都是只能包含 常数 、 聚合函数 和 聚合键 

    HAVING 的魅力

      HAVING 子句是 SQL 里一个非常重要的功能,是理解 SQL 面向集合这一本质的关键。下面结合具体的案例,来感受下 HAVING 的魅力

      是否存在缺失的编号

        tbl_student_class 表中记录的 id 是连续的(id 的起始值不一定是 1),我们去掉其中 3 条

    DELETE FROM tbl_student_class WHERE id IN(2,5,6);
    SELECT * FROM tbl_student_class;

        

        如何判断是否有编号缺失?

        数据量少,我们一眼就能看出来,但是如果数据量上百万行了,用眼就看不出来了吧

        不绕圈子了,我就直接写了,相信大家都能看懂(记得和自己想的对比一下)

    SELECT '存在缺失的编号' AS gap
    FROM tbl_student_class
    HAVING COUNT(*) <> MAX(id) - MIN(id) + 1;

        上面的 SQL 语句里没有 GROUP BY 子句,此时整张表会被聚合为一组,这种情况下 HAVING 子句也是可以使用的(HAVING 不是一定要和 GROUP BY 一起使用)

        写的更严谨点,如下(没有 HAVING,不是主角,看一眼就好)

    -- 无论如何都有结果返回
    SELECT CASE WHEN COUNT(*) = 0 THEN '表为空'
        WHEN COUNT(*) <> MAX(id) - MIN(id) + 1 THEN '存在缺失的编号'
        ELSE '连续' END AS gap
    FROM tbl_student_class;

        那如何找出缺失的编号了,欢迎评论区留言

      求众数

        假设我们有一张表: tbl_student_salary ,记录着毕业生首份工作的年薪

    DROP TABLE IF EXISTS tbl_student_salary;
    CREATE TABLE tbl_student_salary (
      id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
      name varchar(5) NOT NULL COMMENT '姓名',
      salary DECIMAL(15,2) NOT NULL COMMENT '年薪, 单位元',
      PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='毕业生年薪标';
    
    insert into tbl_student_salary values (1,'李小龙', 1000000);
    insert into tbl_student_salary values (2,'李四', 50000);
    insert into tbl_student_salary values (3,'王五', 50000);
    insert into tbl_student_salary values (4,'赵六', 50000);
    insert into tbl_student_salary values (5,'张三', 70000);
    insert into tbl_student_salary values (6,'张一三', 70000);
    insert into tbl_student_salary values (7,'张二三', 70000);
    insert into tbl_student_salary values (8,'张三三', 60000);
    insert into tbl_student_salary values (9,'张三四', 40000);
    insert into tbl_student_salary values (10,'张三丰', 30000);

        平均工资达到了 149000 元,乍一看好像毕业生大多都能拿到很高的工资。然而这个数字背后却有一些玄机,因为功夫大师李小龙在这一届毕业生中,由于他出众的薪资,将大家的平均薪资拉升了一大截

        简单地求平均值有一个缺点,那就是很容易受到离群值(outlier)的影响。这种时候就必须使用更能准确反映出群体趋势的指标——众数(mode)就是其中之一

        那么如何用 SQL 语句来求众数了,我们往下看

    -- 使用谓词 ALL 求众数
    SELECT salary, COUNT(*) AS cnt
    FROM tbl_student_salary
    GROUP BY salary
    HAVING COUNT(*) >= ALL (
        SELECT COUNT(*)
        FROM tbl_student_salary
        GROUP BY salary);

        结果如下

        

        ALL 谓词用于 NULL 或空集时会出现问题,我们可以用极值函数来代替;这里要求的是元素数最多的集合,因此可以用 MAX 函数

    -- 使用极值函数求众数
    SELECT salary, COUNT(*) AS cnt
    FROM tbl_student_salary
    GROUP BY salary
    HAVING COUNT(*) >= (
        SELECT MAX(cnt)
        FROM (
            SELECT COUNT(*) AS cnt
            FROM tbl_student_salary
            GROUP BY salary
            ) TMP
        ) ;

      求中位数

        当平均值不可信时,与众数一样经常被用到的另一个指标是中位数(median)。它指的是将集合中的元素按升序排列后恰好位于正中间的元素。如果集合的元素个数为偶数,则取中间两个元素的平均值作为中位数

        表 tbl_student_salary 有 10 条记录,那么 张三三, 60000 和 李四, 50000 的平均值 55000 就是中位数

        那么用 SQL,该如何求中位数呢?做法是,将集合里的元素按照大小分为上半部分和下半部分两个子集,同时让这 2 个子集共同拥有集合正中间的元素。这样,共同部分的元素的平均值就是中位数,思路如下图所示

        

        像这样需要根据大小关系生成子集时,就轮到非等值自连接出场了

    -- 求中位数的SQL 语句:在HAVING 子句中使用非等值自连接
    SELECT AVG(DISTINCT salary)
    FROM (
        SELECT T1.salary
        FROM tbl_student_salary T1, tbl_student_salary T2
        GROUP BY T1.salary
        -- S1 的条件
        HAVING SUM(CASE WHEN T2.salary >= T1.salary THEN 1 ELSE 0 END) >= COUNT(*) / 2
        -- S2 的条件
        AND SUM(CASE WHEN T2.salary <= T1.salary THEN 1 ELSE 0 END) >= COUNT(*) / 2
    ) TMP;

        这条 SQL 语句的要点在于比较条件 >= COUNT(*)/2 里的等号,加上等号并不是为了清晰地分开子集 S1 和 S2,而是为了让这 2 个子集拥有共同部分

        如果去掉等号,将条件改成 > COUNT(*)/2 ,那么当元素个数为偶数时,S1 和 S2 就没有共同的元素了,也就无法求出中位数了;加上等号是为了写出通用性更高的 SQL

      查询不包含 NULL 的集合

        假设我们有一张学生报告提交记录表:tbl_student_submit_log

    DROP TABLE IF EXISTS tbl_student_submit_log;
    CREATE TABLE tbl_student_submit_log (
      id int(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
      sno varchar(12) NOT NULL COMMENT '学号',
      dept varchar(50) NOT NULL COMMENT '学院',
      submit_date DATE COMMENT '提交日期',
      PRIMARY KEY (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生报告提交记录表';
    
    insert into tbl_student_submit_log values
    (1,'20200607001', '理学院', '2020-12-12'),
    (2,'20200607002', '理学院', '2020-12-13'),
    (3,'20200608001', '文学院', null),
    (4,'20200608002', '文学院', '2020-12-22'),
    (5,'20200608003', '文学院', '2020-12-22'),
    (6,'20200612001', '工学院', null),
    (7,'20200617001', '经济学院', '2020-12-23');

        学生提交报告后, submit_date 列会被写入日期,而提交之前是 NULL

        现在我们需要从这张表里找出哪些学院的学生全部都提交了报告,这个 SQL 该怎么写?

        如果只是用 WHERE submit_date IS NOT NULL 条件进行查询,那文学院也会被包含进来,结果就不正确了

        正确的做法应该先以 dept 进行分组(GROUP BY),然后对组进行条件的过滤,SQL 如下

    SELECT dept
    FROM tbl_student_submit_log
    GROUP BY dept
    HAVING COUNT(*) = COUNT(submit_date);

        这里其实用到了 COUNT 函数,COUNT(*) 可以用于 NULL ,而 COUNT(列名) 与其他聚合函数一样,要先排除掉 NULL 的行再进行统计

        当然,使用 CASE 表达式也可以实现同样的功能,而且更加通用

    SELECT dept
    FROM tbl_student_submit_log
    GROUP BY dept
    HAVING COUNT(*) = SUM(
        CASE WHEN submit_date IS NOT NULL THEN 1
            ELSE 0 END
        );

      其他

        不仅仅只是如上的那些场景适用于 HAVING,还有很多其他的场景也是需要用到 HAVING 的,有兴趣的可以去翻阅《SQL进阶教程》

    聚合键条件的归属

      我们来看个有趣的东西,还是用表:tbl_student_class

      

      我们发现,聚合键所对应的条件既可以写在 HAVING 子句当中,也可以写在 WHERE 子句当中

      虽然条件分别写在 HAVING 子句和 WHERE 子句当中,但是条件的内容,以及返回的结果都完全相同,因此,很多小伙伴就会觉得两种书写方式都没问题

      单从结果来看,确实没问题,但其中有一种属于偏离了 SQL 规范的非正规用法,推荐做法是: 聚合键所对应的条件应该书写在 WHERE 子句中 ,理由有二

      语义更清晰

        WHERE 子句和 HAVING 子句的作用是不同的;前面已经说过,HAVING 子句是用来指定“组”的条件的,而“行”所对应的条件应该写在 WHERE 子句中,这样一来,写出来的 SQL 语句不但可以分清两者各自的功能,而且理解起来也更容易

        

      执行速度更快

        使用 COUNT 等函数对表中数据进行聚合操作时,DBMS 内部进行排序处理,而排序处理会大大增加机器的负担,从而降低处理速度;因此,尽可能减少排序的行数,可以提高处理速度

        通过 WHERE 子句指定条件时,由于排序之前就对数据进行了过滤,那么就减少了聚合操作时的需要排序的记录数量;而 HAVING 子句是在排序之后才对数据进行分组的,与在 WHERE 子句中指定条件比起来,需要排序的数量就会多得多

        另外,索引是 WHERE 根据速度优势的另一个有利支持,在 WHERE 子句指定条件所对应的列上创建索引,可以大大提高 WHERE 子句的处理速度

    总结

      1、集合论

        集合论是 SQL 语言的根基,只有从集合的角度来思考,才能明白 SQL 的强大威力

        学习 HAVING 子句的用法是帮助我们顺利地忘掉面向过程语言的思考方式并理解 SQL 面向集合特性的最为有效的方法

      2、HAVING 子句的要素

        3 个要素:常亮、聚合函数 和 聚合键

        HAVING 大多数情况下和结合 GROUP BY 来使用,但不是一定要结合 GROUP BY 来使用

      3、SQL 的执行顺序

        

        WHERE 子句是指定行所对应的条件,而 HAVING 子句是指定组所对应的条件

    参考

      《SQL基础教程》

      《SQL进阶教程》


    好了,文章就写到这了,意犹未尽的朋友可以进群973961276来跟各位大佬一起交流学习,并且群里有超过的学习资料跟学长们的大厂面试经验分享,真的不来看看吗?

    展开全文
  • SQL 面试题:WHERE 和 HAVING、ON 有什么区别?

    万次阅读 多人点赞 2020-04-09 07:42:16
    SQL 中WHERE和HAVING的区别在于:WHERE子句在GROUP BY分组和聚合函数之前对数据行进行过滤;HAVING子句对GROUP BY分组和聚合函数之后的数据行进行过滤。 SQL 中WHERE和ON的区别在于:对于内连接查询,WHERE和ON中的...

    flowers

    SQL 提供了多种对数据进行过滤的方式,包括WHEREHAVING以及ON子句等。虽然它们都能够实现类似的功能,但是你知道它们之间的区别吗?让我们一起来探讨一下。

    除非特殊说明,以下内容适用于各种数据库,包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等;其中的示例表和数据来源于 GitHub

    WHERE 与 HAVING

    WHEREHAVING的根本区别在于:

    • WHERE子句在GROUP BY分组和聚合函数之前对数据行进行过滤;
    • HAVING子句对GROUP BY分组和聚合函数之后的数据行进行过滤。

    因此,WHERE子句中不能使用聚合函数。例如,以下语句将会返回错误:

    -- 查找人数大于 5 的部门
    select dept_id, count(*)
    from employee
    where count(*) > 5
    group by dept_id;
    

    由于在执行WHERE子句时,还没有计算聚合函数 count(*),所以无法使用。正确的方法是使用HAVING对聚合之后的结果进行过滤:

    -- 查找人数大于 5 的部门
    select dept_id, count(*)
    from employee
    group by dept_id
    having count(*) > 5;
    dept_id|count(*)|
    -------|--------|
          4|       9|
          5|       8|
    

    另一方面,HAVING子句中不能使用除了分组字段和聚合函数之外的其他字段。例如,以下语句将会返回错误:

    -- 统计每个部门月薪大于等于 30000 的员工人数
    select dept_id, count(*)
    from employee
    group by dept_id
    having salary >= 30000;
    

    因为经过GROUP BY分组和聚合函数之后,不再存在 salary 字段,HAVING子句中只能使用分组字段或者聚合函数。

    ⚠️SQLite 虽然允许HAVING子句中出现其他字段,但是得到的结果不正确。

    从性能的角度来说,HAVING子句中如果使用了分组字段作为过滤条件,应该替换成WHERE子句;因为WHERE可以在执行分组操作和计算聚合函数之前过滤掉不需要的数据,性能会更好。下面示例中的语句 1 应该替换成语句 2:

    -- 语句 1
    select dept_id, count(*)
    from employee
    group by dept_id
    having dept_id = 1;
    
    -- 语句 2
    select dept_id, count(*)
    from employee
    where dept_id = 1
    group by dept_id;
    

    当然,WHEREHAVING可以组合在一起使用。例如:

    select dept_id, count(*)
    from employee
    where salary > 10000
    group by dept_id
    having count(*) > 1;
    dept_id|count(*)|
    -------|--------|
          1|       3|
    

    该语句返回了月薪大于 10000 的员工人数大于 1 的部门;WHERE用于过滤月薪大于 10000 的员工;HAVING用于过滤员工数量大于 1 的部门。

    WHERE 与 ON

    当查询涉及多个表的关联时,我们既可以使用WHERE子句也可以使用ON子句指定连接条件和过滤条件。这两者之间的主要区别在于:

    • 对于内连接(inner join)查询,WHEREON中的过滤条件等效;
    • 对于外连接(outer join)查询,ON中的过滤条件在连接操作之前执行,WHERE中的过滤条件(逻辑上)在连接操作之后执行。

    对于内连接查询而言,以下三个语句的结果相同:

    -- 语句 1
    select d.dept_name, e.emp_name, e.sex, e.salary 
    from employee e, department d
    where e.dept_id = d.dept_id
    and e.emp_id = 10;
    dept_name|emp_name|sex|salary |
    ---------|--------|---|-------|
    研发部   |廖化    ||6500.00|
    
    -- 语句 2
    select d.dept_name, e.emp_name, e.sex, e.salary 
    from employee e
    join department d on (e.dept_id = d.dept_id and e.emp_id = 10);
    dept_name|emp_name|sex|salary |
    ---------|--------|---|-------|
    研发部   |廖化    ||6500.00|
    
    -- 语句 3
    select d.dept_name, e.emp_name, e.sex, e.salary 
    from employee e
    join department d on (e.dept_id = d.dept_id)
    where e.emp_id = 10;
    dept_name|emp_name|sex|salary |
    ---------|--------|---|-------|
    研发部   |廖化    ||6500.00|
    

    语句 1 在WHERE中指定连接条件和过滤条件;语句 2 在ON中指定连接条件和过滤条件;语句 3 在ON中指定连接条件,在WHERE中指定其他过滤条件。上面语句不但结果相同,数据库的执行计划也相同。以 MySQL 为例,以上语句的执行计划如下:

    id|select_type|table|partitions|type |possible_keys       |key    |key_len|ref  |rows|filtered|Extra|
    --|-----------|-----|----------|-----|--------------------|-------|-------|-----|----|--------|-----|
     1|SIMPLE     |e    |          |const|PRIMARY,idx_emp_dept|PRIMARY|4      |const|   1|     100|     |
     1|SIMPLE     |d    |          |const|PRIMARY             |PRIMARY|4      |const|   1|     100|     |
    

    📝关于各种数据库的执行计划,可以参考这篇文章

    尽管如此,仍然建议将两个表的连接条件放在ON子句中,将其他过滤条件放在WHERE子句中;这样语义更加明确,更容易阅读和理解。对于上面的示例而言,推荐使用语句 3 的写法。

    对于外连接而言,连接条件只能用ON子句表示,因为WHERE子句无法表示外连接的语义。例如:

    select d.dept_name, e.emp_name, e.sex, e.salary 
    from department d
    left join employee e on (e.dept_id = d.dept_id)
    where d.dept_name = '保卫部';
    dept_name|emp_name|sex|salary|
    ---------|--------|---|------|
    保卫部   |        |   |      |
    

    由于“保卫部”没有员工,我们需要使用外连接返回部门的信息;WHERE条件用于过滤 dept_id = 6 的数据;此时,员工表中返回的都是 NULL。

    📝Oracle 支持在WHERE子句的右/左侧使用 (+) 表示左/右外连接,但是无法表示全外连接。

    对于以上语句,如果将WHERE子句中的过滤条件放到ON子句中,结果将会完全不同:

    select d.dept_name, e.emp_name, e.sex, e.salary 
    from department d
    left join employee e on (e.dept_id = d.dept_id and d.dept_name = '保卫部');
    dept_name|emp_name|sex|salary|
    ---------|--------|---|------|
    行政管理部|        |   |      |
    人力资源部|        |   |      |
    财务部   |        |   |      |
    研发部   |        |   |      |
    销售部   |        |   |      |
    保卫部   |        |   |      |
    

    左外连接返回了所有的部门信息,而且员工信息都为 NULL;显然,这不是我们期望的结果。我们可以通过执行计划分析一下为什么会这样,仍然以 MySQL 为例:

    explain analyze
    select d.dept_name, e.emp_name, e.sex, e.salary 
    from department d
    left join employee e on (e.dept_id = d.dept_id and d.dept_name = '保卫部');
    
    -> Nested loop left join  (cost=7.60 rows=30) (actual time=0.098..0.278 rows=6 loops=1)
        -> Table scan on d  (cost=0.85 rows=6) (actual time=0.052..0.057 rows=6 loops=1)
        -> Filter: (d.dept_name = '保卫部')  (cost=0.71 rows=5) (actual time=0.035..0.035 rows=0 loops=6)
            -> Index lookup on e using idx_emp_dept (dept_id=d.dept_id)  (cost=0.71 rows=5) (actual time=0.020..0.032 rows=4 loops=6)
    

    查询计划显示使用 Nested loop left join 方式执行连接操作;对于 department 使用全表扫描的方式返回 6 行记录;对于 employee 表采用索引(idx_emp_dept)查找,同时使用“d.dept_name = ‘保卫部’”作为过滤条件,循环 6 次返回了 0 行记录;最终返回了上面的结果。

    作为对比,我们可以看看将过滤条件放到WHERE子句时的执行计划:

    explain analyze
    select d.dept_name, e.emp_name, e.sex, e.salary 
    from department d
    left join employee e on (e.dept_id = d.dept_id)
    where d.dept_name = '保卫部';
    
    -> Nested loop left join  (cost=1.98 rows=5) (actual time=0.074..0.078 rows=1 loops=1)
        -> Filter: (d.dept_name = '保卫部')  (cost=0.85 rows=1) (actual time=0.049..0.053 rows=1 loops=1)
            -> Table scan on d  (cost=0.85 rows=6) (actual time=0.039..0.047 rows=6 loops=1)
        -> Index lookup on e using idx_emp_dept (dept_id=d.dept_id)  (cost=1.12 rows=5) (actual time=0.021..0.021 rows=0 loops=1)
    

    查询计划显示使用 Nested loop left join 方式执行连接操作;对于 department 通过扫描返回 1 行记录(d.dept_name = ‘保卫部’);对于 employee 表采用索引(idx_emp_dept)查找,同时使用 dept_id=d.dept_id 作为过滤条件,循环 1 次返回了 0 行记录。

    我们再看一个外连接的示例:

    select d.dept_name, e.emp_name, e.sex, e.salary 
    from department d
    left join employee e on (e.dept_id = d.dept_id and e.emp_name = '赵云');
    dept_name |emp_name|sex|salary  |
    ----------|--------|---|--------|
    行政管理部|        |   |        |
    人力资源部|        |   |        |
    财务部    |        |   |        |
    研发部    |赵云    ||15000.00|
    销售部    |        |   |        |
    保卫部    |        |   |        |
    
    select d.dept_name, e.emp_name, e.sex, e.salary 
    from department d
    left join employee e on (e.dept_id = d.dept_id)
    where e.emp_name = '赵云';
    dept_name|emp_name|sex|salary  |
    ---------|--------|---|--------|
    研发部   |赵云    ||15000.00|
    

    第一个查询语句返回了所有的部门信息,以及部门中名叫“赵云”的员工;第二个查询实际上等价于内连接查询。

    📝一般来说,对于左外连接查询,左表的过滤应该使用WHERE子句,右表的过滤应该使用ON子句;右外连接查询正好相反;全外连接的过滤条件使用ON子句。

    更多 SQL 入门知识可以参考这篇文章

    定期更新数据库领域相关文章,欢迎关注❤️、评论📝、点赞👍!

    展开全文
  • having

    2019-08-14 09:22:55
    having必须和group by一起使用 SELECT Country from Customers GROUP BY Country #且必须在group by的后面 HAVING COUNT(country)> 2; having后面必须接Aggregate 函数 例如:如下sql语句会报如下错误...

    having必须和group by一起使用

     

    SELECT Country

    from Customers

    GROUP BY Country #且必须在group by的后面

    HAVING COUNT(country)> 2;

     

    having后面必须接Aggregate 函数

    例如:如下sql语句会报如下错误,改成HAVING count(CustomerID)>2即可

    SELECT Country

    from Customers

    GROUP BY country

    HAVING CustomerID>2

     

    having必须和group by一起使用

     

    SELECT Country

    from Customers

    GROUP BY Country #且必须在group by的后面

    HAVING COUNT(country)> 2;

     

    having后面必须接Aggregate 函数

    例如:如下sql语句会报如下错误,改成HAVING count(CustomerID)>2即可

    SELECT Country

    from Customers

    GROUP BY country

    HAVING CustomerID>2

     

    展开全文
  • PostgreSQL HAVING 子句

    2020-12-16 16:27:03
    PostgreSQL HAVING 子句 HAVING 子句可以让我们筛选分组后的各组数据。 WHERE 子句在所选列上设置条件,而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。 语法 下面是 HAVING 子句在 SELECT 查询中的位置...
  • sql中where和having 区别

    2014-05-19 22:15:35
    sql中where和havingsql中where和having区别

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 320,597
精华内容 128,238
关键字:

having