欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

MySQL--HAVING子句的力量

程序员文章站 2022-06-01 08:48:51
...

本文根据《SQL进阶教程》（[日]MICK/著 吴炎昌/译）所写笔记。

寻找缺失的编号

现在有一张表seqtbl，虽然编号那一列叫做连续编号，但实际上编号并不是连续的，缺失4和7.我们现在要做的就是查找是否有缺失值：

CREATE TABLE SeqTbl
(seq  INTEGER PRIMARY KEY,
 name VARCHAR(16) NOT NULL);

INSERT INTO SeqTbl VALUES(1,	'迪克');
INSERT INTO SeqTbl VALUES(2,	'安');
INSERT INTO SeqTbl VALUES(3,	'莱露');
INSERT INTO SeqTbl VALUES(5,	'卡');
INSERT INTO SeqTbl VALUES(6,	'玛丽');
INSERT INTO SeqTbl VALUES(8,	'本');

mysql> select * from seqtbl;
+-----+--------+
| seq | name   |
+-----+--------+
|   1 | 迪克   |
|   2 | 安     |
|   3 | 莱露   |
|   5 | 卡     |
|   6 | 玛丽   |
|   8 | 本     |
+-----+--------+

-- 查找是否有缺失值
mysql> select '存在缺失值的编号' as gap
    -> from seqtbl
    -> having count(*)<>max(seq);
+--------------------------+
| gap                      |
+--------------------------+
| 存在缺失值的编号         |
+--------------------------+

如果这个查询结果只有一行，说明存在缺失的编号，如果一行都没有，说明不存在缺失的编号。大家会注意到，上面的语句没有group by子句，此时整张表会被聚合为一行。这种情况下HAVING子句也是可以使用的。HAVING子句是可以单独使用的。不过这种情况下，就不能在select子句里引用原来的表里的列了，要么就得像实例里一样使用常量，要么就像select count(*)这样使用聚合函数。

现在，我们已经知道表中存在缺失值了，那么如何求出缺失的编号是多少呢？

-- 查询缺失编号的最小值
mysql> select min(seq+1) as gap from seqtbl
    -> where (seq+1) not in (select seq from seqtbl);
+------+
| gap  |
+------+
|    4 |
+------+

-- 查询全部缺失值
mysql> select seq+1 as gap from seqtbl
    -> where (seq+1) not in (select seq from seqtbl);
+-----+
| gap |
+-----+
|   4 |
|   7 |
|   9 |
+-----+

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

下面有一张graduates 表，我们求一下众数（方法一，使用谓词。方法二，使用极值函数）：

CREATE TABLE Graduates
(name   VARCHAR(16) PRIMARY KEY,
 income INTEGER NOT NULL);

INSERT INTO Graduates VALUES('桑普森', 400000);
INSERT INTO Graduates VALUES('迈克',     30000);
INSERT INTO Graduates VALUES('怀特',   20000);
INSERT INTO Graduates VALUES('阿诺德', 20000);
INSERT INTO Graduates VALUES('史密斯',     20000);
INSERT INTO Graduates VALUES('劳伦斯',   15000);
INSERT INTO Graduates VALUES('哈德逊',   15000);
INSERT INTO Graduates VALUES('肯特',     10000);
INSERT INTO Graduates VALUES('贝克',   10000);
INSERT INTO Graduates VALUES('斯科特',   10000);

mysql> select * from graduates;
+-----------+--------+
| name      | income |
+-----------+--------+
| 劳伦斯    |  15000 |
| 史密斯    |  20000 |
| 哈德逊    |  15000 |
| 怀特      |  20000 |
| 斯科特    |  10000 |
| 桑普森    | 400000 |
| 肯特      |  10000 |
| 贝克      |  10000 |
| 迈克      |  30000 |
| 阿诺德    |  20000 |
+-----------+--------+

-- 求众数
-- 方法一：使用谓词
mysql> select income,count(*) as cnt
    -> from graduates
    -> group by income
    -> having count(*) >=all(select count(*) from graduates group by income);
+--------+-----+
| income | cnt |
+--------+-----+
|  10000 |   3 |
|  20000 |   3 |
+--------+-----+

--方法二：使用极值函数
mysql> select income,count(*) as cnt
    -> from graduates
    -> group by income
    -> having count(*) >= (select max(cnt) from (select count(*) as cnt from graduates group by income)tmp);
+--------+-----+
| income | cnt |
+--------+-----+
|  10000 |   3 |
|  20000 |   3 |
+--------+-----+

用HAVING子句进行自连接：求中位数（没看懂）

