SQL进阶教程 1-4 HAVING子句的力量（含连接）

1. 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

2. 寻找缺失的编号

select '存在缺失的编号' as gap from seqtable 
having count(*)<>max(seq);

3. 求众数

求元素数最多的集合，可以用极值函数max

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

4.求中位数

运用非等值自连接

select avg(income) from 
(select t1.income from graduates t1,graduates t2 group by t1.income
having sum(case when t2.income >= t2.income then 1 else 0 end)>=count(*)/2
and sum(case when t2.income <= t2.income then 1 else 0 end)>=count(*)/2) tmp;

5.count()和null

count(*)查询所有行，count(列名)与其他聚合函数一样，会排除null。
要查询哪些学院的学生全部都提交了报告。

使用count()

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

使用case

select dpt from students group by dpt having count(*)=sum(
case when sbmt_date is not NULL then 1 else 0 end);

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

查询囊括了表items中所有商品的店铺。

select shop from items,shopitems where items.item=shopitems.item
group by shop having count(shopitems.item)=(select count(item) from items);

注意having里是shopitems的=items的，判断时=前是变量，后是标准。

选择shop含有全部items（排除店铺，该店铺含有不存在items里的商品）。
(精确关系除法)

sql的四种连接——左外连接、右外连接、内连接、全连接
一、内连接

满足条件的记录才会出现在结果集中。

二、 左外连接（left outer join，outer可省略）

左表全部出现在结果集中，若右表无对应记录，则相应字段为NULL

三、右外连接（right outer join，outer可省略）

右表全部出现在结果集中，若左表无对应记录，则相应字段为NULL

四、全连接（full outer join，outer可省略）

全外连接=左外连接+右外连接

select * from shopitems left join items on shopitems.item=items.item;

select shop from shopitems left join items on shopitems.item=items.item
group by shop having count(shopitems.item)=(select count(item) from items)
and count(items.item)=(select count(item) from items);

注意是shopitems left join items

1-4-1 使结果总是返回一行数据

select (case when count(*)<>max(seq) then '存在缺失的编号' else '不存在缺失的编号' end)as gap from seqtable;

1-4-2 特征函数

查询“全体学生都在9月份提交了报告的学院”

select dpt from students group by dpt having 
count(*)=sum(case when sbmt_date BETWEEN "2005-9-1" and "2005-9-30" then 1 else 0 end);

1-4-3 返回店铺缺少多少种商品

select shop,count(shopitems.item) my_item_cnt,(select count(item) from items)-count(shopitems.item) diff_cnt
from shopitems,items where shopitems.item=items.item group by shop;

my_item_cnt是shop拥有items表里多少个商品，diff_cnt是缺少的。