超经典SQL题 | 做完这4道面试题你就过关了

挖数网精选
挖数网精选
挖数网精选
355
文章
0
评论
2020-08-0413:08:00 评论 220 3428字
摘要

很多程序员视 SQL 为洪水猛兽。SQL 是一种为数不多的声明性语言,它的运行方式完全不同于我们所熟知的命令行语言、面向对象的程序语言、甚至是函数语言(尽管有些人认为 SQL 语言也是一种函数式语言)。

第一题

1.现有如下学生成绩表st_score:

超经典SQL题 | 做完这4道面试题你就过关了

将上表转换为如下所示表,需满足以下要求:

①每一个st_id仅存在一行数据;

②成绩出现重复时选最高成绩;

③与下表字段顺序统一,字段名可不同;

④按照st_id升序排列;

⑤一条语句(只有一个分号)查询且没有出现基本语法错误。

注:无法实现行转列则为0分,使用一条一条结果用union连接也是0分,以上要求每个点2分

超经典SQL题 | 做完这4道面试题你就过关了

参考代码

select st_id,

name,

gender,

max(case when lesson = "math" then score else 0 end) as mt_score,

max(case when lesson = "Chinese" then score else 0 end) as chi_score,

max(case when lesson = "English" then score else 0 end) as eng_score

from st_score

group by st_id,name,gender

order by st_id;

解析

case when语句是本题的核心,也是行转列这类题目的核心部分,题目要求①要求一个st_id仅存在一条数据,所以这里还需要配合聚合函数使用,用case when语句作为子查询,主查询再进行合并也是可以的,只是会多一个步骤。

要求②中已经明确要求"成绩出现重复时选"最高成绩",所以表中是有可能出现重复值的,这里只能使用max()函数,如果这里已经明确说明不会出现重复值,就可以用sum()、max()、min()这样的聚合函数。

第二题

2.现已将上表查询的结果保存为新表new_score,根据new_score表查询出满足以下条件的结果:

①语数英三门课程的成绩都排在前五(包含第五);

②条件①中为单科成绩排名,从高到低排列,排名连续,成绩相同时采用并列排名;

③字段顺序同下表;

④结果按照总成绩从高到低排列;

⑤一条语句(只有一个分号)查询且没有出现基本语法错误。

注:使用一条一条结果用union连接则为0分,以上要求每个点2分

最终显示结果:

超经典SQL题 | 做完这4道面试题你就过关了

参考代码

select st_id,

name,

mt_score,

chi_score,

eng_score,

total_score

from (

select st_id,

name,

mt_score,

dense_rank() over(order by mt_score DESC) as rn_math,

chi_score,

dense_rank() over(order by chi_score DESC) as rn_chi,

eng_score,

dense_rank() over(order by eng_score DESC) as rn_eng,

mt_score + chi_score + eng_score as total_score

from new_score

) t

where rn_math <= 5 and rn_chi <= 5 and rn_eng <=5

order by total_score desc;

解析

本题考察的是窗口函数中row_number、rank、dense_rank的分辨和使用,根据本题要求②,这里需要使用dense_rank函数,并且顺序要从高到低排列,还需要用到order by desc;

有的可能会使用order by配合limit来解决排名问题,然后再进行表关联选择同时满足条件的数据,都没有问题,但是逻辑上会更复杂一些,计算过程也会更多一些。

第三题

3.现有某地近15日天气预报数据如下wr表所示:

超经典SQL题 | 做完这4道面试题你就过关了

按照上表写出一条建表语句,要求(每个点2分):

① id字段为整型数据,主键约束,自增;

② dt字段为日期格式,非空;

③ weather字段为可变长度字符串,最大长度为30,非空;

④ min_tem与max_tem字段为整型数据,最大长度为5,非空;

⑤ 无拼写错误,无其他基本语法错误。

参考代码

