SQL进阶之路04：HAVING

最新推荐文章于 2023-01-09 07:24:01 发布

码歌

最新推荐文章于 2023-01-09 07:24:01 发布

阅读量497

点赞数 1

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/m0_58768327/article/details/126922045

版权

HAVING子句在SQL里有非常重要的功能，但其价值并没有被深刻认识到！此次我们深入的学习HAVING子句的各种用法，了解特性！

通过例题来了解HAVING子句

如果所示，这是一张有“连续编号”列的表。但实际上缺少了4和7，那么如何将其查询出来？

对“连续编号”列按照升序排序或者降序排序。
循环比较每一行和下一行的编号。

-- 如果有查询结果代表缺失
select '存在确实编号' as gap from user HAVING count(*) != MAX(id);

排序通常会想到ORDER BY，但ORDER BY并不是SQL运算符，而是光标定义的一部分！

如果用COUNT(*)会统计表中所有数据行，而MAX会统计连续字段最大值，当两者并不相符时就是有缺失，这样写出的SQL语句非常的优雅！！！

如果使用集合论描述，MAX计算的是由ID最大值为止的没有缺失的连续编号，而COUNT计算的是表中实际元素行数。

在这里会发现SQL并没有使用group by子句，此时整张表会被聚合为一行，这种情况下HAVING子句是可以使用的！但要么想示例一样使用常量，要么就想SELECT COUNT(*)使用聚合函数。

如何查询缺失编号的最小值呢？

-- 查询缺失编号最小值
select MIN(ID + 1) AS GAP 
FROM USER 
WHERE (ID + 1) NOT IN (SELECT ID FROM USER )

使用NOT IN 进行的子查询针对某一个编号，但不能是已存在的ID,检查比当前ID+1的编号是否存在当前表中。然后获取最小的ID+1就是结果；

注意：如果ID有null值那么结果就不再正确！！

用HAVING子句进行子查询：求众数

求众数：就是查询出现频率最高的！

-- 找出表中INCOME的众数
SELECT INCOME,COUNT(*) AS CONT  FROM USER
GROUP BY INCOME HAVING CONT >= ALL (SELECT COUNT(*) FROM USER GROUP BY INCOME)

思路：众数就是出现最高的频率，对INCOM分组并使用COUNT计数，使用子句查询得到分组后每个INCOME的值。如果CONT字段大于等于子句值就是众数；

（ALL谓词对空集合或NULL进行操作时会出现问题，那么可以得到子查询集合最大的函数来进行运算）

  CONT >= (SELECT MAX(CNT) FROM (
        SELECT COUNT(*) AS CNT FROM USER GROUP BY INCOME
))

使用HAVING子句进行自连接：求中位数


select avg(distinct income) 
from (
		select a.income
		from user a ,user b
		group by a.income having (
			sum(case when b.income >= a.income then 1  else 0 end) >= count(*) /2 
		) and (
			sum(case when b.income <= a.income then 1  else 0 end)  >= count(*) /2
		)
) temp;

查询不包含NULL的集合

COUNT函数可以COUNT(*),COUNT(字段)两种方式使用，他们有两个区别：1：性能。2：count*可以用于查询NULL。而COUNT字段与其他聚合函数一样会排除NULL；、

-- COUNT(*)
SELECT COUNT(*) FROM USER; 
'结果为10'
-- COUNT(NAME)
SELECT COUNT(NAME) FROM USER;
'结果为7'

找出哪个学院提交了全部报告！

如北大三名学生都存在提交日期则认为北大全部提交；

思路：利用COUNT(*)与COUNT(date)，用dpt分组后使用having来进行等值判断即可完成；

select dpt from studens group by dpt having count(*) = count(date)

-- CASE
select dpt from studens 
group by dpt
having count(*) = sum(case when date is null then 0 else 1 end)

查找哪个城市拥有item表的所有商品？


select s1.shop from shopitems s1,item s2 where s1.item = s2.item 
group by s1.shop
having count(s1.item) = (select  count(item) from item)

select shop from shopitems group by shop having count(item) = (select  count(item) from item)

思路分析：可以通过分组计数与子查询计算item的数量来进行比较；

那只想找到刚好拥有香烟的城市呢？


select shop from shopitems group by shop having count(item) = (select count(*) from item where item = '香烟')

那如果只想找到刚好拥有item表中三个商品的城市的城市呢？

select s1.*,s2.item from shopitems s1 left join item s2 on s1.item = s2.item
group by s1.shop 
having 
	count(s1.item) = (select count(item) from item) and count(s2.item) = (select count(item) from item)

我们先用左连接连接两张表，这时未匹配item商品的城市商品位置为NULL而我们使用聚合函数COUNT(item)刚好将NULL排除在外这样我们就顺利的查询出相应的城市了

结果：

总结：

01. 表不是文件，记录也没有顺序，所以 SQL 不进行排序。
02. SQL 不是面向过程语言，没有循环、条件分支、赋值操作。
03. SQL 通过不断生成子集来求得目标集合。SQL 不像面向过程语言那样通过画流程图来思考问题，而是通过画集合的关系图来思考。
04. GROUP BY 子句可以用来生成子集。 05. WHERE 子句用来调查集合元素的性质，而 HAVING 子句用来调查集合本身的性质。
HAVING后可以使用常量和聚合函数