SQL 中 HAVING 常见的使用方法

目录
  • HAVING 子句
    • 1. 寻找缺失的编号
    • 2. 查询缺少编号的最小值
    • 3. 求众数
    • 4. 求中位数
    • 5. 查询不包含 NULL 的集合
    • 6. 关系除法运算
  • 总结

HAVING 子句

始终要记得 SQL是一种基于“面向集合”思想设计的语言 。

1. 寻找缺失的编号

查询这张表里是否存在数据缺失。当前这张表的编号并不是连续的，缺少了 4 和 7（这里给出的列是有序的，实际情景下很有可能是无序的）。

-- 如果有查询结果，说明存在缺失的编号
SELECT 1 AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);

如果这个查询结果有 1 行，说明存在缺失的编号；如果 1 行都没有，说明不存在缺失的编号。这是因为，如果用 COUNT(*) 统计出来的行数等于“连续编号”列的最大值，就说明编号从开始到最后是连续递增的，中间没有缺失。如果有缺失，COUNT(*) 会小于 MAX(seq) ，这样 HAVING 子句就变成真了。这个解法只需要 3 行代码，十分优雅。

上面的 SQL 语句里没有 GROUP BY 子句，此时整张表会被聚合为一行。这种情况下 HAVING 子句也是可以使用的。在以前的 SQL 标准里，HAVING 子句必须和 GROUP BY 子句一起使用，所以到现在也有人会有这样的误解。但是，按照现在的 SQL 标准来说， HAVING 子句是可以单独使用的 。不过这种情况下，就不能在 SELECT 子句里引用原来的表里的列了，要么就得像示例里一样使用常量，要么就得像 SELECT COUNT(*) 这样使用聚合函数。

也可以认为是对空字段进行了 GROUP BY 操作，只不过省略了 GROUP BY 子句。如果使用窗口函数时不指定 PARTITION BY 子句，就是把整个表当作一个分区来处理的，思路与这里也是一样的。

2. 查询缺少编号的最小值

-- 查询缺失编号的最小值
SELECT MIN(seq + 1) AS gap
FROM SeqTbl
WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);

要注意！

  • 如果表里没有编号 1，那么缺失编号的最小值应该是 1，但是这两条 SQL 语句都不能得出正确的结果
  • 如果表里包含 NULL ，那么这条 SQL 语句也不能得出正确的结果

3. 求众数

-- 求众数的SQL：使用极值函数
SELECT income, COUNT(*) AS cnt
FROM Graduates
GROUP BY income
HAVING COUNT(*) >= ( SELECT MAX(cnt) FROM ( SELECT COUNT(*) AS cnt FROM Graduates GROUP BY income) TMP ) ;

这里使用MAX极值函数而不是ALL谓词是因为极值函数可以避免Null值带来的问题。详细内容可以看 一文详解SQL 中的三值逻辑 这篇文章。

4. 求中位数

将集合里的元素按照大小分为上半部分和下半部分两个子集，同时让这 2 个子集共同拥有集合正中间的元素。

这样，共同部分的元素的平均值就是中位数：

-- 求中位数的SQL 语句：在HAVING 子句中使用非等值自连接
SELECT AVG(DISTINCT income) -- 这里一定要去重后 再求平均
FROM (
        SELECT T1.income
        FROM Graduates T1, Graduates T2
        GROUP BY T1.income
        -- S1 的条件 小于等于T2的数量大于等于全部的一半
        HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2
        -- S2 的条件 大于等于T2的数量大于等于全部的一半
        AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END) >= COUNT(*) / 2
        -- 同时满足  小于等于T2的数量大于等于全部的一半 且 大于等于T2的数量大于等于全部的一半 即说明T2在前后两部分的中间的交集中
) TMP;

5. 查询不包含 NULL 的集合

COUNT 函数的使用方法有 COUNT(*) 和 COUNT( 列名 ) 两种，

它们的区别有两个：

  • 第一个是性能上的区别；第二个是 COUNT(*) 可以用于 NULL ，而 COUNT( 列名 ) 与其他聚合函数一样，要先排除掉NULL 的行再进行统计。
  • 第二个区别也可以这么理解：COUNT(*) 查询的是所有行的数目，而 COUNT( 列名 ) 查询的则不一定是。

现在需要查找哪些学院的学生全部都提交了报告（即理学院、经济学院）。

SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = COUNT(sbmt_date);

同样可以使用case表达式

SELECT dpt
FROM Students
GROUP BY dpt
HAVING COUNT(*) = SUM(CASE WHEN sbmt_date IS NOT NULL THEN 1 ELSE 0 END);

在这里，CASE 表达式的作用相当于进行判断的函数，用来判断各个元素（= 行）是否属于满足了某种条件的集合。这样的函数我们称为特征函数（characteristic function），或者从定义了集合的角度来将它称为定义函数

6. 关系除法运算

现在需要查询囊括了表 Items 中所有商品的店铺（仙台店和东京店）。

SELECT SI.shop
FROM ShopItems SI, Items I
WHERE SI.item = I.item
GROUP BY SI.shop
HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items)

