大数据基础：HQL 入门必练50题（16-30题）

16、检索"01"课程分数小于60，按分数降序排列的学生信息:

select t1.sid,sname,sbirth,ssex,t2.sscore from student t1
inner join
(select sid,sscore from score where cid = 1 and sscore < 60) t2
on t1.sid = t2.sid
order by t2.sscore desc;

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩:

select sid,avg(sscore) avg_score from score group by sid;--平均成绩和学号

select sname,t2.sscore,avg_score from student t1 inner join
(select a.sid,a.sscore,avg_score from score a inner join 
(select sid,avg(sscore) avg_score from score group by sid) b
on a.sid = b.sid) t2
on t1.sid = t2.sid
order by avg_score desc;

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：

以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率:–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

--3.1  查询各科成绩最分、最低分和平均分
select a.cid,cname,max_score,min_score,avg_score from course a
inner join
(select cid,max(sscore) max_score,min(sscore) min_score,round(avg(sscore),2) avg_score 
from score group by cid) b
on a.cid = b.cid;

--3.2  加上及格率，中等率，优良率，优秀率
select sc.cid,c.cname,max(sscore) max_score,min(sscore) min_score,round(avg(sscore),2) avg_score,
   concat(round(100*sum(case when sc.sscore>=85 and sc.sscore<100 then 1 else 0 end)/count(sc.cid),2),'%') as s85_100,
   concat(round(sum(case when sc.sscore>=70 and sc.sscore<85 then 1 else 0 end)/count(sc.cid),2),'%') as s70_85,
   concat(round(sum(case when sc.sscore>=60 and sc.sscore<70 then 1 else 0 end)/count(sc.cid),2),'%') as s60_70,
   concat(round(sum(case when sc.sscore>=0 and sc.sscore<60 then 1 else 0 end)/count(sc.cid),2),'%') as s0_60
from score sc
   left join course c on c.cid=sc.cid group by sc.cid,c.cname;
   
--3.3  Tab键上的``（反引单号）可以为表格导入汉字标题
select sc.cid,c.cname,max(sscore) max_score,min(sscore) min_score,round(avg(sscore),2) avg_score,
   concat(round(100*sum(case when sc.sscore>=85 and sc.sscore<100 then 1 else 0 end)/count(sc.cid),2),'%') as `优秀率`,
   concat(round(sum(case when sc.sscore>=70 and sc.sscore<85 then 1 else 0 end)/count(sc.cid),2),'%') as `优良率`,
   concat(round(sum(case when sc.sscore>=60 and sc.sscore<70 then 1 else 0 end)/count(sc.cid),2),'%') as `中等率`,
   concat(round(sum(case when sc.sscore>=0 and sc.sscore<60 then 1 else 0 end)/count(sc.cid),2),'%') as `及格率`
from score sc
   left join course c on c.cid=sc.cid group by sc.cid,c.cname;

19、按各科成绩进行排序，并显示排名:

select cid,sscore,
rank() over(partition by cid order by sscore desc) as rn1, 
dense_rank() over(partition by cid order by sscore desc) as rn2, 
row_number() over(partition by cid order by sscore desc) as rn3 from score;
--实现分组排名
  --  ROW_NUMBER()  从1开始，按照顺序，生成分组内记录的序列
  -- RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位  
  -- DENSE_RANK() 生成数据项在分组中的排名，排名相等在名次中不会留下空位

20、查询学生的总成绩并进行排名:

select sid,sum(sscore) sum_score from score group by sid;--总成绩

select sname,sum_score from student a 
left join 
(select sid,sum(sscore) sum_score from score group by sid) b
on a.sid = b.sid;
--所有人总成绩，含空值

--1.先求总成绩再联表
select t1.sname,t1.sum_score,
rank() over(order by t1.sum_score desc) as rn1,
dense_rank() over(order by t1.sum_score desc) as rn2, 
row_number() over(order by t1.sum_score desc) as rn3  
from 
(select sname,sum_score from student a 
left join 
(select sid,sum(sscore) sum_score from score group by sid) b
on a.sid = b.sid) t1;

