转载
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;