hive和mysql中where/group by/order by后跟别名的区分

首先说hive，hive的语句执行顺序如下：

from ... on ... join ... where ... group by ... having ... select ... distinct ... distribute by /cluster by ... sort by ... order by ... limit ... union /union all

由于where和group by的执行顺序都在select之前，所以select的别名不可以跟在其之后，order by的执行顺序在select之后，所以order by后可以跟select的别名。

hivesql中：

1. order by后可以跟别名
2. group by和partition by后不可跟别名
3. where后不可跟别名

再说一下mysql，mysql由于内部做了优化机制，所以和hivesql不太一样

mysql中：

1. order by后可以跟别名
2. group by后可以跟别名
3. where后不可跟别名

两者的区别在于：hivesql中group by后不可跟别名，而mysql中group by后可以跟别名；

因为在mysql中做了优化：
 

select 
    case 
        when t.author_level in(1,2) then '1-2级'
        when t.author_level in(3,4) then '3-4级'
        when t.author_level in(5,6) then '5-6级'
        else '' 
    end as level_cnt,
    count(s.issue_id) as num
from author_tb t join answer_tb s on t.author_id = s.author_id 
where s.char_len >= 100
group by level_cnt
order by num desc

等价于

select 
    case 
        when t.author_level in(1,2) then '1-2级'
        when t.author_level in(3,4) then '3-4级'
        when t.author_level in(5,6) then '5-6级'
        else '' 
    end as level_cnt,
    count(s.issue_id) as num
from author_tb t join answer_tb s on t.author_id = s.author_id 
where s.char_len >= 100
group by 
    case 
        when t.author_level in(1,2) then '1-2级'
        when t.author_level in(3,4) then '3-4级'
        when t.author_level in(5,6) then '5-6级'
        else '' 
    end 
order by num desc