经典50题 26~50题答案

25、查询各科成绩前三名的记录三个语句
select s.* from (
select s_id,coure_id,score,
rank()over(partition by coure_id order by score desc) a,
row_number()over(partition by coure_id order by score desc) b,
dense_rank()over(partition by coure_id order by score desc) c
from score) s where s.a<4 and s.b<4 and s.c<4;

了解哪些窗口函数，都是什么功能？找一个在某个业务中的应
用？
排名函数
rank()over(),
row_number()over()
dense_rank()over()

聚合函数
sum()over() 求和
count()over() 统计个数
max()over() 最大值
min()over() 最小值
avg()over() 平均值

first_value()over() 取分组内排序后，第一个值
last_value()over()取分组内排序后，最后一个值
lag()over()取出前n行数据
lead()over()取出后n行数据

案例
查询各科成绩最高分、最低分和平均分：以如下形式显示：
课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
select sc.coure_id,c.course,
max(sc.score),
min(sc.score),
avg(nvl(sc.score,0)),
sum(case when sc.score>=60 then 1 else 0 end) dd,
sum(case when sc.score>70 and sc.score<=80 then 1 else 0 end) cc,
sum(case when sc.score>80 and sc.score<=90 then 1 else 0 end) bb,
sum(case when sc.score>90 and sc.score<=100 then 1 else 0 end) aa
from score sc join course c
on sc.coure_id=c.s_id
group by sc.coure_id,c.course;

编写sql实现每个用户截止到每月为止的最大单月访问次数和累
计到该月的总访问次数
userid,month,visits
A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,25
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
A,2015-03,16
A,2015-03,22
B,2015-03,23
B,2015-03,10
B,2015-03,1
create table if not exists auser(
userid string,
month string,
visits int
)
row format delimited
fields terminated by ‘,’;
load data local inpath ‘./auser’ into table auser;
select*from auser;

select aa.userid,aa.month,aa.sum,
max(aa.sum)over(partition by aa.userid order by aa.month),
sum(aa.sum)over(partition by aa.userid order by aa.month)
from
(select userid,month,sum(visits) sum from auser group by userid,month) aa;

select userid
from
select userid,count(day),
row_number()over(partition by userid order by day)
from logs

26、查询每门课程被选修的学生数:
select sc.coure_id,count(s.id)
from
score group by sc.coure_id

27、查询出只有两门课程的全部学生的学号和姓名:
select s.id,s.name
from student s left join score sc
on s.id=sc.id group by sc.coure_id,
having count(score)=2;
28、查询男生、女生人数:
select sex,count()
from student where group by sex
29、查询名字中含有"风"字的学生信息:
select * from student where name like ‘%风%’
30、查询同名同性学生名单，并统计同名人数:
select student.,count() from student group by name,sex hive count()>1
求出每个栏目的被观看次数及累计观看时长
create table video (
uid int,
channel string,
min int
)
row format delimited
fields terminated by ’ ';

load data local inpath ‘./vedio’ into table video;
select *from video;

select
channel,
count(1) num,
sum(min) total
from video
group by channel
;
编写连续7天登录的总人数
create table t1(
uid int,
dt string,
login_status int
)
row format delimited
fields terminated by ’ ';
load data local inpath ‘./t1’ into table t1;
select*from t1;

select
uid,
dt
from
(select
t1.uid uid,
date_sub(t1.dt,t1.rm) dt
from
(select
uid,
dt,
row_number() over(distribute by uid sort by dt) rm
from t1
where
login_status=1) t1)t2
group by uid,dt
having count(uid) >7
;
31、查询1990年出生的学生名单:
select *
from student
where brithday like ‘%1990%’;

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列:
select coure_id,
avg(score) aa
from score
group by coure_id
order by aa desc,coure_id asc;
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩:
select s.id,s.name,avg(sc.score) avg
from student s
join score sc on s.id=sc.s_id
group by s.id,s.name having avg>=85;
34、查询课程名称为"数学"，且分数低于60的学生姓名和分数:
select s.,sc.score
from student s
join score sc on s.id=sc.s_id
join course c on sc.coure_id=c.s_id and c.course=‘数学’
where sc.score<60;
35、查询所有学生的课程及分数情况:
select s.,c.course,sc.score
from student s
join score sc on s.id=sc.s_id
join course c on sc.coure_id=c.s_id;

思路：
设置子查询，先查出每个店铺每月销售额，
通过子查询，使用sum()over()求出累计销售总额
36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数:

select s.name,c.course,sc.score
from student s
join score sc on s.id=sc.s_id
join course c on sc.coure_id=c.s_id
where sc.score>70;

37、查询课程不及格的学生:
select s.name,sc.score
from student s
join score sc on s.id=sc.s_id
where sc.score<60;

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名:
select s.id,s.name
from student s
join score sc on s.id=sc.s_id
join course c on sc.coure_id=c.s_id
and c.s_id=‘01’
where sc.score>80;

39、求每门课程的学生人数:
select c.course,count(sc.coure_id)
from course c
join score sc on c.s_id=sc.coure_id
group by c.course;

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩:
select
s.id,s.name,
max(sc.score),
row_number()over(order by sc.score desc) aa
from student s
join score sc on s.id=sc.s_id
join course c on sc.coure_id=c.s_id
join teach t on c.teach_id=t.teach_id and t.teach_name=‘张三’
group by s.id,s.name,sc.score order by;

select s.*
from
(select
s.id,s.name,
row_number()over(order by sc.score desc) aa,
sc.score
from student s
join score sc on s.id=sc.s_id
join course c on sc.coure_id=c.s_id
join teach t on c.teach_id=t.teach_id and t.teach_name=‘王五’
group by s.id,s.name,sc.score) s
where s.aa<2;

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩:
select
sc.s_id,
sc.coure_id,
s.coure_id,
sc.score
from score sc
join
(select

• from score )s on s.s_id=sc.s_id
  where s.coure_id<sc.coure_id and s.score=sc.score;

42、查询每门课程成绩最好的前三名:
select ss.*
from
(select
s.*,
sc.coure_id,
sc.score,
row_number()over(distribute by sc.coure_id sort by sc.score desc) aa
from student s
join score sc on s.id=sc.s_id) ss
where ss.aa<4;

43、统计每门课程的学生选修人数（超过5人的课程才统计）:
– 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select
coure_id,
count(score) aa
from score
group by coure_id having aa>5 order by aa desc,coure_id asc

44、检索至少选修两门课程的学生学号:
select
s_id,
count(score) cnt
from score
group by s_id having cnt>=2;

45、查询选修了全部课程的学生信息:
select
s.id,s.name
from student s
join course c
left join score sc on c.s_id=sc.coure_id and s.id=sc.s_id
group by s.id,s.name having sum(case when score is null then 1 else 0 end)=0;
46、查询各学生的年龄(周岁):
– 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select s.,
(year(current_date())-year(brithday))age
from student s;
select s.,
year(brithday)
from student s;
SELECT brithday,(year(current_date())-year(brithday) -
(CASE WHEN month(current_date())>month(brithday)
THEN 0 when month(current_date())= month(brithday)
and day(current_date())>=day(brithday) then 0 ELSE 1 END)) AS age
FROM student ;

47、查询本周过生日的学生:
selectfrom student where weekofyear(brithday)=weekofyear(current_date());
48、查询下周过生日的学生:
select
from
student
where
weekofyear(brithday)=weekofyear(current_date()) + 1;
49、查询本月过生日的学生:
selectfrom student where month(current_date())=month(brithday);
50、查询12月份过生日的学生:
selectfrom student where month(brithday)=12;