面试题之sql试题

最新推荐文章于 2022-08-09 10:52:06 发布

蒙奇.D.庆

最新推荐文章于 2022-08-09 10:52:06 发布

阅读量267

点赞数

分类专栏： sql 面试题

本文链接：https://blog.csdn.net/qq_45292079/article/details/105762510

版权

面试题同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

sql

10 篇文章 0 订阅

订阅专栏

第二高薪水

在这里插入图片描述

select IFNULL((select distinct(Salary) 
from Employee
order by Salary desc
limit 1,1),null) as SecondHighestSalary

注：将不同的薪资按降序排序，然后使用 LIMIT 子句获得第二高的薪资。解决 “NULL” 问题的另一种方法是使用 “IFNULL” 函数

第N高的薪水

在这里插入图片描述
解析

分组排名是数据库中的一个经典题目，实际上又根据排名的具体细节可分为3种场景：

1.连续排名，例如薪水3000、2000、2000、1000排名结果为1-2-3-4，体现2.同薪不同名，排名类似于编号
同薪同名但总排名不连续，例如同样的薪水分布，排名结果为1-2-2-4
3.同薪同名且总排名连续，同样的薪水排名结果为1-2-2-3
不同的应用场景可能需要不同的排名结果，也意味着不同的查询策略。本题的目标是实现第三种排名方式下的第N个结果，且是全局排名，不存在分组的问题，实际上还要相对简单一些。

值得一提的是：在Oracle等数据库中有窗口函数，可非常容易实现这些需求，而MySQL直到8.0版本也引入相关函数。当前MySQL OJ系统为5.6版本，所以不能直接应用。

为此，本文提出以下几种解决思路，仅供参考。
如果有意可关注文末个人公众号，查看一篇更为详尽的分组排名问题。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET N := N-1;
  RETURN (
     
      SELECT 
            salary
      FROM 
            employee
      GROUP BY 
            salary
      ORDER BY 
            salary DESC
      LIMIT N, 1
  );
END

分数排名

在这里插入图片描述

select a.Score as Score,
(select count(distinct b.Score) from Scores as b where b.Score>=a.score)as Rank
from Scores as a
order by a.Score desc;

讲解

select Score as Score
from Scores;

#将选择出来的命名为a
select a.Score as Score
from Scores as a;

#排序
select a.Score as Score
from Scores as a
order by a.Score DESC;

#比较难的是第二部分。假设现在给你一个分数X，如何算出它的排名Rank呢？ 我们可以先提取出大于等于X的所有分数集合H，将H去重后的元素个数就是X的排名。比如你考了99分，但最高的就只有99分，那么去重之后集合H里就只有99一个元素，个数为1，因此你的Rank为1。 先提取集合H：
select b.Score
from Scores as b
where b.Score >=X;

#我们要的是集合H去重之后的元素个数，因此升级为：
select count(distinct b.Score) from Scores b where b.Score >= X as Rank;

-- 而从结果的角度来看，第二部分的Rank是对应一部分的分数来的，所以这里的X就是上面的a.Score，
select count(distinct b.Score) from Scores b where b.Score >= a.Score as Rank;

-- 把两部分结合在一起为：
select a.Score as Score,
(select count(distinct b.Score) from Scores as b where b.Score>=a.score)as Rank
from Scores as a
order by a.Score desc;

连续出现的数字

在这里插入图片描述

若只是针对此题（连续出现的次数少）

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num

思路：连续出现的意味着相同数字的 Id 是连着的，由于这题问的是至少连续出现 3 次，我们使用 Logs 并检查是否有 3 个连续的相同数字。三表联查
但是当要求连续次数多时，就不能使用多表联查

基于row_number() over()的写法

select distinct(num) "ConsecutiveNums"
from (
	select num,(row_number() over(order by id )-row_number() over(partition by num order by id))                rank_
	from Logs
) tmp
group by rank_,num
having count(rank_)>=3;

想了解row_number() over()，点击此链接
思路：

1、由于要获取至少连续三次出现的数字，看到这个题肯定是会变的，如果是至少连续出现四次呢(100次呢)，咱们连接四个表(连接一千个?)？这种方法肯定是不可取的。
2、找规律，找出这连续起来的数字有什么规律呢，我们会发现连续的数字是相同的数字，但是id有可能不是连续的，我们就需要通过对结果集进行再次编号，让其变成连续的。
原始数据:
3、首先我们获取到对每条数据编号从1开始使用row_number()函数使用id来排序既row_number() over(order by id)
4、然后我们通过另一种方式排序将，这些num值一样的进行排序，然后对其编号同样使用row_bumber()使用num来分组使用id排序 over(partition by num order by id)
5、通过3、4步骤我们能得到什么呢，两个相减之后我们可以得到，只要是相等的，则相减的值是一样的。而且如果不连续的话相减值也不一样。

通过排名算法实现

select distinct  dd.Num ConsecutiveNums
from  (
select d.Num,
       @n :=if(@pre=Num,@n+1,@n:=1) count,
       @pre:=Num
from Logs d,
     (select @pre:=null, @n :=1)r)dd
where dd.count>=3;

思路：

通过排名算法实现,当前值@n和上一个值@pre比较,如果相等@n+1 否则@n:=1
最后在查询 count>=3的数据

部门工资前三高的所有员工

在这里插入图片描述

SELECT 
B.Name AS Department,
A.Name AS Employee1,
A.Salary
FROM (SELECT DENSE_RANK() OVER (partition by DepartmentId order by Salary desc) AS ranking,DepartmentId,Name,Salary
      FROM Employee1) AS A
JOIN Department AS B ON A.DepartmentId=B.id
WHERE A.ranking<=3

注：1、rank()函数
此排序方法进行排序时，相同的排序是一样的，而且下一个不同值是跳着排序的。
例：1，2，2，4
2、row_number()函数
此方法不管排名是否有相同的，都按照顺序1，2，3……n
例：1，2，3，4
3、dense_rank()函数
此方法对于排名相同的名次一样，且后面名次不跳跃
例：1，2，2，3
由结果观之，应用dense_rank() over()

上升的温度

在这里插入图片描述
注：
DATEDIFF(expr1,expr2)
DATEDIFF() returns expr1 − expr2
前一个参数减去后一个参数，两值相减以天为单位

select w1.id from weather w1,weather w2 where datediff(w1.recorddate,w2.recorddate) = 1 and w1.temperature > w1.temperature

行程和用户

在这里插入图片描述

在这里插的述
解法一

SELECT t.Request_at AS `Day`,ROUND(SUM(CASE t.`Status` WHEN 'cancelled_by_client' THEN 1 WHEN 'cancelled_by_driver' THEN 1 ELSE 0 END)/COUNT(t.`Status`) ,2)AS `Cancellation Rate`
FROM Trips t
JOIN Users d ON t.Client_Id = d.Users_Id AND d.Banned = 'NO'
JOIN Users c ON t.Driver_Id = c.Users_Id AND c.Banned =  'NO'
WHERE t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.Request_at

注：
在mysql中，round函数用于数据的四舍五入，它有两种形式：

1、round(x,d) ，x指要处理的数，d是指保留几位小数

这里有个值得注意的地方是，d可以是负数，这时是指定小数点左边的d位整数位为0,同时小数位均为0；

2、round(x) ,其实就是round(x,0),也就是默认d为0；

case when 的语法：

简单函数
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
当case后的变量是when后面的变量时，被赋予then后的值，否则，被赋予else后的值
将顾客或者司机取消的订单赋值为1，完成的赋值为0，然后取平均值，即为取消率
解法二

SELECT T.request_at AS `Day`, 
	ROUND(
			SUM(
				IF(T.STATUS = 'completed',0,1)
			)
			/ 
			COUNT(T.STATUS),
			2
	) AS `Cancellation Rate`
FROM Trips AS T
JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned ='No')
JOIN Users AS U2 ON (T.driver_id = U2.users_id AND U2.banned ='No')
WHERE T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at

与解法一有异曲同工之妙

体育馆的人流量

在这里插入图片描述

解法一

select
	id, to_char(visit_date, 'yyyy-mm-dd') as visit_date, people
from
	(select
		id, visit_date, people,
		count(1) over (partition by offset) cnt
	from
		(select
			id, visit_date, people,
			(row_number() over (order by id) - id) offset
			-- 题目中的 id 列的值是单调递增的，可认为是所有记录的行号信息。 然后里层的 row_number() 给那些所有 people 值大行 100 的记录打个行号值。
你徒手在纸上写一下两个行号的值，然后求一下差值（offset），你就能发现规律了：那些连续出现的记录的 offset 的值是一样的。
只要发现了这个规律，再后边的逻辑就简单了。
		from stadium
		where people >= 100
		)
	)
where cnt >= 3   -- 连续 3 天（及以上）
order by id