如果集合中的元素个数为偶数，则取中间两个元素的平均值作为中位数。做法是，将集合里的元素按照大小分为上半部分和下半部分两个子集，同时让这2个子集共同拥有集合正中间的元素。

mysql> 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;
+----------------------+
| AVG(DISTINCT income) |
+----------------------+
|           17500.0000 |
+----------------------+

查询不包含null的集合

count函数的使用方法有count(*)和count(列名)两种，他们的区别有两个：第一个是性能上的区别，第二个是count(*)可以用于null，而count（列名）与其他聚合函数一样，要先排除掉null的行再进行统计。
下面我们用一张nulltbl表来看一下区别：

mysql> create table nulltbl
    -> (col_1 char(4));
mysql> insert into nulltbl values(null);
Query OK, 1 row affected (0.04 sec)

mysql> insert into nulltbl values(null);
Query OK, 1 row affected (0.02 sec)

mysql> insert into nulltbl values(null);
Query OK, 1 row affected (0.04 sec)

mysql> select * from nulltbl;
+-------+
| col_1 |
+-------+
| NULL  |
| NULL  |
| NULL  |
+-------+

mysql> select count(*),count(col_1) from nulltbl;
+----------+--------------+
| count(*) | count(col_1) |
+----------+--------------+
|        3 |            0 |
+----------+--------------+

例如：这里有一张存储了学生调教报告的日期的表students,学生提交报告后，“提交日期”会被写入到日期，而提交之前是null。现在我们需要从这张表里找出哪些学院的学生全部都提交了报告（即理学院、经济学院）。如果只是用where sbmt_date is not null这样的条件查询，文学院也会被包含进来。正确的做法是以“学院”为group by 的列生成子集。

CREATE TABLE Students
(student_id   INTEGER PRIMARY KEY,
 dpt          VARCHAR(16) NOT NULL,
 sbmt_date    DATE);

INSERT INTO Students VALUES(100,  '理学院',   '2005-10-10');
INSERT INTO Students VALUES(101,  '理学院',   '2005-09-22');
INSERT INTO Students VALUES(102,  '文学院',   NULL);
INSERT INTO Students VALUES(103,  '文学院',   '2005-09-10');
INSERT INTO Students VALUES(200,  '文学院',   '2005-09-22');
INSERT INTO Students VALUES(201,  '工学院',   NULL);
INSERT INTO Students VALUES(202,  '经济学院', '2005-09-25');

mysql> select * from students;
+------------+--------------+------------+
| student_id | dpt          | sbmt_date  |
+------------+--------------+------------+
|        100 | 理学院       | 2005-10-10 |
|        101 | 理学院       | 2005-09-22 |
|        102 | 文学院       | NULL       |
|        103 | 文学院       | 2005-09-10 |
|        200 | 文学院       | 2005-09-22 |
|        201 | 工学院       | NULL       |
|        202 | 经济学院     | 2005-09-25 |
+------------+--------------+------------+

-- 查询“提交日期”列内不包含null的学院：使用count函数
mysql> select dpt from students
    -> group by dpt
    -> having count(*)=count(sbmt_date);
+--------------+
| dpt          |
+--------------+
| 理学院       |
| 经济学院     |
+--------------+

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

case表达式将“提交日期”不是null的行标记为1，将“提交日期”为null的行标记为0.

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

有两张表，全国连锁折扣店的商品表items，以及各个店铺的库存管理表shopitems。

CREATE TABLE Items
(item VARCHAR(16) PRIMARY KEY);
 
CREATE TABLE ShopItems
(shop VARCHAR(16),
 item VARCHAR(16),
    PRIMARY KEY(shop, item));

INSERT INTO Items VALUES('啤酒');
INSERT INTO Items VALUES('纸尿裤');
INSERT INTO Items VALUES('自行车');

INSERT INTO ShopItems VALUES('仙台',  '啤酒');
INSERT INTO ShopItems VALUES('仙台',  '纸尿裤');
INSERT INTO ShopItems VALUES('仙台',  '自行车');
INSERT INTO ShopItems VALUES('仙台',  '窗帘');
INSERT INTO ShopItems VALUES('东京',  '啤酒');
INSERT INTO ShopItems VALUES('东京',  '纸尿裤');
INSERT INTO ShopItems VALUES('东京',  '自行车');
INSERT INTO ShopItems VALUES('大阪',  '电视');
INSERT INTO ShopItems VALUES('大阪',  '纸尿裤');
INSERT INTO ShopItems VALUES('大阪',  '自行车');

