Hive_Sql经典50题

建表

create database if not exists home_work;
use home_work;

-- 建4张表
create external table home_work.course
(
    c_id   int,
    c_name string,
    t_id   int
)
    row format delimited fields terminated by '\t';

create external table home_work.score
(
    s_id    int,
    c_id    int,
    s_score int
)
    row format delimited fields terminated by '\t';

create external table home_work.student
(
    s_id    int,
    s_name  string,
    s_birth string,
    s_sex   string
)
    row format delimited fields terminated by ',';


create external table home_work.teacher
(
    t_id   int,
    t_name string
)
    row format delimited fields terminated by ',';

-- 导入数据
load data local inpath '/root/hive_data/course1.txt' into table home_work.course;
load data local inpath '/root/hive_data/score1.txt' into table home_work.score;
load data local inpath '/root/hive_data/student1.txt' into table home_work.student;
load data local inpath '/root/hive_data/teacher1.txt' into table home_work.teacher;

-- 查看是否导入成功
select *
from home_work.course;
select *
from home_work.score;
select *
from home_work.student;
select *
from home_work.teacher;

hive_sql50题

------------------------------1~10 -------------------------------------

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

with sc1 as (select * from score where c_id = 1),
     sc2 as (select * from score where c_id = 2)
select s.*
     , sc1.s_score as score_01
     , sc2.s_score as score_02
from student s
         join sc1 on s.s_id = sc1.s_id
         join sc2 on s.s_id = sc2.s_id
where sc1.s_score > sc2.s_score;

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

with sc1 as (select * from score where c_id = 1),
     sc2 as (select * from score where c_id = 2)
select s.*
     , sc1.s_score as score_01
     , sc2.s_score as score_02
from student s
         join sc1 on s.s_id = sc1.s_id
         join sc2 on s.s_id = sc2.s_id
where sc1.s_score < sc2.s_score;

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

with t1 as (
    select s_id
         , avg(s_score) as avg_score
    from score
    group by s_id
    having avg_score >= 60)
select s.s_id
     , s.s_name
     , round(avg_score, 2) as avg_score
from student s
         join t1 on s.s_id = t1.s_id;

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

with t1 as (
    select s_id
         , avg(s_score) as avg_score
    from score
    group by s_id)
select s.s_id
     , s.s_name
     , if(avg_score is null, 0, round(avg_score, 2)) as avg_score
from student s
         left join t1 on s.s_id = t1.s_id
where avg_score is null
   or avg_score < 60;

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

with t1 as (
    select s_id
         , count(*)     as cnt_course
         , sum(s_score) as sum_score
    from score
    group by s_id)
select s.s_id
     , s.s_name
     , if(cnt_course is null, 0, cnt_course) as cnt_course
     , if(sum_score is null, 0, sum_score)   as sum_score
from student s
         left join t1 on s.s_id = t1.s_id;

6、查询"李"姓老师的数量

select count(*) as cnt_name_li
from teacher
where t_name like '李%';

7、查询学过"张三"老师授课的同学的信息

with t1 as (select c_id
            from course c
                     join teacher t on c.t_id = t.t_id
            where t_name = '张三'),
     t2 as (select distinct s_id
            from score sc
                     join t1 on sc.c_id = t1.c_id)
select *
from student s
where s_id in (
    select t2.s_id
    from t2);

--     exists (
--     select *
--     from t2
--     where s.s_id = t2.s_id);

8、查询没学过"张三"老师授课的同学的信息

with t1 as (select c_id
            from course c
                     join teacher t on c.t_id = t.t_id
            where t_name = '张三'),
     t2 as (select distinct s_id
            from score sc
                     join t1 on sc.c_id = t1.c_id)
select *
from student s
where s.s_id not in (
    select t2.s_id
    from t2);
--    not exists (
--     select *
--     from t2
--     where s.s_id = t2.s_id);

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

-- 解法一
with t1 as (
    select s_id
    from score
    where c_id in ('01', '02')
    group by s_id
    having count(*) = 2)
select *
from student s
where exists (
    select *
    from t1
    where s.s_id = t1.s_id);


-- 解法二
with sc1 as (select s_id from score where c_id = 1),
     sc2 as (select s_id from score where c_id = 2)
select *
from student s
where s.s_id in (select sc2.s_id from sc2)
  and s.s_id in (select sc1.s_id from sc1);


--解法三
with sc1 as (select s_id from score where c_id = 1),
     sc2 as (select s_id from score where c_id = 2),
     sc3 as (select sc1.s_id sid
             from sc1
                      left join sc2 on sc1.s_id = sc2.s_id
             where sc2.s_id is not null)
select *
from student s
where s.s_id in (select sc3.sid from sc3);

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

-- 解法一
with t1 as (select s_id from score where c_id = 1),
     t2 as (select s_id from score where c_id = 2),
     t3 as (select t1.s_id as s_id
            from t1
                     left join t2 on t1.s_id = t2.s_id
            where t2.s_id is null)
select *
from student s
where exists (select * from t3 where s.s_id = t3.s_id);

-- 解法二
with sc1 as (select s_id from score where c_id = 1),
     sc2 as (select s_id from score where c_id = 2)
select *
from student s
where s.s_id in (select sc1.s_id from sc1)
  and s.s_id not in (select sc2.s_id from sc2);

------------------------------11~20 -------------------------------------

11、查询没有学全所有课程的同学的信息

-- 解法一
with t1 as (
    select s_id
    from score
    where c_id in ('01', '02', '03')
    group by s_id
    having count(*) = (select count(*) as cnt_course from course))
select *
from student s
where not exists (
    select *
    from t1
    where s.s_id = t1.s_id);


-- 解法二
with t1 as (select count(*) as cnt_course
            from course),
     t2 as (select s_id
                 , count(*) as cnt_course
            from score
            group by s_id),
     t3 as (select s_id
            from t1
                     cross join t2
            where t1.cnt_course = t2.cnt_course)
select *
from student s
where not exists (select *
                  from t3
                  where s.s_id = t3.s_id);

-- 解法三
with t1 as (select count(*) as cnt_course
            from course),
     t2 as (select s_id
                 , count(*) as cnt_course
            from score
            group by s_id),
     t3 as (select s_id
            from t1
                     left join t2 on t1.cnt_course = t2.cnt_course
            where t2.cnt_course is not null)
select *
from student s
where not exists (select *
                  from t3
                  where s.s_id = t3.s_id);

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

with t1 as (select sc1.c_id cid from score sc1 where sc1.s_id = '01'),
     t2 as (select distinct s_id
            from score sc
                     join t1 on sc.c_id = t1.cid)
select *
from student s
where exists (select * from t2 where s.s_id = t2.s_id);

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

with t1 as (select c_id from score where s_id = 1),
     t2 as (select count(*) as cnt_course from t1),
     t3 as (select s_id
                 , count(*) as cnt_course
            from score sc
                     join t1 on sc.c_id = t1.c_id
            where s_id != 1
            group by s_id),
     t4 as (select s_id
            from t2
                     cross join t3
            where t2.cnt_course = t3.cnt_course)
select *
from student s
where exists (select * from t4 where s.s_id = t4.s_id);

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

-- 解法一
with t1 as (select t_id from teacher where t_name = '张三')
   , t2 as (select c_id
            from course c
                     join t1 on c.t_id = t1.t_id)
   , t3 as (select s_id
            from score sc
                     join t2 on sc.c_id = t2.c_id)
select s_name
from student s
where not exists (
    select s_id
    from t3
    where s.s_id = t3.s_id);


-- 解法二
with t1 as (select c_id
            from course c
                     inner join teacher t on c.t_id = t.t_id
            where t_name = '张三'),
     t2 as (select s_id
            from score sc
                     inner join t1 on sc.c_id = t1.c_id)
select s_name
from student s
where not exists (select * from t2 where s.s_id = t2.s_id);

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

with t1 as (select s_id
            from score
            where s_score < 60
            group by s_id
            having count(*) >= 2)
select s.s_id
     , s.s_name
     , round(avg(s_score), 2) as avg_score
from student s
         inner join t1 on s.s_id = t1.s_id
         inner join score sc on s.s_id = sc.s_id
group by s.s_id, s.s_name;

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

with t1 as (select s_id
                 , s_score
            from score
            where c_id = 1
              and s_score < 60)
select s.*
     , s_score as score_01
from student s
         inner join t1 on s.s_id = t1.s_id
order by score_01 desc;

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

-- 解法一
with t1 as (
    select s.s_id,
           s.s_name,
           sum(case c_id when 1 then s_score else 0 end) as chinese,
           sum(case c_id when 2 then s_score else 0 end) as math,
           sum(case c_id when 3 then s_score else 0 end) as english,
           round(avg(s_score), 2)                        as avg_score
    from student s
             left join score sc
                       on s.s_id = sc.s_id
    group by s.s_id, s.s_name
    order by avg_score desc
)
select s_id
     , s_name
     , chinese
     , math
     , english
     , if(avg_score is null, 0, avg_score) as avg_score
from t1;

-- 解法二
select s.s_id,
       s.s_name,
       sum(case c_id when 1 then s_score else 0 end)       as chinese,
       sum(case c_id when 2 then s_score else 0 end)       as math,
       sum(case c_id when 3 then s_score else 0 end)       as english,
       round(if(avg(s_score) is null, 0, avg(s_score)), 2) as avg_score
from student s
         left join score sc
                   on s.s_id = sc.s_id
group by s.s_id, s.s_name
order by avg_score desc;

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

-- 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--  及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select c.c_id                                                                                             as course_id,
       c.c_name                                                                                           as course_name,
       max(s_score)                                                                                       as max_score,
       min(s_score)                                                                                       as ming_score,
       round(avg(s_score), 2)                                                                             as avg_score,
       concat(round(sum(case when s_score >= 60 then 1 else 0 end) / count(*) * 100, 2), '%')             as pass_rate,
       concat(round(sum(case when s_score between 70 and 80 then 1 else 0 end) / count(*) * 100, 2),
              '%')                                                                                        as medium_rate,
       concat(round(sum(case when s_score between 80 and 90 then 1 else 0 end) / count(*) * 100, 2), '%') as good_rate,
       concat(round(sum(case when s_score >= 90 then 1 else 0 end) / count(*) * 100, 2),
              '%')                                                                                        as excellent_rate
from course c
         inner join score s on c.c_id = s.c_id
group by c.c_id, c.c_name;

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

select c.c_id,
       c.c_name,
       s.s_id,
       s.s_name,
       s_score,
       row_number() over (partition by c.c_id order by s_score desc) as rank
from score sc
         inner join student s on sc.s_id = s.s_id
         inner join course c on sc.c_id = c.c_id;

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

with t1 as (select s_id
                 , sum(s_score) as sum_score
            from score
            group by s_id)
select s.s_id
     , s.s_name
     , sum_score
     , row_number() over (order by sum_score desc) as rank
from student s
         inner join t1 on s.s_id = t1.s_id;

------------------------------21~30 -------------------------------------

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

-- 解法一
select t.t_id
     , t.t_name
     , c.c_id
     , c.c_name
     , round(avg(s_score), 2) as avg_score
from score sc
         inner join course c on sc.c_id = c.c_id
         inner join teacher t on t.t_id = c.t_id
group by t.t_id, t.t_name, c.c_id, c.c_name
order by t.t_id, avg_score desc;

-- 解法二
select cs.t_id
     , avg(s_score) avg_score
from score sc
         join course cs on sc.c_id = cs.c_id
         join teacher t on t.t_id = cs.t_id
group by cs.t_id, cs.c_id
order by avg_score desc;

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

with t1 as (
    select c.c_id
         , c.c_name
         , s.*
         , sc.s_score
         , row_number() over (partition by c.c_id order by s_score desc) as rank
    from score sc
             inner join student s on sc.s_id = s.s_id
             inner join course c on sc.c_id = c.c_id
)
select *
from t1
where rank in (2, 3);

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select c.c_id                                                                                            as course_id,
       c.c_name                                                                                          as course_name,
       sum(case when s_score between 85 and 100 then 1 else 0 end)                                       as number_100_85,
       concat(round(sum(case when s_score between 85 and 100 then 1 else 0 end) / count(*) * 100, 2),
              '%')                                                                                       as percentage,
       sum(case when s_score between 70 and 85 then 1 else 0 end)                                        as number_85_70,
       concat(round(sum(case when s_score between 70 and 85 then 1 else 0 end) / count(*) * 100, 2),
              '%')                                                                                       as percentage,
       sum(case when s_score between 60 and 70 then 1 else 0 end)                                        as number_70_60,
       concat(round(sum(case when s_score between 60 and 70 then 1 else 0 end) / count(*) * 100, 2),
              '%')                                                                                       as percentage,
       sum(case when s_score between 0 and 60 then 1 else 0 end)                                         as number_0_60,
       concat(round(sum(case when s_score between 0 and 60 then 1 else 0 end) / count(*) * 100, 2), '%') as percentage
from course c
         inner join score sc
                    on c.c_id = sc.c_id
group by c.c_id, c.c_name;

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

with t1 as (select s_id
                 , round(avg(s_score), 2) as avg_score
            from score
            group by s_id)
select s.s_id
     , s.s_name
     , avg_score
     , row_number() over (order by avg_score desc) as rank
from student s
         inner join t1 on s.s_id = t1.s_id;

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

with t1 as (
    select c.c_id
         , c.c_name
         , s.s_id
         , s.s_name
         , s_score
         , row_number() over (partition by c.c_id order by s_score desc) as rank
    from score sc
             inner join student s on sc.s_id = s.s_id
             inner join course c on sc.c_id = c.c_id)
select *
from t1
where rank <= 3;


-- select c.c_id
--      , c.c_name
--      , s.s_id
--      , s.s_name
--      , s2.s_score
--      ,row_number() over (partition by c.c_id order by s2.s_score desc) as rank
-- from student s
--          inner join score s2 on s.s_id = s2.s_id
--          inner join course c on s2.c_id = c.c_id
-- where rank < 4;

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

with t1 as (select c_id
                 , count(*) as cnt_student
            from score
            group by c_id)
select c.c_id
     , c.c_name
     , cnt_student
from course c
         inner join t1 on c.c_id = t1.c_id;

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

with t1 as (select s_id
            from score
            group by s_id
            having count(1) = 2)
select s.s_id, s.s_name
from student s
where exists (select * from t1 where s.s_id = t1.s_id);

28、查询男生、女生人数

select s_sex
     , count(*) as cnt_sex
from student
group by s_sex;

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

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

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

with t1 as (
    select s_name
         , s_sex
         , count(*) as cnt_student
    from student
    group by s_name, s_sex
    having cnt_student > 1)
select *
from student s
         inner join t1 on s.s_name = t1.s_name and s.s_sex = t1.s_sex;

------------------------------31~40 -------------------------------------

31、查询1990年出生的学生名单

select *
from student
where year(s_birth) = '1990';

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

with t1 as (select c_id
                 , round(avg(s_score), 2) as avg_score
            from score
            group by c_id)
select c.c_id
     , c.c_name
     , avg_score
from course c
         inner join t1 on c.c_id = t1.c_id
order by avg_score desc, c.c_id;

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

with t1 as (select s_id
                 , avg(s_score) as avg_score
            from score
            group by s_id
            having avg_score >= 85)
select s.s_id
     , s.s_name
     , round(avg_score, 2)
from student s
         inner join t1 on s.s_id = t1.s_id;

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

-- 解法一
with t1 as (select c_id from course where c_name = '数学')
select s.s_name
     , s2.s_score
from student s
         inner join score s2 on s.s_id = s2.s_id
         inner join t1 on s2.c_id = t1.c_id and s2.s_score < 60;


