在以前的 SQL 标准里HAVING子句只能出现在GROUP BY后面,后来的SQL标准中HAVING子句已经可以单独使用了。
1.寻找缺失的编号
寻找缺失的编号.pngSELECT '有缺失' as 'result' FROM SeqTbl HAVING COUNT(*) != MAX(seq)
上面没有用GROUP BY 直接使用了HAVING条件,所以结果没有会被合并成一行。这时SELECT的字段要么是常量,要么是聚合函数否则都是错误的或者没有意义的。
2.查出缺失的最小的编号
SELECT MIN(seq + 1) AS gap
FROM SeqTbl
WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);
或者join方式
SELECT S1.seq+1 FROM SeqTbl S1 LEFT JOIN SeqTbl S2 ON S1.seq + 1 = S2.seq WHERE S2.seq IS NULL LIMIT 1
但是这两种都有缺点,开头和结尾出缺失不准确,后续补充。
3.求众数
求众数.png这一种多了一层查询
SELECT income ,COUNT(*) cnt FROM Graduates GROUP BY income HAVING COUNT(*) =
(
SELECT MAX(num)
FROM (SELECT COUNT(*) num FROM Graduates GROUP BY income) T)
这种利用了ALL关键字,减少了一层查询,但是ALL 谓词用于 NULL 或空集时会出现问题,这点要结合实际考虑。
SELECT income, COUNT(*) AS cnt
FROM Graduates
GROUP BY income
HAVING COUNT(*) >= ALL ( SELECT COUNT(*)
FROM Graduates
GROUP BY income);
3.求中位数
SELECT AVG(DISTINCT income)
FROM (SELECT T1.income
FROM Graduates T1, Graduates T2
GROUP BY T1.income
-- S1 的条件
HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1 ELSE 0 END)
>= COUNT(*) / 2
-- S2 的条件
AND SUM(CASE WHEN T2.income <= T1.income THEN 1 ELSE 0 END)
>= COUNT(*) / 2 ) TMP;
4.求所售的商品和items表包含的完全一样的店铺名称。
Items.png ShopItems.pngSELECT ShopItems.shop from ShopItems LEFT JOIN Items ON
ShopItems.item = items.item GROUP BY shop HAVING COUNT(ShopItems.item) = (SELECT COUNT(*) FROM Items) -- 仅仅判断这个会把仙台也计算进来
AND COUNT(Items.item) = (SELECT COUNT(*) FROM Items) -- count(Items.item) 计算JOIN后不为NULL的数量
练习题
- 在“寻找缺失的编号”部分,我们写了一条 SQL 语句,让程序只在存在缺失的编号时返回结果。请将 SQL 语句修改成始终返回一行结果,即存在缺失的编号时返回“存在缺失的编号”,不存在缺失的编号时返回“不存在缺失的编号”。
SELECT CASE COUNT(*) != MAX(seq) WHEN 1 THEN '存在缺失的编号' ELSE '不存在缺失的编号' END result FROM SeqTbl
-
全体学生都在 9 月份提交了报告的学院
Student.png
SELECT dpt FROM Students GROUP BY dpt HAVING COUNT(*) = COUNT(sbmt_date)
AND COUNT(*) = SUM(CASE WHEN sbmt_date LIKE '2005-09-%' THEN 1 ELSE 0 END)
3.对于没有备齐全部商品类型的店铺,我们也希望返回的一览表能展示这些店铺缺少多少种商品
SELECT SI.shop, COUNT(SI.item) ,(SELECT COUNT(*) FROM Items) - COUNT(SI.item)
FROM ShopItems SI JOIN Items I
ON SI.item=I.item
GROUP BY SI.shop
网友评论