hive基本操作1

select * from city order by city_code desc limit 10;

select province_name,count(city_name) as cnt from city group by province_name order by cnt desc limit 10;

select count(distinc(province_name)) from city;

select count(1) from
(
select province_name from city group by province_name
)a;

select province_name,count(city_name) as cnt from city group by province_name having cnt=1 ;//这两种方法都可以
select province_name,cnt from
(
select province_name,count(city_name) as cnt from city group by province_name
)a
where cnt=1;

select * from city where city_code=430100 or city_code=430200;

select * from city where city_code in(430100,430200);

select * from city where city_code>=430100 and city_code<=430200;

select * from city where city_code not in(430100,430200);