在 group by 、having、order by 可以使用别名 示例:
select count(1) a,substr(landlord_name,1,2) as c from mjc_lease_details d
GROUP BY c having c like '丁%' order by c
等价于
select count(1) a,substr(landlord_name,1,2) as c from mjc_lease_details d
GROUP BY substr(landlord_name,1,2) having substr(landlord_name,1,2) like '丁%' order by substr(landlord_name,1,2)
where中 不可以使用别名
select count(1) a,substr(landlord_name,1,2) as c from mjc_lease_details d
where c like '丁%'
GROUP BY c order by c
总结
例如像hive,Oracle这种并不支持这种写法,所以尽量还是写规范点,套一层子查询