牛逼的Hive-SQL实战

• 查询全体学生的学号与姓名

　　hive> select Sno,Sname from student;

• 查询选修了课程的学生姓名

    hive> select distinct Sname from student inner join sc on student.Sno=Sc.Sno;

—-hive的group by 和集合函数

• 查询学生的总人数

　hive> select count(distinct Sno)count from student;

• 计算1号课程的学生平均成绩

　　hive> select avg(distinct Grade) from sc where Cno=1;

• 查询各科成绩平均分

        hive> select Cno,avg(Grade) from sc group by Cno; 

• 查询选修1号课程的学生最高分数

　select Grade from sc where Cno=1 sort by Grade desc limit 1; 
(注意比较:select * from sc where Cno=1 sort by Grade
          select Grade from sc where Cno=1 order by Grade)     

　　
* 求各个课程号及相应的选课人数

　hive> select Cno,count(1) from sc group by Cno;

• 查询选修了3门以上的课程的学生学号

　hive> select Sno from (select Sno,count(Cno) CountCno from sc group by Sno)a where a.CountCno>3;
或　hive> select Sno from sc group by Sno having count(Cno)>3; 

—-hive的Order By/Sort By/Distribute By Order By.

strict模式下（hive.mapred.mode=s