写在前面：这些都是根据朋友反馈整理的面试真题，来自于各个公司，推荐大家先独立思考并写出后对照答案，答案不唯一，只是提供思路。

另注：大部分SQL题都是笔试，规定手写而非上机，现场难度较大，建议在电脑上写出来后再用纸笔写一下效果更佳。

真题题目：

真题一：

问题：假设现在有一个听歌流水表，存储了用户们听歌单歌曲的记录。统计每个Top3歌单以及Top3歌单下的Top3歌曲

用户编号  歌单编号  歌单名称    歌曲编号    歌曲名称1    1    经典老歌    1    月亮代表我的心
2    1    经典老歌    1    月亮代表我的心
3    1    经典老歌    3    夜来香
4    1    经典老歌    4    我只在乎你
5    1    经典老歌    5    千言万语
6    1    经典老歌    5    千言万语
7    2    流行金曲    7    突然好想你
8    2    流行金曲    8    后来
9    2    流行金曲    9    童话
10    2    流行金曲    10    晴天
11    2    流行金曲    7    突然好想你
12    2    流行金曲    7    突然好想你
13    3    纯音乐集    13    二泉映月
14    3    纯音乐集    14    琵琶语
15    3    纯音乐集    15    梦回还
16    4    欧美音乐    16    Shape of My Heart
17    4    欧美音乐    17    Just the Way You Are
18    4    欧美音乐    18    Hello
19    4    欧美音乐    19    A Thousand Years
20    4    欧美音乐    20    Thinking Out Loud
21    4    欧美音乐    20    Thinking Out Loud
22    4    欧美音乐    18    Hello
23    4    欧美音乐    18    Hello
24    5    民谣时光    24    易燃易爆炸
25    5    民谣时光    25    成全
26    5    民谣时光    25    成全
27    5    民谣时光    25    成全

所谓的top3 就是最受欢迎的前3名

建表语句以及导入语句：
create table songs(
  uid int,
  lid int,
  list_name string,
  sid int,
  song_name string
)
row format delimited 
fields terminated by '\t'

真题二：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | string  |
+-------------+---------+
id 是这个表的主键,是连续。
题目：编写一个 SQL 查询，查找所有至少连续出现三次的数字。

比如：

Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
1 是唯一连续出现至少三次的数字

建表语句：
create table logs(
   id int,
   num int
);
INSERT INTO logs (id, num) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 1),
(6, 2),
(7, 2);

真题三：

有一张表A，里面有3个字段：学生（student），学科(subject)，分数(score)
+---------+---------+-------+
| student | subject | score ｜
| 张三 | 语文| 70 ｜
| 张三| 数学  | 80 ｜
| 李四| 语文| 60 ｜
| 王五| 英语| 90 ｜
+---------+---------+-------+

题目：请用一条sql语句查询出每个学科排名第三名的学生，他们对应的学科成绩，总成绩，以及总排名

-- 创建学生成绩表
CREATE TABLE IF NOT EXISTS A (
    student STRING COMMENT '学生姓名',
    subject STRING COMMENT '科目',
    score INT COMMENT '分数'
)
COMMENT '学生成绩表';
-- 插入示例数据
INSERT INTO TABLE A VALUES
('张三', '语文', 70),
('张三', '数学', 80),
('李四', '语文', 60),
('王五', '英语', 90);

真题四：

图书馆每日人流量信息被记录在表 Info 的这三列信息中:序号 (id)、日期 (date)、人流量 (people)。

请编写一个查询语句，找出高峰期时段， 要求连续三天及以上，并且每天人流量均不少于 100。

为什么这一次，我们创建表的时候，没有指定分隔符呢？因为我们现在的数据都是 insert into 进入的，可以不指定分隔符。

建表语句：
create table info(
  id int,
  `date` string,
  people int
);
INSERT INTO Info(ID, Date, People) 
VALUES (1, '2018-01-01', 70), 
(2,'2018-01-02', 100), 
(3,'2018-01-03', 120), 
(4,'2018-01-04', 170), 
(5,'2018-01-05', 120),
(6,'2018-01-06', 80), 
(7,'2018-01-07', 120), 
(8,'2018-01-08', 120), 
(9,'2018-01-09', 120), 
(10,'2018-01-10', 120), 
(11,'2018-01-11', 120), 
(12,'2018-01-12', 120), 
(13,'2018-01-13', 70), 
(14,'2018-01-14', 120);

真题答案：

第一题：

加载数据：

load data local inpath '你存放文本的目录' into table 表名;

示例：

load data local inpath '/home/hivedata/zuoye04.txt' into table songs;

解答：

select * from songs;

with t as (
    select list_name,count(1) num from songs group by list_name
),t2 as (
    select *,dense_rank() over (order by num desc) pm from t
),t3 as (
    select list_name from t2 where pm <=3
),t4 as (
    select list_name,song_name,
       count(1) s_num
      from songs where list_name in (select list_name from t3)
     group by list_name,song_name
),t5 as (
    select *,dense_rank() over (partition by list_name order by s_num desc) pm from t4
)
select list_name,song_name,s_num from t5 where pm <=3;

第二题：

-- 第一种思路：
with t as (
    select *,
       id-row_number() over (partition by num order by id) temp
       from logs
)
select num,count(1) from t group by num,temp having count(1) >=3;

-- 第二个思路：
with t as (
    select *,
       lag(num,1) over(order by id) lagnum,
       lead(num,1) over(order by id) leadnum
       from logs
)
select distinct num from t where num=lagnum and num= leadnum;
-- 第三个思路：
select distinct l1.num from logs l1 , logs l2 ,logs l3
  where l1.num = l2.num and l2.num= l3.num
   and l1.id + 1 = l2.id and l2.id+1 = l3.id;

第三题：

with t as (
    select *,
       sum(score) over(partition by student) sumScore
       from A
),t2 as (
   select *,
       dense_rank() over ( order by sumScore desc) zpm,
       dense_rank() over (partition by subject order by score desc) xkpm

       from t
)
select * from t2 where xkpm = 3;

第四题：

with t as (
    select * ,
       date_sub(`date`,row_number() over (order by `date`)) diffdate
      from   info where people>=100
),t2 as (
    select *,
       count(1) over(partition by diffdate ) lxdays
       from t
)
select `date`,people from t2 where lxdays >=3 order by `date`;