SQL进阶-11-having子句

HAVING子句是SQL中非常重要的功能，本文将再次介绍该子句的使用。作者指出：SQL语句处理的对象是集合而不是记录，我们要有面向集合的思考方式。

队伍点名

需求

从下面的表格中找出：所有队员都是待命状态的队伍

SQL实现

全称量化实现

所有的队员都是待命，这是一个全称量化的命题，我们使用not exist来表达

都是待命状态等价于没有不是待命状态

-- 1、全称量化实现

select
	team_id
	,member
from Team t1
where not exists(select *    -- 不存在不是待命的人  也就是全部是待命
                 from Team t2
                 where t1.team_id = t2.team_id
                 and status <> '待命'
                );

having-1-实现

下面我们使用having语句来实现：

select
	team_id
from Team
group by team_id
having count(*) = sum(case when status = '待命' -- 待命为1，求出所有为1的和，等于行记录，则说明全部是待命的状态
                      then 1
                      else 0 end)

自己想到的方法也和下面的特征标记方法类似：

having-2-实现

select
	team_id
from Team
group by team_id
having max(status) = '待命'
and min(status) = '待命';   -- 最大值和最小值一样，说明只存在一种状态

将条件放在select子句中：

select
	team_id
	,case when max(status) = '待命' and min(status) = '待命'
		then '全部待命' else '人手不够'
		end as status
from Team
group by team_id;

单重集合和多重集合

需求

从下面的原材料表中找出重复的产地及材料

锌重复的东京
钛钢重复的名古屋

SQL实现

having实现

满足需求城市的特点：排除重复元素前后的个数不同！！！

如果不存在重复元素，不管是否加上distinct可选项，count的结果是相同的

select center  -- 查出存在重复的地方
from City
group by center
having count(material) <> count(distinct material);  -- 去重前后个数不同，说明存在重复的记录

select
	center
	,case when count(material) <> count(distinct material) then '存在重复复'
	else '不存在重复' end as status
from City
group by center;

exists实现

使用exists性能会变好写；如果想查出不存在重复材料的生产地，改成not exists即可

select center, material
from Material m1
where exists (select * from Material m2
              where m1.center = m2.center
              and m1.receive_date <> m2.receive_date
              and m1.material = m2.material
             );

缺失编号问题

查找一条序列是否存在缺失的编号

1
2
3

select '存在缺失编号' as gap
from Seqtable
having count(*) <> max(seq) - min(seq) + 1;  -- 行记录和最值的差+1不等，说明存在重复行

-- 表为空也返回记录

select
	case when count(*) = 0 then '表为空'
			when count(*) <> max(seq) - min(seq) + 1 then '存在缺失编号'
	else '连续' end as gap
from Seqtable;

查找最小的缺失值编号的SQL语句，去掉起始值是1的限制：

select
	case when count(*) = 0 or min(seq) > 1 then 1   -- 空表或者最小值大于1，则返回1
	else (select min(seq + 1)
        from Seqtale s1
        where not exists (select * from Setable s2  -- not  exists能够处理NULL的情况，not in不能
                 where s2.seq = s1.seq + 1)) end
from Seqtable;

学生成绩查询

查询75%以上的学生分数都在80分以上的班级

select class
from Test
group by class
having count(*) * 0.75 <= sum(case when score >= 80   -- 大于80分的人数
                              then 1 else 0 end
                             );

分数在50分以上的男生人数比分数在50分以上的女生的人数多的班级

select class
from Test
group by class
having sum(case when score >= 50 and sex='男' then 1 else 0 end) >   -- 两个条件指定之后求和
			sum(case when score >= 50 and sex='女' then 1 else 0 end);

女生平均分比男生平均分高的班级

select class
from Test
group by class
having avg(case when sex='男' then score else NULL end) <   -- 男生的平均分；空集返回NULL
			avg(case when sex='女' then score else NULL end);