sql 累计占比_sql 面试题(难题汇总)

除了sqlzoo的练习题外，为了加大练习强度，我还不断搜集一些其他的SQL题，特别是大小厂面试题的类型。我把比较难的题挑出来做了一个合集，才有了这篇文章。我会把题目和答案分开放，方便先自己练习，不懂再查答案。

【1】腾讯面试题

table_A （ 用户userid和登录时间time）求连续登录3天的用户数

【2】滴滴面试题

学生表:tb_student(name:学生姓名，id：学号，class：班级，in_time：入学时间，age:年龄，sex：性别，major：专业)

学生成绩表：tb_score(id：学号，course：课程，score：分数)

（1）筛选出2017年入学的“计算机”专业年龄最小的10位同学名单（姓名、学号、班级、年龄）

（2）统计每个班同学各科成绩平均分大于80分的人数和人数占比

【3】滴滴面试题

table1（id：自增id，money：费用）问题：按id顺序累加money，取出累计值与1000相差最小差值的id。

【4】滴滴面试题

Employee 表包含所有员工信息，每个员工有其对应的 Id, Name,Salary 和 DepartmentId。

+----+-------+--------+--------------+

| Id | Name | Salary | DepartmentId |

+----+-------+--------+--------------+

| 1 | Joe | 70000 | 1 |

| 2 | Henry | 80000 | 2 |

| 3 | Sam | 60000 | 2 |

| 4 | Max | 90000 | 1 |

Department 表包含公司所有部门的信息。

+----+----------+

| Id | Name |

+----+----------+

| 1 | IT |

| 2 | Sales |

+----+----------+

编写一个 SQL 查询，找出每个部门工资第二高的员工。

【5】这个题来源于（佰初：面试数据分析会遇到的SQL题）我筛选了一些有点难度的。

（1）查询“北京佰初数据有限公司”各个职级（post_grade）上的最高薪水、最低薪水、平均薪水.

（2）将“北京佰初数据有限公司”的员工两两组队，取出所有可能的组合。

（3）查询每个公司薪资排名前三的岗位id、薪资，按公司id升序，按薪资降序排序

（4）假设4张表存放在 MySQL 数据库中，查询“北京佰初数据有限公司”过去7天每天的新入职员工数量，按入职日期（date_of_entry）倒序排列.

（5）假设4张表存放在 MySQL 数据库中，查询“北京佰初数据有限公司”2019年5月每天的新入职员工数量，按入职日期（date_of_entry）倒序排列.

（6）查询“北京佰初数据有限公司”每个部门里薪资高于部门平均薪资的员工姓名和部门名字（department_name）

牛客网题（筛选）

【1】请取出 BORROW表中日期(RDATE字段)为当天的所有记录？(RDATE字段为datetime型，包含日期与时间)。

【2】有一张学生成绩表sc（sno 学号，class 课程，score 成绩），请查询出每个学生的英语、数学的成绩（行转列，一个学生只有一行记录）。

【3】有一个名为app的MySQL数据库表，app_id（应用ID）， version_code（应用的版本号），download_count （当前版本的下载量）。

查询每个应用中总下载量最大的版本号和次数

+--------+--------------+----------------+

| app_id | version_code | download_count |

+--------+--------------+----------------+

| 1 | 10 | 90 |

| 1 | 11 | 100 |

| 1 | 10 | 20 |

| 2 | 15 | 10 |

| 2 | 16 | 15 |

| 2 | 17 | 30 |

| 2 | 16 | 5 |

| 3 | 2 | 50 |

+--------+--------------+----------------+

力扣网（筛选）

【1】

（1）编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

+----+--------+

| Id | Salary |

+----+--------+

| 1 | 100 |

| 2 | 200 |

| 3 | 300 |

+----+--------+

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

+---------------------+

| SecondHighestSalary |

+---------------------+

| 200 |

+---------------------+

（2）原题不变，查询第n高薪水（这道题不用做，难度比较大）

【2】

【3】

————————————————————

参考答案

【1】

SELECT userid,time,
DATE_SUB(left(time,10),INTERVAL t.rn DAY) as flag_date,count(distinct left(time,10))
from (SELECT userid,time,dense_rank ()over(PARTITION by userid ORDER BY time) as rn
from  table_A) as t
GROUP BY user_id,flag_date
HAVING count(distinct left(time,10))>=3;
解析：用窗口函数dense_rank 进行排序，日期函数DTESUB，将（日期-排序数）得到一个相等的日期flag_date，以其进行分组，将连续的日期分为一组。考虑到一天内多次登录，需要用left函数截取时间的日期部分，最后count进行去重操作。

【2】

（1）

select name,id,class,age
from tb_student
where year(in_time)=2017 
and major = '计算机'
ORDER BY age ASC
limit 10;
（2）

SELECT a.class,
sum(case when aaa.x > 80 then 1 else 0 end)as num_80 ,
(sum(case when aaa.x > 80 then 1 else 0 end)/count(*))as proportion
from tb_student a 
INNER JOIN (SELECT id,avg(score)as x from tb_score GROUP BY id)as aaa
on a.id = aaa.id
GROUP BY class;
解析：写一个子查询，从score表中得到以学号分组的学生各科平均分。命名为aaa，将其与学生表内联结。再以class分组，得到以class分组的各班学生的平均分，最后通过case语句，sum聚合函数得到平均分>80分的计数，和所占各班总人数比例。

