mysql中where和having条件查询的区别

having一般跟在group by后面,先对查询结果分组,再根据having条件,筛选满足条件的组,限制字段最好选择分组的字段,可以操纵使用聚合函数(sum()、count()、avg()等)的列,也可以操纵没有使用聚合函数的列。
where是在查询结果分组前,筛选符合where条件的数据,不能操纵使用聚合函数的列。
group by一般和聚合函数配合使用,having 的字段一般也只对这些聚合函数处理过的列采用,但如果使用这些聚合函数但不使用group by就会报错。
如果test表内容为

columnA columnB columnC
apple yes green
apple yes yellow
apple no red
tomato yes green
tomato no yellow
tomato yes red

使用select *,count(columnA) as num from test;和select *,count(columnB) as num from test;均会报错,单独使用count()也不会报错,而使用group by之后就不会报错,如下面查询语句:

mysql> select *,count(columnA) as num from test group by columnA;
+---------+---------+---------+-----+
| columnA | columnB | columnC | num |
+---------+---------+---------+-----+
| apple | yes | green | 3 |
| tomato | yes | green | 3 |
+---------+---------+---------+-----+
2 rows in set (0.01 sec)

mysql> select count(*) as num from test ;
+-----+
| num |
+-----+
| 6 |
+-----+
1 row in set (0.00 sec)


用下面查询语句的结果,比较用where和having及group by的用法。

mysql> select *,count(*) as num from test;
ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

mysql> select *,count(*) as num from test group by columnB;
+---------+---------+---------+-----+
| columnA | columnB | columnC | num |
+---------+---------+---------+-----+
| apple | no | red | 2 |
| apple | yes | green | 4 |
+---------+---------+---------+-----+
2 rows in set (0.00 sec)

使用聚合函数后,选择符合条件的列,用having可以,而where不行。

mysql> select *,count(*) as num from test group by columnB having num>2;
+---------+---------+---------+-----+
| columnA | columnB | columnC | num |
+---------+---------+---------+-----+
| apple | yes | green | 4 |
+---------+---------+---------+-----+
1 row in set (0.00 sec)

mysql> select *,count(*) as num from test where num>2 group by columnB ;
ERROR 1054: Unknown column 'num' in 'where clause'


下面两个结果显示了where 和 having的区别,having 是先分组后再在分组后的结果中筛选,where 是先筛选结果再分组。

mysql> select *,count(*) as num from _xwh_test group by columnB having columnA='apple';
+---------+---------+---------+-----+
| columnA | columnB | columnC | num |
+---------+---------+---------+-----+
| apple | no | red | 2 |
| apple | yes | green | 4 |
+---------+---------+---------+-----+
2 rows in set (0.01 sec)

mysql> select *,count(*) as num from test where columnA='apple' group by columnB ;
+---------+---------+---------+-----+
| columnA | columnB | columnC | num |
+---------+---------+---------+-----+
| apple | no | red | 1 |
| apple | yes | green | 2 |
+---------+---------+---------+-----+
2 rows in set (0.00 sec)

where也可以和having结合使用。

mysql> select *,count(*) as num from test where columnA='apple' group by columnB having num>2;
Empty set (0.01 sec)

mysql中where和having条件查询的区别》上有 1 条评论

发表评论

电子邮件地址不会被公开。