--2.-先联表再求总成绩
select t1.sname,t1.sum_score,
rank() over(order by t1.sum_score desc) as rn1,
dense_rank() over(order by t1.sum_score desc) as rn2, 
row_number() over(order by t1.sum_score desc) as rn3 
from 
(select c.sname,sum(sscore) sum_score from
(select sname,sscore from student a 
left join score b on a.sid = b.sid) c group by c.sname
) t1;

21、查询不同老师所教不同课程平均分从高到低显示:

select cid,avg(sscore) avg_score from score group by cid;--学科平均成绩

select tname,avg_score from teacher a
inner join course b on a.tid = b.tid
inner join (select cid,avg(sscore) avg_score from score group by cid) c
on b.cid = c.cid;

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩:

select a.sid,sname,sbirth,ssex,c.cname,b.sscore from student a
inner join
(
select t1.* from
    (select * from score where cid=1 order by sscore desc limit 1,2) t1
union all 
select t2.* from
    (select * from score where cid=2 order by sscore desc limit 1,2) t2
union all 
select t3.* from
    (select * from score where cid=3 order by sscore desc limit 1,2) t3
) b 
on a.sid = b.sid
inner join
course c on b.cid = c.cid;

23、统计各科成绩各分数段人数：

课程编号,课程名称,优秀[100-85],优良[85-70],中等[70-60],不及格[0-60]及所占百分比

select sc.cid,c.cname,
   sum(case when sc.sscore>=85 and sc.sscore<100 then 1 else 0 end) as 100_85,
   round(sum(case when sc.sscore>=85 and sc.sscore<100 then 1 else 0 end)/count(sc.cid),2) as perc100_85,
   sum(case when sc.sscore>=70 and sc.sscore<85 then 1 else 0 end) as 85_70,
   round(sum(case when sc.sscore>=70 and sc.sscore<85 then 1 else 0 end)/count(sc.cid),2) as perc85_70,
   sum(case when sc.sscore>=60 and sc.sscore<70 then 1 else 0 end) as 70_60,
   round(sum(case when sc.sscore>=60 and sc.sscore<70 then 1 else 0 end)/count(sc.cid),2) as perc70_60,
   sum(case when sc.sscore>=0 and sc.sscore<60 then 1 else 0 end) as  60_0,
   round(sum(case when sc.sscore>=0 and sc.sscore<60 then 1 else 0 end)/count(sc.cid),2) as perc60_0
from score sc
   left join course c on c.cid=sc.cid group by sc.cid,c.cname;

24、查询学生平均成绩及其名次:

select t1.sname,t1.avg_score,
rank() over(order by t1.avg_score desc) as rn1,
dense_rank() over(order by t1.avg_score desc) as rn2, 
row_number() over(order by t1.avg_score desc) as rn3  
from 
(select sname,avg_score from student a 
left join 
(select sid,avg(sscore) avg_score from score group by sid) b
on a.sid = b.sid) t1;

25、查询各科成绩前三名的记录

select t1.sid,sname,sbirth,ssex,t3.cname,t2.sscore,rn2 from student t1
inner join 
(
select * from
(select score.*,
dense_rank() over(partition by cid order by sscore desc) as rn2 from score) a
where a.rn2 in(1,2,3)
) t2 
on t1.sid = t2.sid
inner join course t3 on t3.cid = t2.cid;

26、查询每门课程被选修的学生数:

select cname,cn from course a inner join
(select cid,count(1) cn from score group by cid) b 
on a.cid = b.cid;

27、查询出只有两门课程的全部学生的学号和姓名:

select t1.sid,sname from student t1
inner join
(
select * from
(select sid,count(1) cn from score group by sid) a
where a.cn = 2
) t2
on t1.sid = t2.sid;

28、查询男生、女生人数:

select ssex,count(1) cn from student group by ssex;

29、查询名字中含有"风"字的学生信息:

select * from student where sname like '%风%';

30、查询同名同性学生名单，并统计同名人数:

select sname,ssex,count(1) cn from student group by sname,ssex having cn >1;