同样也可以写出 只包含 Items 中所有商品的店铺（东京店）

SELECT SI.shop
FROM ShopItems SI LEFT OUTER JOIN Items I
ON SI.item=I.item
GROUP BY SI.shop
HAVING COUNT(SI.item) = (SELECT COUNT(item) FROM Items) -- 条件1
       AND COUNT(I.item) = (SELECT COUNT(item) FROM Items); -- 条件2

总结

到此这篇关于SQL 中 HAVING 常见的使用方法的文章就介绍到这了,更多相关SQL HAVING内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • 深入浅析SQL中的group by 和 having 用法

    一.sql中的group by 用法解析: Group By语句从英文的字面意义上理解就是"根据(by)一定的规则进行分组(Group)". 作用:通过一定的规则将一个数据集划分成若干个小的区域,然后针对若干个小区域进行数据处理. 注意:group by 是先排序后分组! 举例说明:如果要用到group by 一般用到的就是"每"这个字, 例如现在有一个这样的需求:查询每个部门有多少人.就要用到分组的技术 select DepartmentID as '部门名称',

  • mysql之group by和having用法详解

    GROUP BY语法可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表. select子句中的列名必须为分组列或列函数,列函数对于group by子句定义的每个组返回一个结果. 某个员工信息表结构和数据如下: id name dept salary edlevel hiredate 1 张三 开发部 2000 3 2009-10-11 2 李四 开发部 2500 3 2009-10-01 3 王五 设计部 2600 5 2010-10-02 4 王六 设计部 2300 4

  • sql中的 where 、group by 和 having 用法解析

    废话不多说了,直接给大家贴代码了,具体代码如下所示: --sql中的 where .group by 和 having 用法解析 --如果要用到group by 一般用到的就是"每这个字" 例如说明现在有一个这样的表:每个部门有多少人 就要用到分组的技术 select DepartmentID as '部门名称',COUNT(*) as '个数' from BasicDepartment group by DepartmentID --这个就是使用了group by +字段 进行了分组

  • 浅谈sql语句中GROUP BY 和 HAVING的使用方法

    在介绍GROUP BY 和 HAVING 子句前,我们必需先讲讲sql语言中一种特殊的函数:聚合函数, 例如SUM, COUNT, MAX, AVG等.这些函数和其它函数的根本区别就是它们一般作用在多条记录上. SELECT SUM(population) FROM bbc 这里的SUM作用在所有返回记录的population字段上,结果就是该查询只返回一个结果,即所有 国家的总人口数. having是分组(group by)后的筛选条件,分组后的数据组内再筛选 where则是在分组前筛选 通过

  • mysql having用法解析

    having的用法 having字句可以让我们筛选成组后的各种数据,where字句在聚合前先筛选记录,也就是说作用在group by和having字句前.而 having子句在聚合后对组记录进行筛选. SQL实例: 一.显示每个地区的总人口数和总面积． SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region 先以region把返回记录分成多个组,这就是GROUP BY的字面含义.分完组后,然后用聚合函数对每组中 的不同

  • SQL中where子句与having子句的区别小结

    前言: Where和Having都是对查询结果的一种筛选,说的书面点就是设定条件的语句.下面这篇文章就来给大家介绍下SQL中where子句与having子句的区别,下面话不多说了,来一起看看详细的介绍吧 1.where 不能放在GROUP BY 后面 2.HAVING 是跟GROUP BY 连在一起用的,放在GROUP BY 后面,此时的作用相当于WHERE 3.WHERE 后面的条件中不能有聚集函数,比如SUM(),AVG()等,而HAVING 可以 Where和Having都是对查询结果的一

  • SQL中where和having的区别详解

    SQL中where和having的区别详解

    概念 where where是一个约束声明,在查询数据库的结果返回之前对数据库中的查询条件进行约束,再返回结果前起作用,并且where后不能使用"聚合函数". 聚合函数 对一组值执行计算,并返回单个值,也被称为组函数,经常与 SELECT 语句的 GROUP BY 子句的HAVING一同使用.例如 AVG 返回指定组中的平均值COUNT 返回指定组中项目的数量MAX 返回指定数据的最大值.MIN 返回指定数据的最小值.SUM 返回指定数据的和,只能用于数字列,空值被忽略. having

  • SQL 中 HAVING 常见的使用方法

    目录 HAVING 子句 1. 寻找缺失的编号 2. 查询缺少编号的最小值 3. 求众数 4. 求中位数 5. 查询不包含 NULL 的集合 6. 关系除法运算 总结 HAVING 子句 始终要记得 SQL是一种基于“面向集合”思想设计的语言 . 1. 寻找缺失的编号 查询这张表里是否存在数据缺失.当前这张表的编号并不是连续的,缺少了 4 和 7(这里给出的列是有序的,实际情景下很有可能是无序的). -- 如果有查询结果,说明存在缺失的编号 SELECT 1 AS gap FROM SeqTbl

  • SQL中的三种去重方法小结

    目录 distinct group by row_number 在使用SQL提数的时候,常会遇到表内有重复值的时候,比如我们想得到 uv (独立访客),就需要做去重. 在 MySQL 中通常是使用 distinct 或 group by子句,但在支持窗口函数的 sql(如Hive SQL.Oracle等等) 中还可以使用 row_number 窗口函数进行去重. 举个栗子,现有这样一张表 task: task_id order_id start_time 1 123 2020-01-05 1 2

  • SQL中from_unixtime函数的使用方法实例

    目录 1.from_unixtime的语法及用法 (1)语法:from_unixtime(timestamp ,date_format) (2)用法:将时间戳转为指定日期格式. (3)常见的日期格式 2.实例 总结 1.from_unixtime的语法及用法 (1)语法:from_unixtime(timestamp ,date_format) 即from_unixtime(时间戳 ,日期格式 参数说明 timestamp :时间戳,可为一串数字,也可为字段. date_format:时间格式,

  • SQL中EXPLAIN命令的使用方法

    在日常工作中,我们会有时会开慢查询去记录一些执行时间比较久的SQL语句,找出这些SQL语句并不意味着完事了,些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划,查看该SQL语句有没有使用上了索引,有没有做全表扫描,这都可以通过explain命令来查看.所以我们深入了解MySQL的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用. explain显示了mysql如何使用索引来处理select语句以及连接表.可

  • sql 中 case when 语法使用方法

    没有,用case when 来代替就行了. 例如,下面的语句显示中文年月 复制代码 代码如下: select getdate() as 日期,case month(getdate()) when 11 then '十一' when 12 then '十二' else substring('一二三四五六七八九十', month(getdate()),1) end+'月' as 月份 CASE 可能是 SQL 中被误用最多的关键字之一.虽然你可能以前用过这个关键字来创建字段,但是它还具有更多用法.例

  • MSSQL批量替换语句 在SQL SERVER中批量替换字符串的方法

    方法一:(这种是最常用的,因为很多大段的内容都使用text ntext等数据类型,而我们通常也是替换里面的内容) varchar和nvarchar类型是支持replace,所以如果你的text不超过8000可以先转换成前面两种类型再使用replace 替换 text ntext 数据类型字段的语句 复制代码 代码如下: update 表名 set 字段名=replace(cast(与前面一样的字段名 as varchar(8000)) ,'原本内容','想要替换成什么') 方法二:(替换其他数据

  • mybatis-plus中lambdaQuery()与lambdaUpdate()比较常见的使用方法总结

    mybatis-plus中lambdaQuery()与lambdaUpdate()比较常见的使用方法总结

    目录 简介 前言 学生类 根据id查询 带条件的查询 根据id查询对象 查询学生集合 常见的分页查询 根据id删除 带条件的删除 删除名称为张三 年龄等于15的学生 修改 根据id修改 修改 将学号为1的学生的地址修改为湖南 查询年龄小于20的学生集合 其他以此内推 总结 简介 MyBatis-Plus (opens new window)(简称 MP)是一个 MyBatis (opens new window)的增强工具,在 MyBatis 的基础上只做增强不做改变,为简化开发.提高效率而生.

  • SQL中字符串中包含字符的判断方法

    在sql中我判断包含字符串我们可使用很多方法,如like,replace,charindex函数都可实现我们要的功能,下面我来给各位介绍判断字符串包含字符串sql语句. 通过2个函数CHARINDEX和PATINDEX以及通配符的灵活使用 函数:CHARINDEX和PATINDEX CHARINDEX:查某字符(串)是否包含在其他字符串中,返回字符串中指定表达式的起始位置. PATINDEX:查某字符(串)是否包含在其他字符串中,返回指定表达式中某模式第一次出现的起始位置:如果在全部有效的文本和

  • 在SQL Server中使用CLR调用.NET方法实现思路

    介绍 我们一起来做个示例,在.NET中新建一个类,并在这个类里新建一个方法,然后在SQL Server中调用这个方法.按照微软所述,通过宿主 Microsoft .NET Framework 2.0 公共语言运行库 (CLR),SQL Server 2005显著地增强了数据库编程模型. 这使得开发人员可以用任何CLR语言(如C#.VB.NET或C++等)来写存储过程.触发器和用户自定义函数. 我们如何实现这些功能呢? 为了使用CLR,我们需要做如下几步: 1.在.NET中新建一个类,并在这个类里

  • SQL中遇到多条相同内容只取一条的最简单实现方法

    SQL中遇到多条相同内容只取一条的最简单实现方法

    SQL中经常遇到如下情况,在一张表中有两条记录基本完全一样,某个或某几个字段有些许差别, 这时候可能需要我们踢出这些有差别的数据,即两条或多条记录中只保留一项. 如下:表timeand 针对time字段相同时有不同total和name的情形,每当遇到相同的则只取其中一条数据,最简单的实现方法有两种 1.select time,max(total) as total,name from timeand group by time;//取记录中total最大的值 或 select time,min(

随机推荐