mysql> select * from items;
+-----------+
| item      |
+-----------+
| 啤酒      |
| 纸尿裤    |
| 自行车    |
+-----------+
mysql> select * from shopitems;
+--------+-----------+
| shop   | item      |
+--------+-----------+
| 东京   | 啤酒      |
| 东京   | 纸尿裤    |
| 东京   | 自行车    |
| 仙台   | 啤酒      |
| 仙台   | 窗帘      |
| 仙台   | 纸尿裤    |
| 仙台   | 自行车    |
| 大阪   | 电视      |
| 大阪   | 纸尿裤    |
| 大阪   | 自行车    |
+--------+-----------+

我们要查询的是囊括了表items中所有商品的店铺（仙台和东京）。大阪店没有啤酒，所以不是我们的目标。用一下语句查询结果是错误的：

mysql> select distinct shop
    -> from shopitems
    -> where item in (select item from items);
+--------+
| shop   |
+--------+
| 东京   |
| 仙台   |
| 大阪   |
+--------+

这是因为，在where子句里指定的条件只对表里的某一行数据有效。谓词in的条件其只是指定了“店内有啤酒或者纸尿裤或者自行车的店铺”，所以店铺只要有这三种商品中的任何一种就会出现在查询结果中。
正确的SQL语句应该这样写：

mysql> 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);
+--------+
| shop   |
+--------+
| 东京   |
| 仙台   |
+--------+

having子句的子查询select count(item) from items的返回结果是常量3.因此，对商品表和店铺的库存管理表进行连接操作后结果是3行的店铺会被选中。而仙台店则因（仙台，窗帘）的行在表连接时会被排除掉，所以也会被选中。东京店连接后的结果时3也会被选中。
但是需要注意的是，如果把having子句改写成having count(si.item)=count(i.item)，结果就不对了。仙台、东京、大阪3个店都会被选中。这是因为受到操作的影响，count(i.item)的值和表items原本的行数不一样了。

-- count(i.item)的值已经不一定是3了
mysql> select si.shop,count(si.item),count(i.item)
    -> from shopitems si,items i
    -> where si.item=i.item
    -> group by si.shop;
+--------+----------------+---------------+
| shop   | count(si.item) | count(i.item) |
+--------+----------------+---------------+
| 东京   |              3 |             3 |
| 仙台   |              3 |             3 |
| 大阪   |              2 |             2 |
+--------+----------------+---------------+

接下来，我们看看如何排除掉仙台店（仙台店的仓库存在“窗帘”，但商品表里没有窗帘），让结果只出现京东店。这类问题被称为“精确关系除法”，即只选择没有剩余商品的店铺（与此相对，前一个问题被称为“带余除法”。解决这一问题，我们使用外联结：

mysql> select si.shop
    -> from shopitems si left outer join items i
    -> on si.item=i.item
    -> group by si.shop
    -> having count(si.item)=(select count(item) from items)   -- 条件1
    ->    and count(i.item)=(select count(item) from items);   -- 条件2
+--------+
| shop   |
+--------+
| 东京   |
+--------+

以表shopitems为主表进行外连接后，结果如下：

mysql> select si.shop,si.item,i.item
    -> from shopitems si left outer join items i
    -> on si.item=i.item;
+--------+-----------+-----------+
| shop   | item      | item      |
+--------+-----------+-----------+
| 东京   | 啤酒      | 啤酒      |
| 东京   | 纸尿裤    | 纸尿裤    |
| 东京   | 自行车    | 自行车    |
| 仙台   | 啤酒      | 啤酒      |
| 仙台   | 窗帘      | NULL      |
| 仙台   | 纸尿裤    | 纸尿裤    |
| 仙台   | 自行车    | 自行车    |
| 大阪   | 电视      | NULL      |
| 大阪   | 纸尿裤    | 纸尿裤    |
| 大阪   | 自行车    | 自行车    |
+--------+-----------+-----------+

执行下面语句：

mysql> select si.shop
    -> ,count(si.item),count(i.item)
    -> from shopitems si left outer join items i
    -> on si.item=i.item
    ->  group by si.shop;
+--------+----------------+---------------+
| shop   | count(si.item) | count(i.item) |
+--------+----------------+---------------+
| 东京   |              3 |             3 |
| 仙台   |              4 |             3 |
| 大阪   |              3 |             2 |
+--------+----------------+---------------+

显然，只有东京符合。

本节小结

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

上一篇： 170Hz高刷+2K屏 华硕27英寸电竞小金刚Plus史低

下一篇： 发布npm包npm publish 400 maintainers

推荐阅读