having子句使用

求众数

对于如下表求其中的众数。

该处思路是将收入相同的的毕业生汇总到一个集合里，然后从汇总后的各个集合中找出个数最多的一个。

select income,count(*)
from Graduates
group by income
having count(*) >= all(select count(*)
					from Graduates
					group by income);

此处使用谓词 all 需要注意子句结果不能为 null 或空集否则将查询不到结果，为避免此种情况也可以使用极值函数来进行改写。

select income,count(*)
from Graduates
group by income
having count(*) >= (select max(cnt)
					from(select count(*) as cnt
					from Graduates
					group by income)tmp);

求中位数

求中位数这里用到的方法是将集合里的元素按照大小分为上半部分和下半部分两个子集，同时让着两个集合拥有集合正中间的元素，这样共同部分的元素的平均值就是中位数。
例如先前的 Graduates 表，其中位数就是第 5 和第 6 两个数字的平均数。概括一下当总数据量是偶数时中位数就是count(*)/2,与count(*)/2+1两个数的平均数，再概括一下就是顺数count(*)/2位与倒数 count(*)/2 位数的平均数，这个对总数据量是奇数的集合也成立，然后就可以写出如下 sql。

select T1.income
from Graduates T1,Graduates T2
group by T1.income
--取出从前向后数count(*)/2以后的数据(5,6,7,8,9,10)
having sum(case when T1.income<=T2.income then 1 else 0 end)>=count(*)/2
--取出从后向前数count(*)/2以前的数据(6,5,4,3,2,1)
and    sum(case when T1.income>=T2.income then 1 else 0 end)>=count(*)/2;
--两个限制条件共同取出5，6两个位置的数据

--再取平均数得出的就是中位数
select avg(income)
from(
select T1.income
from Graduates T1,Graduates T2
group by T1.income
having sum(case when T1.income<=T2.income then 1 else 0 end)>=count(*)/2
and    sum(case when T1.income>=T2.income then 1 else 0 end)>=count(*)/2)tmp
;

查询不包含NULL的集合

对于如下表 Students，需要找出提交日期全部不为空的学院名。

如果只是单纯的使用 is not null 来进行查询的话，会将文学院也错误的统计进去，正确的做法是使用 count 来进行计算，sql 如下：

select dpt
from Students
group by dpt
having count(*)=count(sbmt_date);

这样就可以得到正确的结果，这样写是由于 count(*) 不会忽略空值，但是 count(字段) 会忽略掉空值，所以如果 count(*)=count(sbmt_date) 则说明该学院中 sbmt_date 全部不为空。

关系除法

现在要找出 ShopItems 中包含 Items 中全部项的店铺。

select shop
from Items I,ShopItems SI
where I.item=SI.item
group by SI.shop
having count(*)=(select count(*) from Items);

select shop
from ShopItems SI
group by SI.shop
having sum(case when exists(select SI.item from Items where SI.item=Items.item) then 1 else 0 end)=(select count(*)from Items)

这两种写法都能实现上述的需求。
如果需要完全匹配的项，例如仙台的店中有 Items 表中不存在的窗帘，若要将这种条目去除则可以使用如下两种写法：

select shop
from ShopItems SI left join Items I
on SI.item = I.item
group by shop
having count(SI.item)=(select count(*) from Items)
and count(I.item)=(select count(*) from Items);

select shop
from ShopItems SI
group by shop
having sum(case when exists(select SI.item from Items where SI.item=Items.item) then 1 else 0 end)=(select count(*)from Items)
and count(*)=(select count(*)from Items);