mysql-Invalid use of group function-聚合函数不能直接使用在where后面-使用exists，外查询与子查询应条件关联

TABLE `tb_test_emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dept_id` int(11) NULL DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `score` int(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ; INSERT INTO `tb_test_emp` VALUES (1, 1, ‘张三‘, 50); INSERT INTO `tb_test_emp` VALUES (2, 1, ‘李四‘, 60); INSERT INTO `tb_test_emp` VALUES (3, 1, ‘王五‘, 70); INSERT INTO `tb_test_emp` VALUES (4, 2, ‘赵六‘, 60); INSERT INTO `tb_test_emp` VALUES (5, 2, ‘周七‘, 80); INSERT INTO `tb_test_emp` VALUES (6, 3, ‘朱八‘, 100); INSERT INTO `tb_test_emp` VALUES (7, 3, ‘龙九‘, 50); CREATE TABLE `tb_test_dept` ( `id` int(11) NOT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ); INSERT INTO `tb_test_dept` VALUES (1, ‘销售部‘); INSERT INTO `tb_test_dept` VALUES (2, ‘人事部‘); INSERT INTO `tb_test_dept` VALUES (3, ‘讲师部‘);

2、案例1：查询高于平均分数的员工信息

 

 报错信息：Invalid use of group function即“集函数的无效用法”

聚合函数不能直接使用在where后面

正确写法：

<span style="color: #000000;"># 利用子查询先查出来再比较
</span><span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">FROM</span> tb_test_emp <span style="color: #0000ff;">WHERE</span> score <span style="color: #808080;">></span> (<span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">AVG</span>(score) <span style="color: #0000ff;">FROM</span> tb_test_emp ); 

3、WHERE和HAVING的区别

where 子句的作用是对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

4、案例2：查询不及格员工的部门信息

<span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>  <span style="color: #0000ff;">FROM</span> tb_test_dept <span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">in</span> (<span style="color: #0000ff;">SELECT</span> dept_id <span style="color: #0000ff;">FROM</span> tb_test_emp  <span style="color: #0000ff;">WHERE</span> score <span style="color: #808080;"><</span> <span style="color: #800000; font-weight: bold;">60</span>);

5、案例3：查询所有员工都及格的部门信息-反查询（部门没有不及格的员工）

<span style="color: #000000;"># 意思就是没有不及格的员工
</span><span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>  <span style="color: #0000ff;">FROM</span> tb_test_dept <span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">not</span> <span style="color: #808080;">in</span> (<span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">DISTINCT</span> dept_id <span style="color: #0000ff;">FROM</span> tb_test_emp  <span style="color: #0000ff;">WHERE</span> score <span style="color: #808080;"><</span> <span style="color: #800000; font-weight: bold;">60</span>);

6、案例2和3改为exists-EXISTS 判断子查询是否返回null，如果返回null那么就匹配失败，否则匹配成功

EXISTS 判断子查询是否返回null，如果返回null那么就匹配失败，否则匹配成功

使用exists，外查询（父查询）一般会和子查询发生条件关联 

AND d.id =<span> e.dept_id</span>

<span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>  <span style="color: #0000ff;">FROM</span> tb_test_dept  d <span style="color: #0000ff;">WHERE</span>  <span style="color: #808080;">EXISTS</span> (<span style="color: #0000ff;">SELECT</span> dept_id <span style="color: #0000ff;">FROM</span> tb_test_emp e <span style="color: #0000ff;">WHERE</span> score <span style="color: #808080;"><</span> <span style="color: #800000; font-weight: bold;">60</span> <span style="color: #808080;">AND</span> d.id <span style="color: #808080;">=</span><span style="color: #000000;"> e.dept_id);
</span><span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>  <span style="color: #0000ff;">FROM</span> tb_test_dept d <span style="color: #0000ff;">WHERE</span>  <span style="color: #808080;">not</span> <span style="color: #808080;">EXISTS</span>  (<span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">DISTINCT</span> dept_id <span style="color: #0000ff;">FROM</span> tb_test_emp e <span style="color: #0000ff;">WHERE</span> score <span style="color: #808080;"><</span> <span style="color: #800000; font-weight: bold;">60</span> <span style="color: #808080;">AND</span> d.id <span style="color: #808080;">=</span> e.dept_id );

 

mysql-Invalid use of group function-聚合函数不能直接使用在where后面-使用exists，外查询与子查询应条件关联

标签：image   src   class   讲师   bsp   nbsp   标准   信息   销售