[mysql基础文档]-26-内存查询having
引言
在select查询中，where查询只能对MySQL保存在硬盘上的数据进行查询，而使用as定义的变量，或使用where查询返回的结果集是在内存中的，想要对内存中这一部分数据进行查询，可以使用本文介绍的having。
文章目录
0×1.如何使用having进行内存查询
本文所使用的数据表为"mysql基础文档-24-select查询基础"第一部分所创建的cellphone表，请参考：[练习数据表]
having运行方式实例：
--实例1，使用as运算符将结果集保存在内存中，并使用having筛选内存中的结果集
--前面已经提到，数据表中每一列的列头字段，相当于一个变量，变量是能够参与运算的，下面的语句中数据库会读取每条记录selling_price列的值，并用它减去对应这条记录中sale_price的值，最后用as运算符将结果集赋值给以save命名的列
mysql> select (selling_price-sale_price) as save from cellphone;
+--------+
| save |
+--------+
| 150.00 |
| 200.00 |
| 170.00 |
| 161.00 |
| 600.00 |
| 598.00 |
| 300.00 |
| 300.00 |
| 200.00 |
| 189.00 |
| 300.00 |
| 431.00 |
| 300.00 |
| 200.00 |
| 290.00 |
| 200.00 |
| 600.00 |
| 300.00 |
| 200.00 |
| 200.00 |
+--------+
--使用as运算符将结果赋予一个变量，变量是储存在内存中的，而where只能对硬盘中的数据进行筛选，所以这里报错了，告诉你没有save这一列
mysql> select (selling_price-sale_price) as save from cellphone where save>300;
ERROR 1054 (42S22): Unknown column 'save' in 'where clause'
--对保存在内存中的结果集，可以使用having筛选，选出save列中大于300的字段显示
mysql> select (selling_price-sale_price) as save from cellphone having save>300;
+--------+
| save |
+--------+
| 600.00 |
| 598.00 |
| 431.00 |
| 600.00 |
+--------+
--实例2，使用where子运算筛选出一个结果集，这个结果集是保存在内存中的，再用having对这个内存中的结果集进行再次筛选
--cellphone表中标价比打折价高出200的商品名称筛选结果
mysql> select goods_name,sale_price,selling_price from cellphone where selling_price-sale_price>200;
+--------------+------------+---------------+
| goods_name | sale_price | selling_price |
+--------------+------------+---------------+
| GalaxyN9200 | 5388.00 | 5988.00 |
| iPhone6A1586 | 4288.00 | 4886.00 |
| MeizuNote2 | 899.00 | 1199.00 |
| Huawei4X | 999.00 | 1299.00 |
| MX5 | 1899.00 | 2199.00 |
| iPhone5s | 3188.00 | 3619.00 |
| GalaxyN9109W | 2698.00 | 2998.00 |
| iPhone4s | 1398.00 | 1688.00 |
| iPhone6s | 5288.00 | 5888.00 |
| MX4Pro | 1599.00 | 1899.00 |
+--------------+------------+---------------+
--在where后添加having语句，对where筛选结果再次进行筛选，选出标价比打折价高出300的商品名称，where筛选出来的结果集暂时保存在内存中，对内存中的结果集进行操作需要使用having
mysql> select goods_name,sale_price,selling_price from cellphone where selling_price-sale_price>200 having selling_price-sale_price>300;
+--------------+------------+---------------+
| goods_name | sale_price | selling_price |
+--------------+------------+---------------+
| GalaxyN9200 | 5388.00 | 5988.00 |
| iPhone6A1586 | 4288.00 | 4886.00 |
| iPhone5s | 3188.00 | 3619.00 |
| iPhone6s | 5288.00 | 5888.00 |
+--------------+------------+---------------+
P.s：where和having共存，where肯定是在having前面，因为必须要将磁盘上的数据查出来放在内存之后才能用having查到这些数据。
0×2.having内存查询实例
下面来看一个学生成绩查询实例，要求查询出下表中2门及2门以上不及格者（mark<60）的平均成绩：
--创建成绩表score
mysql> create table score(name char(20) not null default '',subject char(20) not null default '',mark tinyint unsigned not null default 0);
--插入6条记录
mysql> insert into score values
-> ('tom','math',90),
-> ('tom','english',50),
-> ('tom','art',59),
-> ('john','math',55),
-> ('john','art',40),
-> ('jack','art',52);
--表完整结构
mysql> select * from score;
+------+---------+------+
| name | subject | mark |
+------+---------+------+
| tom | math | 90 |
| tom | english | 50 |
| tom | art | 59 |
| john | math | 55 |
| john | art | 40 |
| jack | art | 52 |
+------+---------+------+
--将初始问题拆分成不同的步骤，对步骤进行再加工是解决MySQL查询问题的关键，下面的每一步都是对上面一步结果的再加工，而实际工作中只需要写出最后一步即可，这里仅仅是想告诉大家这种思维方式
--第一步，查询出score表中每条记录mark<60的布尔值，0表示false，1表示true
mysql> select name,mark<60 from score;
+------+---------+
| name | mark<60 |
+------+---------+
| tom | 0 |
| tom | 1 |
| tom | 1 |
| john | 1 |
| john | 1 |
| jack | 1 |
+------+---------+
--第二步，使用group by对name字段进行分组，并使用sum()函数将分组后每一组的(mark<60)结果加起来
mysql> select name,sum(mark<60) from score group by name;
+------+--------------+
| name | sum(mark<60) |
+------+--------------+
| jack | 1 |
| john | 2 |
| tom | 2 |
+------+--------------+
--第三步，将sum(mark<60)的结果赋予ms变量，并使用having查询出ms大于等于2的结果，ms>=2就意味着"2门及2门以上不及格"
mysql> select name,sum(mark<60) as ms from score group by name having ms>=2;
+------+------+
| name | ms |
+------+------+
| john | 2 |
| tom | 2 |
+------+------+
--最后一步，添加一列，使用avg()函数计算这些"2门及2门以上不及格"者的平均成绩，得到最终结果
mysql> select name,sum(mark<60) as ms,avg(mark) from score group by name having ms>=2;
+------+------+-----------+
| name | ms | avg(mark) |
+------+------+-----------+
| john | 2 | 47.5000 |
| tom | 2 | 66.3333 |
+------+------+-----------+
对于上面这个问题，还存在着一个经典标准错误答案，请看下面的实例：
--将sum()函数换成count()函数，得到的结果看上去也是对的
mysql> select name,count(mark<60) as ms,avg(mark) from score group by name having ms>=2;
+------+----+-----------+
| name | ms | avg(mark) |
+------+----+-----------+
| john | 2 | 47.5000 |
| tom | 3 | 66.3333 |
+------+----+-----------+
--现在对score表再添加两条记录
mysql> insert into score values
-> ('qingsword','math',100),
-> ('qingsword','english',100);
--使用相同的语句发现了问题，本不该出现在结果中的qingsword，也出现了
mysql> select name,count(mark<60) as ms,avg(mark) from score group by name having ms>=2;
+-----------+----+-----------+
| name | ms | avg(mark) |
+-----------+----+-----------+
| john | 2 | 47.5000 |
| qingsword | 2 | 100.0000 |
| tom | 3 | 66.3333 |
+-----------+----+-----------+
--出现这种问题的关键就是count()函数，还记得第一步中的结果吗？tom三行，john两行，jack一行，count()函数仅仅统计的是行数，只要行数大于等于2的结果，都会出现在这里面，因为jack只有一行，所以第一次操作中，并没有出现这样的错误。
P.s：上面的实例中，错误的地方在于count()统计函数，score<60会得到一个布尔返回值条件为真结果是1，条件为假结果是0，此时不管分数是否小于60，都能得到一行值，而count仅仅只统计行数，这就是为什么第一次错误的查询会看来正确的原因，因为jack只有一条记录，当有一个全部及格且大于或等于两条记录的人出现的时候，count统计出这个人的score<60的结果条数是>=2的，满足having gk>=2，所以显示出这个人。