mysql having别名以及having和where的区别

转载
https://blog.csdn.net/YPL_ZML/article/details/90743851

select给字段起别名,where不能用,但group by,having,order by后可以

为什么mysql having的条件表达式可以直接使用select后的别名？
SQL语句的语法顺序：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> UNION -> ORDER BY

因此一般不能在having condition中使用select list中的alias。

标准版本sql是不支持,但是mysql优化别名列到对应的group by,having,order by
比如
select coupon_type c,count(1) ct
from coupon_info group by c having ct>1 order by ct desc;
=>
select coupon_type c,count(1) ct
from coupon_info group by coupon_type having count(1)>1 order by count(1) desc;

where和having

区别

1.where是从磁盘筛选数据到内存 haaving是基于where内存中筛选数据

2.having可以使用聚和函数,select别名筛选 where不能

3.只可以用where，不可以用having的情况

select name,price from t_fruit where id > 1;

select name,price from t_fruit having id > 1;

相同

4.如果未group/group返回组中都是单条 where和having效果一致,但是会载入所有数据到内存筛选

select * from t_fruit where price>1;
select * from t_fruit having price>1;