经典的SparkSQL/Hive-SQL/MySQL面试-练习题
经典的SparkSQL/Hive-SQL/MySQL面试-练习题
大数据学习与分享
发布于 2020-10-23 07:24:35
发布于 2020-10-23 07:24:35
代码可运行
运行总次数：0
代码可运行
第一题
需求：
已知一个表order，有如下字段:date_time，order_id，user_id，amount。
数据样例:2020-10-10,1003003981,00000001,1000，请用sql进行统计:
(1)2019年每个月的订单数、用户数、总成交金额。
(2)2020年10月的新客数(指在2020年10月才有第一笔订单)实现：
(1)
SELECT t1.year_month,
count(t1.order_id) AS order_cnt,
count(DISTINCT t1.user_id) AS user_cnt,
sum(amount) AS total_amount
FROM
(SELECT order_id,
user_id,
amount,
date_format(date_time,'yyyy-MM') year_month
FROM test_db.test3
WHERE date_format(date_time,'yyyy') = '2019') t1
GROUP BY t1.year_month;
(2)
SELECT count(user_id)
FROM test_db.test3
GROUP BY user_id
HAVING date_format(min(date_time),'yyyy-MM')='2020-10';第二题
需求：
存在如下客户访问商铺的数据，访问日志存储的表名为user_visit，访客的用户id为user_id，被访问的店铺名称为shop_name。
数据如下：
+--------+-----------+
|user_id | shop_name|
+--------+-----------+
| u1|beautiful_a|
| u2|beautiful_b|
| u1|beautiful_b|
| u3|beautiful_c|
| u4|beautiful_b|
| u1|beautiful_a|
| u5|beautiful_b|
| u4|beautiful_b|
| u6|beautiful_c|
| u1|beautiful_b|
| u2|beautiful_a|
| u5|beautiful_a|
+--------+-----------+实现：
（1）
SELECT shop_name,
count(*) uv
FROM
(SELECT user_id,
shop_name
FROM test_db.user_visit
GROUP BY user_id, shop_name) t
GROUP BY shop_name as t;
（2）
SELECT t2.shop_name,
t2.user_id,
t2.cnt
FROM
(SELECT t1.*,
row_number() over(partition BY t1.shop_name ORDER BY t1.cnt DESC) rank
FROM
(SELECT user_id,
shop_name,
count(*) AS cnt
FROM test_db.user_visit
GROUP BY user_id, shop_name) t1
) t2
WHERE rank < 4; 第三题
需求：
有如下的用户访问数据
+-------+----------+-----------+
|user_id|visit_date|visit_count|
+-------+----------+-----------+
| u01| 2017/1/21| 5|
| u02| 2017/1/23| 6|
| u03| 2017/1/22| 8|
| u04| 2017/1/20| 3|
| u01| 2017/1/23| 6|
| u01| 2017/2/21| 8|
| u02| 2017/1/23| 6|
| u01| 2017/2/22| 4|
+-------+----------+-----------+
要求使用SQL统计出每个用户的累积访问次数，如下表所示：
+-------+-----------+------------------+---------------+
|user_id|visit_month|month_total_visit_cnt|total_visit_cnt|
+-------+-----------+------------------+---------------+
| u01| 2017-01| 11| 11|
| u01| 2017-02| 12| 23|
| u02| 2017-01| 12| 12|
| u03| 2017-01| 8| 8|
| u04| 2017-01| 3| 3|
+-------+-----------+------------------+---------------+实现：
SELECT t2.user_id,
t2.visit_month,
month_total_visit_cnt,
sum(month_total_visit_cnt) over (partition BY user_id ORDER BY visit_month) AS total_visit_cnt
FROM
(SELECT user_id,
visit_month,
sum(visit_count) AS month_total_visit_cnt
FROM
(SELECT user_id,
date_format(regexp_replace(visit_date,'/','-'),'yyyy-MM') AS visit_month,
visit_count
FROM test_db.test1) t1
GROUP BY user_id, visit_month) t2
ORDER BY t2.user_id, t2.visit_month;第四题
需求：
表user(user_id，name，age)记录用户信息，表view_record(user_id，movie_name)记录用户观影信息，请根据年龄段（每10岁为一个年龄段，70以上的单独作为一个年龄段）观看电影的次数进行排序？实现：
SELECT
t2.age_group,
sum(t1.cnt) as view_cnt
FROM
(SELECT user_id,
count(*) cnt
FROM test_db.view_record
GROUP BY user_id) t1
JOIN
(SELECT user_id,
CASE WHEN age <= 10 AND age > 0 THEN '0-10'
WHEN age <= 20 AND age > 10 THEN '10-20'
WHEN age >20 AND age <=30 THEN '20-30'
WHEN age >30 AND age <=40 THEN '30-40'
WHEN age >40 AND age <=50 THEN '40-50'
WHEN age >50 AND age <=60 THEN '50-60'
WHEN age >60 AND age <=70 THEN '60-70'
ELSE '70以上' END as age_group
FROM test_db.user) t2 ON t1.user_id = t2.user_id
GROUP BY t2.age_group
ORDER BY t2.age_group;第五题
需求：
有日志如下，请用SQL求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户）
日期 用户 年龄
+----------+-------+---+
| date_time|user_id|age|
+----------+-------+---+
|2019-02-12| 2| 19|
|2019-02-11| 1| 23|
|2019-02-11| 3| 39|
|2019-02-11| 1| 23|
|2019-02-11| 3| 39|
|2019-02-13| 1| 23|
|2019-02-15| 2| 19|
|2019-02-11| 2| 19|
|2019-02-11| 1| 23|
|2019-02-16| 2| 19|
+----------+-------+---+实现：
SELECT sum(total_user_cnt) total_user_cnt,
sum(total_user_avg_age) total_user_avg_age,
sum(two_days_cnt) two_days_cnt,
sum(avg_age) avg_age
FROM
(SELECT 0 total_user_cnt,
0 total_user_avg_age,
count(*) AS two_days_cnt,
cast(sum(age) / count(*) AS decimal(5,2)) AS avg_age
FROM
(SELECT user_id,
max(age) age
FROM
(SELECT user_id,
max(age) age
FROM
(SELECT user_id,
age,
date_sub(date_time,rank) flag
FROM
(SELECT date_time,
user_id,
max(age) age,
row_number() over(PARTITION BY user_id ORDER BY date_time) rank
FROM test_db.test5
GROUP BY date_time,user_id) t1 ) t2
GROUP BY user_id, flag
HAVING count(*) >=2) t3
GROUP BY user_id) t4
UNION ALL
SELECT count(*) total_user_cnt,
cast(sum(age) /count(*) AS decimal(5,2)) total_user_avg_age,
0 two_days_cnt,
0 avg_age
FROM
(SELECT user_id,
max(age) age
FROM test_db.test5
GROUP BY user_id) t5) t6;第六题
需求：
请用sql写出所有用户中在2020年10月份第一次购买商品的金额，表order字段:
购买用户：user_id，金额：money，购买时间：pay_time(格式：2017-10-01)，订单id：order_id实现：
SELECT user_id, pay_time, money, order_id
FROM (SELECT user_id, money, pay_time, order_id,
row_number() over (PARTITION BY user_id ORDER BY pay_time) rank
FROM test_db.order
WHERE date_format(pay_time,'yyyy-MM') = '2020-10') t
WHERE rank = 1;第七题
需求：
有一个账号表如下，请写出SQL语句，查询各自区组的money排名前3的账号
dist_id string '区组id',
account string '账号',
gold_coin int '金币'实现：
SELECT dist_id,
account,
gold_coin
FROM
(SELECT dist_id,
account,
gold_coin,
row_number () over (PARTITION BY dist_id ORDER BY gold_coin DESC) rank
FROM test_db.test9) t
WHERE rank <= 3;第八题
需求：
充值日志表credit_log，字段如下：
`dist_id` int '区组id',
`account` string '账号',
`money` int '充值金额',
`create_time` string '订单时间'
请写出SQL语句，查询充值日志表2020年08月08号每个区组下充值额最大的账号，要求结果：
区组id，账号，金额，充值时间 实现：
WITH temp AS
(SELECT dist_id,
account,
sum(`money`) sum_money
FROM test_db.test8
WHERE date_format(create_time,'yyyy-MM-dd') = '2020-08-08'
GROUP BY dist_id,
account)
SELECT t1.dist_id,
t1.account,
t1.sum_money
FROM
(SELECT temp.dist_id,
temp.account,
temp.sum_money,
rank() over(partition BY temp.dist_id
ORDER BY temp.sum_money DESC) ranks
FROM TEMP) t1
WHERE ranks = 1;第九题
需求：
有一个线上服务器访问日志格式如下（用sql答题）
时间 接口 IP
+----------------------------------------+------------+
| date_time |interface |ip |
+-------------------+--------------------+------------+
|2016-11-09 15:22:05|/request/user/logout| 110.32.5.23|
|2020-09-28 14:23:1 |/api_v1/user/detail | 57.2.1.16 |
|2020-09-28 14:59:40|/api_v2/read/buy | 172.6.5.166|
+-------------------+--------------------+------------+
求2020年9月28号下午14点（14-15点），访问/api_v1/user/detail接口的top10的ip地址实现：
SELECT ip,
count(*) AS count
FROM test_db.test7
WHERE date_format(date_time,'yyyy-MM-dd HH') >= '2020-09-28 14'
AND date_format(date_time,'yyyy-MM-dd HH') < '2020-09-28 15'
AND interface='/api_v1/user/detail'
GROUP BY ip
ORDER BY count desc
LIMIT 10;第十题
存在如下表：
table student(s_id string, s_name string, s_birth string, s_sex string)
table course(c_id string, c_name string, t_id string)
table teacher(t_id string, t_name string)
table score(s_id string, c_id string, s_score int)示例数据：
student：
01 赵雷 1993-01-01 男
02 钱电 1989-12-21 男
03 孙雷 2000-05-20 男
04 李云 1990-08-06 男
05 周天 1978-12-01 女
06 吴兰 1992-03-01 女
07 郑竹 1989-07-01 男
08 王霞 1993-01-20 女
course：
01 语文 02
02 数学 01
03 英语 03
teacher：
01 张三
02 李四
03 王五
score：
01 01 80
01 02 90
01 03 99
02 01 70
02 02 60
02 03 80
03 01 80
03 02 80
03 03 80分别实现以下需求：
1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT student.*,
a.s_score as s1_score,
b.s_score as s2_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"课程成绩低的学生的信息及课程分数
SELECT student.*,
a.s_score as s1_score,
b.s_score as s2_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分的同学的学生编号和学生姓名和平均成绩
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分的同学的学生编号和学生姓名和平均成绩
-- 包括有成绩的和无成绩的
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
) 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);5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
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 student
JOIN score ON student.s_id = score.s_id
JOIN course ON course.c_id = score.c_id
JOIN teacher ON course.t_id = teacher.t_id AND t_name = '张三';8.查询没学过"张三"老师授课的同学的信息
SELECT student.*
FROM student
LEFT 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 = '张三') tmp
ON student.s_id = tmp.s_id
WHERE tmp.s_id is null;9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
SELECT * from student
JOIN (select s_id FROM score WHERE c_id =1 ) t1
ON student.s_id=t1.s_id
JOIN (select s_id FROM score WHERE c_id =2 ) t2
ON student.s_id=t2.s_id;10.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT student.* FROM studentJOIN (select s_id FROM score WHERE c_id =1 ) tmp1on student.s_id=tmp1.s_idLEFT JOIN (select s_id FROM score WHERE c_id =2 ) tmp2on student.s_id =tmp2.s_idWHERE tmp2.s_id is null;11.查询没有学全所有课程的同学的信息
select student.* from studentjoin (select count(c_id)num1 from course) tmp1left join(select s_id,count(c_id) num2from 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) tmp2on 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"号的同学学习的课程完全相同的其他同学的信息
select student.*,tmp1.course_id from student join (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 ) tmp2on 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='张三')tmp2on score.c_id=tmp2.c_id )tmpon student.s_id = tmp.s_idwhere tmp.s_id is null;15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.s_id,student.s_name,tmp.avg_score from student inner join (select s_id from score where s_score<60 group by score.s_id having count(s_id)>1 ) tmp2 on student.s_id = tmp2.s_idleft join ( select s_id,round(AVG (score.s_score)) avg_score from score group by s_id ) tmpon tmp.s_id=student.s_id;16.检索"01"课程分数小于60，按分数降序排列的学生信息
select student.*,s_score from student,score
where 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 course
join(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) excellentRates
from score group by c_id)tmp on tmp.c_id=course.c_id;19. 按各科成绩进行排序，并显示排名
select s1.*,row_number()over(order by s1.s_score desc) Ranking
from score s1 where s1.c_id='01'order by noRanking asc
union all select s2.*,row_number()over(order by s2.s_score desc) Ranking
from score s2 where s2.c_id='02'order by noRanking asc
union 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) Rankingfrom score , studentwhere score.s_id=student.s_idgroup by score.s_id,s_name order by sumscore desc;21.查询不同老师所教不同课程平均分从高到低显示
select course.c_id,course.t_id,t_name,round(avg(s_score),2)as avgscore from course
join teacher on teacher.t_id=course.t_id
join score on course.c_id=score.c_id
group by course.c_id,course.t_id,t_name order by avgscore desc;22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
select tmp1.* from
(select * from score where c_id='01' order by s_score desc limit 3)tmp1
order by s_score asc limit 2
union all select tmp2.* from
(select * from score where c_id='02' order by s_score desc limit 3)tmp2
order by s_score asc limit 2
union all select tmp3.* from
(select * from score where c_id='03' order by s_score desc limit 3)tmp3
order by s_score asc limit 2;23.统计各科成绩各分数段人数
-- 课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select c.c_id,c.c_name,tmp1.s0_60, tmp1.percentum,tmp2.s60_70, tmp2.percentum,tmp3.s70_85, tmp3.percentum,tmp4.s85_100, tmp4.percentum
from course c
join(select c_id,sum(case when s_score<60 then 1 else 0 end )as s0_60,
round(100*sum(case when s_score<60 then 1 else 0 end )/count(c_id),2)as percentum
from score group by c_id)tmp1 on tmp1.c_id =c.c_id
left join(select c_id,sum(case when s_score<70 and s_score>=60 then 1 else 0 end )as s60_70,
round(100*sum(case when s_score<70 and s_score>=60 then 1 else 0 end )/count(c_id),2)as percentum
from score group by c_id)tmp2 on tmp2.c_id =c.c_id
left join(select c_id,sum(case when s_score<85 and s_score>=70 then 1 else 0 end )as s70_85,
round(100*sum(case when s_score<85 and s_score>=70 then 1 else 0 end )/count(c_id),2)as percentum
from score group by c_id)tmp3 on tmp3.c_id =c.c_id
left join(select c_id,sum(case when s_score>=85 then 1 else 0 end )as s85_100,
round(100*sum(case when s_score>=85 then 1 else 0 end )/count(c_id),2)as percentum
from score group by c_id)tmp4 on tmp4.c_id =c.c_id;24.查询学生平均成绩及其名次
select tmp.*,row_number()over(order by tmp.avgScore desc) Ranking from
(select student.s_id,
student.s_name,
round(avg(score.s_score),2) as avgScore
from student join score
on student.s_id=score.s_id
group by student.s_id,student.s_name)tmp
order by avgScore desc;25.查询各科成绩前三名的记录
1.课程id为01的前三名
select score.c_id,course.c_name,student.s_name,s_score from score
join student on student.s_id=score.s_id
join course on score.c_id='01' and course.c_id=score.c_id
order by s_score desc limit 3;
2.课程id为02的前三名
select score.c_id,course.c_name,student.s_name,s_score
from score
join student on student.s_id=score.s_id
join course on score.c_id='02' and course.c_id=score.c_id
order by s_score desc limit 3;
3.课程id为03的前三名
select score.c_id,course.c_name,student.s_name,s_score
from score
join student on student.s_id=score.s_id
join course on score.c_id='03' and course.c_id=score.c_id
order by s_score desc limit 3;26.查询每门课程被选修的学生数
select c.c_id,c.c_name,tmp.number from course cjoin (select c_id,count(1) as number from score where score.s_score<60 group by score.c_id ) tmpon tmp.c_id=c.c_id;27.查询出只有两门课程的全部学生的学号和姓名
select st.s_id,st.s_name from student stjoin (select s_id from score group by s_id having count(c_id) =2) tmpon st.s_id=tmp.s_id;28.查询男生、女生人数
select tmp1.man,tmp2.women from
(select count(1) as man from student where s_sex='男')tmp1,
(select count(1) as women from student where s_sex='女')tmp2;29.查询名字中含有"风"字的学生信息
select * from student where s_name like '%风%';30.查询同名同性学生名单，并统计同名人数
select s1.s_id,s1.s_name,s1.s_sex,count(*) as sameName
from student s1,student s2
where s1.s_name=s2.s_name and s1.s_id<>s2.s_id and s1.s_sex=s2.s_sex
group by s1.s_id,s1.s_name,s1.s_sex;31.查询1990年出生的学生名单
select * from student where s_birth like '1990%';32.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select score.c_id,c_name,round(avg(s_score),2) as avgScore from score
join course on score.c_id=course.c_id
group by score.c_id,c_name order by avgScore desc,score.c_id asc;33.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select score.s_id,s_name,round(avg(s_score),2)as avgScore from score
join student on student.s_id=score.s_id
group by score.s_id,s_name having avg(s_score) >= 85;34.查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s_name,s_score as mathScore from studentjoin (select s_id,s_score from score,course where score.c_id=course.c_id and c_name='数学' ) tmpon tmp.s_score < 60 and student.s_id=tmp.s_id;35.查询所有学生的课程及分数情况
select a.s_name,
SUM(case c.c_name when '语文' then b.s_score else 0 end ) as chainese,
SUM(case c.c_name when '数学' then b.s_score else 0 end ) as math,
SUM(case c.c_name when '英语' then b.s_score else 0 end ) as english,
SUM(b.s_score) as sumScore
from student a
join score b on a.s_id=b.s_id
join course c on b.c_id=c.c_id
group by s_name,a.s_id;36.查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数
select student.s_id,s_name,c_name,s_score from student
join (select sc.* from score sc
left join(select s_id from score where s_score < 70 group by s_id)tmp
on sc.s_id=tmp.s_id where tmp.s_id is null)tmp2
on student.s_id=tmp2.s_id
join course on tmp2.c_id=course.c_id
order by s_id;
-- 查询全部及格的信息
select sc.* from score sc
left join(select s_id from score where s_score < 60 group by s_id)tmp
on sc.s_id=tmp.s_id
where tmp.s_id is null;
-- 或(效率低)
select sc.* from score sc
where sc.s_id not in (select s_id from score where s_score < 60 group by s_id);37.查询课程不及格的学生
select s_name,c_name as courseName,tmp.s_score
from student
join (select s_id,s_score,c_name
from score,course
where score.c_id=course.c_id and s_score < 60)tmp
on student.s_id=tmp.s_id;38.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select student.s_id,s_name,s_score as score_01
from student
join score on student.s_id=score.s_id
where c_id='01' and s_score >= 80;39.求每门课程的学生人数
select course.c_id,course.c_name,count(1)as selectNum
from course
join score on course.c_id=score.c_id
group by course.c_id,course.c_name;40.查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select student.*,tmp3.c_name,tmp3.maxScore
from (select s_id,c_name,max(s_score)as maxScore from score
join (select course.c_id,c_name from course join
(select t_id,t_name from teacher where t_name='张三')tmp
on course.t_id=tmp.t_id)tmp2
on score.c_id=tmp2.c_id group by score.s_id,c_name
order by maxScore desc limit 1)tmp3
join student
on student.s_id=tmp3.s_id;41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select distinct a.s_id,a.c_id,a.s_score from score a,score b
where a.c_id <> b.c_id and a.s_score=b.s_score;42.查询每门课程成绩最好的前三名
select tmp1.* from
(select *,row_number()over(order by s_score desc) ranking
from score where c_id ='01')tmp1
where tmp1.ranking <= 3
union all
select tmp2.* from
(select *,row_number()over(order by s_score desc) ranking
from score where c_id ='02')tmp2
where tmp2.ranking <= 3
union all
select tmp3.* from
(select *,row_number()over(order by s_score desc) ranking
from score where c_id ='03')tmp3
where tmp3.ranking <= 3;43.统计每门课程的学生选修人数（超过5人的课程才统计）
– 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select distinct course.c_id,tmp.num from course
join (select c_id,count(1) as num from score group by c_id)tmp
where tmp.num>=5 order by tmp.num desc ,course.c_id asc;44.检索至少选修两门课程的学生学号
select s_id,count(c_id) as totalCourse
from score
group by s_id
having count(c_id) >= 2;45.查询选修了全部课程的学生信息
select student.*
from student,
(select s_id,count(c_id) as totalCourse
from score group by s_id)tmp
where student.s_id=tmp.s_id and totalCourse=3;46.查询下周过生日的学生
select s_name,s_sex,s_birth from student
where substring(s_birth,6,2)='10'
and substring(s_birth,9,2)>=15
and substring(s_birth,9,2)<=21;47.查询本月过生日的学生
select s_name,s_sex,s_birth from student where substring(s_birth,6,2)='10';48.查询12月份过生日的学生
select s_name,s_sex,s_birth from student where substring(s_birth,6,2)='12';49.查询各学生的年龄（周岁）
–- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
方法一
select s_name,s_birth,
(year(CURRENT_DATE)-year(s_birth)-
(case when month(CURRENT_DATE) < month(s_birth) then 1
when month(CURRENT_DATE) = month(s_birth) and day(CURRENT_DATE) < day(s_birth) then 1
else 0 end)
) as age
from student;50.查询本周过生日的学生
select s_name,s_sex,s_birth from student
where substring(s_birth,6,2)='10'
and substring(s_birth,9,2)=14;
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2020-09-29，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
暂无评论
推荐阅读
推荐阅读
相关推荐
Linux内核编程--进程通信信号
更多 >
腾讯云开发者