create table wr(

id int primary key auto_increment,

dt date not null,

weather varchar(30) not null,

min_tem int(5) not null,

max_tem int(5) not null

);

解析

本道题考察的是建表语句的写法,之所以放在这里考察,主要是为了防止大家在学习各种select查询技巧的同时,不要忘记最开始学的内容,虽然约束部分在课程里没有讲,但是在"爱数据学院""互动社区"栏目下面有这部分内容的扩展喔,同时也有很多其他拓展知识,多多利用啊。

在这里还有人把insert用来建表我是没想到的,insert是插入数据的,create是新建数据库或者数据表的,这个可不能搞混了。

注:作为会SQL"增删改查"的你,当面试官问你建表语句怎么写,忘记了可就尴尬了~

第四题

4.根据wr表查询出连续3天以上(包含3天)都下雨(rain)的日期、天气、最高最低温度,字段顺序如下表所示:

超经典SQL题 | 做完这4道面试题你就过关了

注:思路逻辑正确但无法写出代码实现功能可得4分,代码逻辑完整和正确但代码最终无法实现功能可得8分,出现基本语法错误一处扣1分,最多扣4分,使用一条一条结果用union连接不得分。(能写出代码的同学尽量写出完整代码,并写出关键的注释来体现出思考逻辑,写不出代码的同学尽量表述出自己清晰的思路逻辑,尽可能的争取得分,能得一分是一分~)

解析

第一步:在这里需要借助一下辅助列:具体是用总的序号(rn)减去根据天气排的序号(rn_w)得到一个差值D_value,结果如下图,可以看到颜色相同的就是同一天气连续的的行(在这里rn列跟rn_w主要是为了方便大家理解,用熟悉之后可以不用展示出来):

超经典SQL题 | 做完这4道面试题你就过关了

参考代码

select dt,

weather,

min_tem,

max_tem,

ROW_NUMBER() over(order by dt) rn,

ROW_NUMBER() over(PARTITION by weather order by dt) rn_w,

ROW_NUMBER() over(order by dt) - ROW_NUMBER() over(PARTITION by weather order by dt) as D_value

from wr

order by dt;

第二步:再根据计算出来的D_value来计算连续的天(次)数,结果如下图:

超经典SQL题 | 做完这4道面试题你就过关了

参考代码

select dt,

weather,

min_tem,

max_tem,

count(1) over(partition by weather,D_value) as times

from (

select dt,

weather,

min_tem,

max_tem,

ROW_NUMBER() over(order by dt) rn,

ROW_NUMBER() over(PARTITION by weather order by dt) rn_w,

ROW_NUMBER() over(order by dt) - ROW_NUMBER() over(PARTITION by weather order by dt) as D_value

from wr

) D_wr

order by dt;

最后一步:直接用where条件来筛选出连续3天以上(包含3天)都下雨(rain)的结果:

超经典SQL题 | 做完这4道面试题你就过关了

参考代码

select dt,

weather,

min_tem,

max_tem

from (

select dt,

weather,

min_tem,

max_tem,

count(1) over(partition by D_wr.weather,D_wr.D_value) as times

from (

select dt,

weather,

min_tem,

max_tem,

ROW_NUMBER() over(order by dt) rn,

ROW_NUMBER() over(PARTITION by weather order by dt) rn_w,

ROW_NUMBER() over(order by dt) - ROW_NUMBER() over(PARTITION by weather order by dt) as D_value

from wr

) D_wr

) times_wr

where times>=3 and weather = "rain";

说在最后

  • 考点:聚合函数、case when、子查询、窗口函数等
  • 主要涉及到case when语句实现行转列,窗口函数解决不同维度的Top问题和连续性问题。
  • 考察同学们的审题是否细致、考查理解业务的能力
  • 将实际业务需求转换成构建SQL语句、并得到分析结果的能力

End.

作者:爱数据青林

来源:爱数据学院7月SQL月考题

本文为爱数据学院原创文章,未经许可禁止转载

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: