//五種子句是有嚴(yán)格順序的：

where → group by → having → order by → limit

//where和having的區(qū)別:

//where是先過濾再分組(對原始數(shù)據(jù)過濾),where限定聚合函數(shù)

hive> select count(*),age from tea where id>18 group by age;

//having是先分組再過濾(對每個(gè)組進(jìn)行過濾,having后只能跟select中已有的列)

hive> select age,count(*) c from tea group by age having c>2;

//group by后面沒有的列,select后面也絕不能有(聚合函數(shù)除外)

hive> select ip,sum(load) as c from logs? group by ip sort by c desc limit 5;

//distinct關(guān)鍵字返回唯一不同的值(返回age和id均不相同的記錄)

hive> select distinct age,id from tea;

//hive只支持Union All,不支持Union

//hive的Union All相對sql有所不同,要求列的數(shù)量相同,并且對應(yīng)的列名也相同,但不要求類的類型相同(可能是存在隱式轉(zhuǎn)換吧)

select name,age from tea where id<80

union all

select name,age from stu where age>18;