case语句也可以用if语句来代替

写法2：

SELECT a.class,count(if(aaa.avg>80,true,null)) as numover80,
count(if(aaa.avg>80,true,null))/count(a.id) as total
from tb_student a 
INNER JOIN (SELECT id,avg(score)as x from tb_score GROUP BY id)as aaa
on a.id = aaa.id
GROUP BY class;
【3】

写法1：

select id,
abs((sum(money)over(order by id))-1000) as x
from table1
order by x asc
limit 1;
写法2：

select id,min(abs(x-1000))
FROM(SELECT id,
sum(money)over(ORDER BY id)as x
from table1)as aaa
【4】

select Departmentid,Name,Salary,rn
from(select *,
row_number()over(PARTITION by Departmentid 
ORDER BY Salary desc) as rn
from Employee)as a
WHERE rn=2
【5】

（1）

select.post_grade,max(salary),min(salary),avg(salary)
from table_staff  a
inner join table_post b
on a.post_id =b.post_id
where company_id in
 (select company id from table_company where company_name ='北京佰初数据有限公司')
group by post_grade;
(2) 这个题考察的是自连接查询

select a.staff name as team_a,b.staff name as team_b 
from table_staff a, table_staff b
where company_id in
 (select company id from table_company where company_name ='北京佰初数据有限公司')
and a.staff_id <b.staff_id;
（3）这个题考察窗口函数

select post_id ,salary
from(select * , row_number()over(partition by company_id order by salary desc) as rn
from table staff) as x
where rn<=3; 
（4）这个题需要用到日期函数 dateadd()和getdate()

select date_of_entry,count(staff_id) as num
from table_staff
where date_of_entry>=dateadd(day,-7,getdate())
and company_id in
 (select company id from table_company where company_name ='北京佰初数据有限公司')
group by date of entry
order by date of entry desc;
（5）

select date_of_entry,count(staff_id)as num
from table_staff
where date_of_entry>='2019-05-01'
and date_of_entry<'2019-06-01'
and company_id in
 (select company id from table_company where company_name ='北京佰初数据有限公司')
group by date of entry
order by date of entry desc;
（6）这个题考察了窗口函数、聚合函数、联结表、子查询的使用

select x.staff_name,x.department_name
from(select*,avg(salary)over(PARTITION by department_id) as salary_avg
from table_staff a
join table_post b on a.post_id =b.post_id
join table_post c on a.company_id = c.company_id 
where company_id in
 (select company id from table_company where company_name ='北京佰初数据有限公司')
) as x
where x.salary>x.salary_avg;
牛客网（筛选）

【1】考察日期函数

select *
from BORROW 
where datediff(dd,RDATE,getdate())=0
DATEDIFF() 函数返回两个日期之间的时间。

语法：datediff（datepart,startdate,enddate）

注意：mysql中datediff只有2个参数datediff（startdate,enddate）

startdate 和 enddate 参数是合法的日期表达式。

datepart 参数可以是下列的值：

datediff（dd,RDDATE,getdate()）==0含义，即返回以日为单位（dd），和当前日期（getdate）相差为0日的RDDATE

这里用这个函数的意义在于，RDDATE包括日期和时间，这里因为有时间，和getdate不能直接比较，所以用datediff转换为范围。

【2】

select sno,
sum(if(class='english',score,0)) as english,
sum( if(class='math',score,0) ) as math
from sc
where class in('english','math')
group by sno
【3】

select app_id,version_code,sum_donload
from(select *,sum(download_count)as sum_donload,
dense_rank()over(partition by app_id order by sum(download_count) desc) as dr
from app
GROUP BY app_id,version_code)as x
WHERE dr =1;
力扣网（筛选）

【1】

（1）

仅满足查询第二高薪水

select distinct Salary as SecondHighestSalary
from Employee
order by Salary desc limit 1,1;
limit N # 返回 N 条记录

offset M # 跳过 M 条记录，M 默认为 0

limit M,N # 相当于 limit N offset M，从第 M 条记录开始，返回 N 条记录

同时满足查询第二高薪水，并在不存在第二高薪水时返回null。

写法一：

select(select distinct Salary
from Employee
order by Salary desc limit 1,1) as SecondHighestSalary;
写法二：

select ifnull(
(select distinct Salary 
from Employee 
order by Salary desc limit 1,1),null) as SecondHighestSalary;
（2）

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
 set n = N-1;
 RETURN (select ifnull(
                       (select distinct Salary 
                        from Employee 
                        order by Salary desc limit n,1),null) as SecondHighestSalary
   );
END
【2】

DATEDIFF 函数，可以计算两者的日期差

DATEDIFF('2007-12-31','2007-12-30'); # 1

DATEDIFF('2010-12-30','2010-12-31'); # -1

select a.Id
from weather a, weather b
where a.Temperature > b.Temperature
and datediff(a.RecordDate,b.RecordDate) = 1
【3】

写法一：

update salary set 
sex =if (sex='f','m','f');
写法二：

update salary set
sex = case sex when "m" then "f" else "m" end;