[AppleScript] 纯文本查看 复制代码
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]
– 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:
[AppleScript] 纯文本查看 复制代码
select student.*,a.s_score as 01_score,b.s_score as 02_score
from student
join score a on student.s_id=a.s_id and a.c_id='01'
left join score b on student.s_id=b.s_id and b.c_id='02'
where a.s_score>b.s_score;
–答案2
[AppleScript] 纯文本查看 复制代码
select student.*,a.s_score as 01_score,b.s_score as 02_score
from student
join score a on a.c_id='01'
join score b on b.c_id='02'
where a.s_id=student.s_id and b.s_id=student.s_id and a.s_score>b.s_score;
– 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数:
[AppleScript] 纯文本查看 复制代码
select student.*,a.s_score as 01_score,b.s_score as 02_score
from student
join score a on student.s_id=a.s_id and a.c_id='01'
left join score b on student.s_id=b.s_id and b.c_id='02'
where a.s_score<b.s_score;
–答案2
[AppleScript] 纯文本查看 复制代码
select student.*,a.s_score as 01_score,b.s_score as 02_score
from student
join score a on a.c_id='01'
join score b on b.c_id='02'
where a.s_id=student.s_id and b.s_id=student.s_id and a.s_score<b.s_score;
– 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:
[AppleScript] 纯文本查看 复制代码
select student.s_id,student.s_name,tmp.平均成绩 from student
join (
select score.s_id,round(avg(score.s_score),1)as 平均成绩
from score group by s_id)as tmp
on tmp.平均成绩>=60
where student.s_id = tmp.s_id
–答案2
[AppleScript] 纯文本查看 复制代码
select student.s_id,student.s_name,round(avg (score.s_score),1) as 平均成绩 from student
join score on student.s_id = score.s_id
group by student.s_id,student.s_name
having avg (score.s_score) >= 60;
– 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:
– (包括有成绩的和无成绩的)
[AppleScript] 纯文本查看 复制代码
select student.s_id,student.s_name,tmp.avgScore from student
join (
select score.s_id,round(avg(score.s_score),1)as avgScore from score group by s_id)as tmp
on tmp.avgScore < 60
where student.s_id=tmp.s_id
union all
select s2.s_id,s2.s_name,0 as avgScore from student s2
where s2.s_id not in
(select distinct sc2.s_id from score sc2);
–答案2
[AppleScript] 纯文本查看 复制代码
select score.s_id,student.s_name,round(avg (score.s_score),1) as avgScore from student
inner join score on student.s_id=score.s_id
group by score.s_id,student.s_name
having avg (score.s_score) < 60
union all
select s2.s_id,s2.s_name,0 as avgScore from student s2
where s2.s_id not in
(select distinct sc2.s_id from score sc2);
– 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:
[AppleScript] 纯文本查看 复制代码
select student.s_id,student.s_name,(count(score.c_id) )as total_count,sum(score.s_score)as total_score
from student
left join score on student.s_id=score.s_id
group by student.s_id,student.s_name ;
– 6、查询"李"姓老师的数量:
select t_name,count(1) from teacher where t_name like '李%' group by t_name;
– 7、查询学过"张三"老师授课的同学的信息:
select student.* from studentjoin score on student.s_id =score.s_idjoin course on course.c_id=score.c_idjoin teacher on course.t_id=teacher.t_id and t_name='张三';
– 8、查询没学过"张三"老师授课的同学的信息:
select student.* from studentleft join (select s_id from score join course on course.c_id=score.c_id join teacher on course.t_id=teacher.t_id and t_name='张三')tmpon student.s_id =tmp.s_idwhere tmp.s_id is null;
– 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息:
select * from studentjoin (select s_id from score where c_id =1 )tmp1 on student.s_id=tmp1.s_idjoin (select s_id from score where c_id =2 )tmp2 on student.s_id=tmp2.s_id;
– 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息:
select student.* from studentjoin (select s_id from score where c_id =1 )tmp1 on student.s_id=tmp1.s_idleft join (select s_id from score where c_id =2 )tmp2 on student.s_id =tmp2.s_idwhere tmp2.s_id is null;
– 11、查询没有学全所有课程的同学的信息:
–先查询出课程的总数量
select count(1) from course;
–再查询所需结果
select student.* from studentleft join( select s_id from score group by s_id having count(c_id)=3)tmpon student.s_id=tmp.s_idwhere tmp.s_id is null;
–方法二(一步到位):
select student.* from studentjoin (select count(c_id)num1 from course)tmp1left join( select s_id,count(c_id)num2 from score group by s_id)tmp2on student.s_id=tmp2.s_id and tmp1.num1=tmp2.num2where tmp2.s_id is null;
– 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息:
select student.* from studentjoin (select c_id from score where score.s_id=01)tmp1join (select s_id,c_id from score)tmp2 on tmp1.c_id =tmp2.c_id and student.s_id =tmp2.s_idwhere student.s_id not in('01')group by student.s_id,s_name,s_birth,s_sex;
– 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息:
–备注:hive不支持group_concat方法,可用 concat_ws(’|’, collect_set(str)) 实现
select student.*,tmp1.course_id from studentjoin (select s_id ,concat_ws('|', collect_set(c_id)) course_id from score group by s_id having s_id not in (1))tmp1 on student.s_id = tmp1.s_idjoin (select concat_ws('|', collect_set(c_id)) course_id2 from score where s_id=1)tmp2 on tmp1.course_id = tmp2.course_id2;
– 14、查询没学过"张三"老师讲授的任一门课程的学生姓名:
select student.* from student left join (select s_id from score join (select c_id from course join teacher on course.t_id=teacher.t_id and t_name='张三')tmp2 on score.c_id=tmp2.c_id )tmp on student.s_id = tmp.s_id where tmp.s_id is null;
– 15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩:
select student.s_id,student.s_name,tmp.avg_score from studentinner join (select s_id from score where s_score<60 group by score.s_id having count(s_id)>1)tmp2on student.s_id = tmp2.s_idleft join ( select s_id,round(AVG (score.s_score)) avg_score from score group by s_id)tmp on tmp.s_id=student.s_id;
– 16、检索"01"课程分数小于60,按分数降序排列的学生信息:
select student.*,s_score from student,scorewhere student.s_id=score.s_id and s_score<60 and c_id='01'order by s_score desc;
– 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:
select a.s_id,tmp1.s_score as chinese,tmp2.s_score as math,tmp3.s_score as english, round(avg (a.s_score),2) as avgScorefrom score aleft join (select s_id,s_score from score s1 where c_id='01')tmp1 on tmp1.s_id=a.s_idleft join (select s_id,s_score from score s2 where c_id='02')tmp2 on tmp2.s_id=a.s_idleft join (select s_id,s_score from score s3 where c_id='03')tmp3 on tmp3.s_id=a.s_idgroup by a.s_id,tmp1.s_score,tmp2.s_score,tmp3.s_score order by avgScore desc;
– 18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率:
–及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
select course.c_id,course.c_name,tmp.maxScore,tmp.minScore,tmp.avgScore,tmp.passRate,tmp.moderate,tmp.goodRate,tmp.excellentRates from coursejoin(select c_id,max(s_score) as maxScore,min(s_score)as minScore, round(avg(s_score),2) avgScore, round(sum(case when s_score>=60 then 1 else 0 end)/count(c_id),2)passRate, round(sum(case when s_score>=60 and s_score<70 then 1 else 0 end)/count(c_id),2) moderate, round(sum(case when s_score>=70 and s_score<80 then 1 else 0 end)/count(c_id),2) goodRate, round(sum(case when s_score>=80 and s_score<90 then 1 else 0 end)/count(c_id),2) excellentRatesfrom score group by c_id)tmp on tmp.c_id=course.c_id;
– 19、按各科成绩进行排序,并显示排名:
– row_number() over()分组排序功能(mysql没有该方法)
select s1.*,row_number()over(order by s1.s_score desc) Ranking from score s1 where s1.c_id='01'order by noRanking ascunion all select s2.*,row_number()over(order by s2.s_score desc) Ranking from score s2 where s2.c_id='02'order by noRanking ascunion all select s3.*,row_number()over(order by s3.s_score desc) Ranking from score s3 where s3.c_id='03'order by noRanking asc;
– 20、查询学生的总成绩并进行排名:
select score.s_id,s_name,sum(s_score) sumscore,row_number()over(order by sum(s_score) desc) Ranking from score ,student where score.s_id=student.s_id group by score.s_id,s_name order by sumscore desc;
后续部分参见:
https://blog.csdn.net/Thomson617/article/details/83280617
Hive下的SQL语法总结: