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后可以使用常量和聚合函数