美文网首页
HAVING子句的力量

HAVING子句的力量

作者: 鸿雁长飞光不度 | 来源:发表于2018-03-06 15:32 被阅读0次

在以前的 SQL 标准里HAVING子句只能出现在GROUP BY后面,后来的SQL标准中HAVING子句已经可以单独使用了。

1.寻找缺失的编号

寻找缺失的编号.png
SELECT '有缺失' 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.png
SELECT 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的数量

练习题

  1. 在“寻找缺失的编号”部分,我们写了一条 SQL 语句,让程序只在存在缺失的编号时返回结果。请将 SQL 语句修改成始终返回一行结果,即存在缺失的编号时返回“存在缺失的编号”,不存在缺失的编号时返回“不存在缺失的编号”。
SELECT CASE COUNT(*) != MAX(seq) WHEN  1 THEN '存在缺失的编号' ELSE '不存在缺失的编号' END result FROM SeqTbl  
  1. 全体学生都在 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

相关文章

  • HAVING子句的力量

    在以前的 SQL 标准里HAVING子句只能出现在GROUP BY后面,后来的SQL标准中HAVING子句已经可以...

  • 2020-07-15 SQL刷题笔记一

    having 子句 having子句既可包含聚合函数作用的字段也可包括普通的标量字段 having子句必须于gro...

  • HAVING子句

    有重复的数据行,才有分组并统计的可能性。如果没有重复的数据行,就没有分组的意义。SELECT子句中只能出现分组字段...

  • having子句

    原因:存在HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。 要求: (1)having子句中的...

  • sqlite(17)-Having子句

    Having子句允许指定条件来过滤将出现在最终结果的分组结果。WHERE子句在所选列上设置条件,而Having子句...

  • SQLite Having 子句

    SQLite Having 子句 HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。 WHERE ...

  • MySQL学习day-34:子查询与分页

    一、子查询 可以将子查询放在许多的 SQL 子句中,包括:WHERE 子句; HAVING 子句;FROM 子句。...

  • 搜扣 sql

    HAVING 1、HAVING子句中能够使用三种要素:常数,聚合函数,GROUP BY子句中指定的列名(聚合建)用...

  • SQL HAVING 子句

    印象深刻的一个sql语法,记录一下!来自 w3school(http://www.w3school.com.cn/...

  • MySQL HAVING子句

    在本教程中,我们将学习如何使用MySQL HAVING子句来指定行分组或聚合的过滤条件。MySQL HAVING子...

网友评论

      本文标题:HAVING子句的力量

      本文链接:https://www.haomeiwen.com/subject/vikxfftx.html