-- 解法二
with t1 as (
    select s_id, s_score
    from score sc
             inner join course c on sc.c_id = c.c_id
    where c_name = '数学'
      and s_score < 60
)
select s_name, s_score
from student s
         inner join t1 on s.s_id = t1.s_id;

35、查询所有学生的课程及分数情况

select s.s_id
     , s.s_name
     , sum(case c_id when 1 then s_score else 0 end) as chinese
     , sum(case c_id when 2 then s_score else 0 end) as math
     , sum(case c_id when 3 then s_score else 0 end) as english
from student s
         left join score sc
                   on s.s_id = sc.s_id
group by s.s_id, s.s_name;

36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数

-- 解法一
select s.s_name
     , c.c_id
     , s2.s_score
from student s
         inner join score s2 on s.s_id = s2.s_id
         inner join course c on s2.c_id = c.c_id
where s2.s_score > 70;


-- 解法二
with t1 as (select s_id
                 , c_id
                 , s_score
            from score
            where s_score > 70
            group by s_id, c_id, s_score)
select s_name
     , c_name
     , s_score
from t1
         inner join student s on t1.s_id = s.s_id
         inner join course c on t1.c_id = c.c_id;

37、查询课程不及格的学生

-- 解法一
select s.s_name
     , c.c_id
     , s2.s_score
from student s
         inner join score s2 on s.s_id = s2.s_id
         inner join course c on s2.c_id = c.c_id
where s2.s_score < 60;

-- 解法二
select s.s_id
     , s.s_name
     , c.c_id
     , c.c_name
     , s_score
from score sc
         inner join student s on sc.s_id = s.s_id
         inner join course c on sc.c_id = c.c_id
where s_score < 60;

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

-- 解法一
with t1 as (select s_id
            from score
            where c_id = 1
              and s_score >= 80)
select s_id, s_name
from student s
where exists (select * from t1 where s.s_id = t1.s_id);


-- 解法二
with t1 as (select s_id
            from score
            where c_id = 1
              and s_score >= 80)
select s_id, s_name
from student s
where s.s_id in (select s_id from t1);

39、求每门课程的学生人数

with t1 as (select c_id
                 , count(*) as cnt_student
            from score
            group by c_id)
select c.c_id
     , c.c_name
     , cnt_student
from course c
         inner join t1 on c.c_id = t1.c_id;

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

-- 解法一
with t1 as (select t_id from teacher where t_name = '张三')
   , t2 as (select c_id
            from course c
                     join t1 on c.t_id = t1.t_id)
select s.*
     , s2.s_score as max_score
from student s
         inner join score s2 on s.s_id = s2.s_id
         inner join t2 on s2.c_id = t2.c_id
order by s2.s_score desc
limit 1;

-- 解法二
with t1 as (select c_id
            from course c
                     inner join teacher t on c.t_id = t.t_id
            where t_name = '张三')
select s.*
     , s2.s_score as max_score
from student s
         inner join score s2 on s.s_id = s2.s_id
         inner join t1 on s2.c_id = t1.c_id
order by s2.s_score desc
limit 1;

-- 解法三
with t1 as (select c_id
            from course c
                     inner join teacher t on c.t_id = t.t_id
            where t_name = '张三'),
     t2 as (select s_id, s_score
            from score sc
                     inner join t1 on sc.c_id = t1.c_id
            order by s_score desc
            limit 1)
select s.*, s_score as max_score
from student s
         inner join t2 on s.s_id = t2.s_id;

------------------------------41~50 -------------------------------------

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

with t1 as (select *
                 , count(1) over (partition by s_score) as cnt_student
            from score)
select s_id, c_id, s_score
from t1
where cnt_student > 1;

42、查询每门课程成绩最好的前三名

-- 解法一
with t1 as (select c_id
                 , s_id
                 , s_score
                 , row_number() over (partition by c_id order by s_score desc) as rank
            from score)
