1-4 having子句的力量-SQL进阶教程

寻找缺失的编号

-- 如果有查询结果,说明存在缺失的编号
select '存在缺失的编号' gap
from SeqTbl
having count(*)<>max(seq);
/*
having子句是可以脱离group by子句单独使用的
但是在这种情况下select子句就不能引用原来表里的字段了,要么像示例里一样使用常量,要么像 select count(*)这样使用聚合函数
*/

-- 查询缺失编号的最小值
select min(seq+1) gap
from SeqTbl
where (seq+1) not in (select seq from SeqTbl);

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

-- 求众数的SQL语句(1)：使用谓词
select income,count(*) cnt
from Graduates
group by income
having count(*)>=all(
	select count(*)
	from Graduates
	group by income
);

-- 求众数的SQL语句(2) ：使用极值函数
select income,count(*) cnt
from Graduates
group by income
having count(*)>=(
	select max(cnt)
	from(
		select count(*) cnt
		from Graduates
		group by income
	) tmp
);

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

-- 求中位数的SQL语句：在having子句中使用非等值自连接
select avg(distinct income)
from (
	select T1.income
	from Graduates T1,Graduates T2
	group by T1.income
	having sum(case when T2.income>=T1.income then 1 else 0 end)>=count(*)/2
	and sum(case when T2.income<=T1.income them then 1 else 0 end)>=count(*)/2
) tmp;

查询不包含null的集合

count(*) 可以用于null，而count(列名) 与其他聚合函数一样，要先排除掉null的行再进行统计

-- 在对包含null列使用时，count(*)和count(列名) 的查询结果是不同的
select count(*),count(col_1)
from NullTbl;

-- 查询“提交日期”列内不包含null的学院(1) ：使用count函数
select dpt
from Students
group by dpt
having count(*)=count(sbmt_date);

-- 查询“提交日期”列内不包含null的学院(2) ：使用case表达式
select dpt
from Students
group by dpt
having count(*)=sum(case when sbmt_date is not null then 1 else 0 end);

用关系除法运算进行购物篮分析

-- 查询啤酒、纸尿裤和自行车同时在库的店铺
select SI.shop
from ShopItems SI,Items I
where SI.item=I.item
group by SI.shop
having count(SI.item)=(select count(item) from Items);

-- 只选择没有剩余商品的店铺(东京)：使用外连接和count函数
select SI.shop
from ShopItems SI
left join Items I
on SI.item=I.item
group by SI.shop
having count(SI.item)=(select count(item) from Items)
and count(I.item)=(select count(item) from Items);

本节要点

①表不是文件，记录也没有顺序，所以SQL不进行排序
②SQL不是面向过程语言，没有循环、条件分支、赋值操作
③SQL通过不断生成子集来求得目标集合。SQL不像面向过程语言那样通过画流程图来思考问题，而是通过画集合的关系图来思考
④group by子句可以用来生成子集
⑤where子句用来调查集合元素的性质，而having子句用来调查集合本身的性质