SQL进阶-11-having子句

-- 1、全称量化实现

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

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

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

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

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;

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
             );

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;

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;

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

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);