select t1.c_id
     , s.s_id
     , s.s_name
     , t1.s_score
     , t1.rank
from student s
         join t1 on s.s_id = t1.s_id
where rank < 4;

-- 解法二
with t1 as (
    select c.c_id
         , c.c_name
         , s.s_id
         , s.s_name
         , s_score
         , row_number() over (partition by c.c_id order by s_score desc) as rank
    from score sc
             inner join student s on sc.s_id = s.s_id
             inner join course c on sc.c_id = c.c_id
)
select *
from t1
where rank <= 3;

43、统计每门课程的学生选修人数（超过5人的课程才统计）

-- 解法一
with t1 as (select c_id
                 , count(1) as cnt_student
            from score
            group by c_id
            having cnt_student > 5)
select c.c_id, cnt_student
from course c
         inner join t1
                    on c.c_id = t1.c_id
order by cnt_student desc, c.c_id;

-- 解法二
with t1 as (select c_id
                 , count(1) as cnt_student
            from score
            group by c_id
            having cnt_student > 5)
select c_id, cnt_student
from t1
order by cnt_student desc, c_id;

44、检索至少选修两门课程的学生学号

select s_id
from score
group by s_id
having count(1) > 1;

45、查询选修了全部课程的学生信息

-- 解法一
with t1 as (select count(1) count from course)
   , t2 as (select s_id, count(1) c_count from score group by s_id)
   , t3 as (select s_id
            from t1
                     join t2 on t1.count = t2.c_count)
select s.*
from student s
where s.s_id in (select s_id from t3);


-- 解法二
with t1 as (select count(*) as cnt_course from course),
     t2 as (select s_id
                 , count(*) as cnt_course
            from score
            group by s_id),
     t3 as (select s_id
            from t1
                     cross join t2
            where t1.cnt_course = t2.cnt_course)
select *
from student s
where exists (select * from t3 where s.s_id = t3.s_id);

46、查询各学生的年龄(周岁)

-- 解法一
select s_id
     , s_name
     , s_birth
     , case
           when month(s_birth) > month(`current_date`())
               then year(current_date()) - year(s_birth) - 1
           when month(s_birth) = month(`current_date`())
               and day(s_birth) > day(`current_date`())
               then year(current_date()) - year(s_birth) - 1
           else year(current_date()) - year(s_birth)
    end age
from student;

-- 解法二
select s_id,
       s_name,
       s_birth,
       if(
                   month(current_date()) < month(s_birth) or
                   (month(current_date()) = month(s_birth) and
                    day(current_date()) < day(s_birth)),
                   year(current_date()) - year(s_birth) - 1,
                   year(current_date()) - year(s_birth)
           )
           as s_age
from student;

47、查询本周过生日的学生

-- 解法一
select *
from student
where datediff(concat(year(current_date())
                   , date_format(s_birth, '-MM-dd'))
    , to_date(current_date()))
    between 0 and 7
   or datediff(concat(year(current_date()) + 1
                   , date_format(s_birth, '-MM-dd'))
    , to_date(current_date()))
    between 0 and 7;

-- 解法二
SELECT s_id
     , s_name
     , s_birth
     , s_sex
FROM student
WHERE WEEKOFYEAR(CURRENT_DATE) = WEEKOFYEAR(s_birth);

48、查询下周过生日的学生

-- 解法一
select *
from student
where datediff(concat(year(current_date())
                   , date_format(s_birth, '-MM-dd'))
    , to_date(current_date()))
    between 7 and 14
   or datediff(concat(year(current_date()) + 1
                   , date_format(s_birth, '-MM-dd'))
    , to_date(current_date()))
    between 7 and 14;

-- 解法二
SELECT s_id
     , s_name
     , s_birth
     , s_sex
FROM student
WHERE WEEKOFYEAR(CURRENT_DATE) + 1 = WEEKOFYEAR(s_birth);

49、查询本月过生日的学生

select *
from student
where month(s_birth) = month(current_date());

50、查询12月份过生日的学生

select *
from student
where month(s_birth) = 12;