【leetcode-sql】569-571、574、577-579

最新推荐文章于 2022-04-01 11:11:37 发布
最新推荐文章于 2022-04-01 11:11:37 发布
阅读量252 收藏
点赞数
分类专栏： Sql练习 文章标签： sql leetcode 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_37875391/article/details/120563602
版权
569. 员工薪水中位数

Employee 表包含所有员工。Employee 表有三列：员工Id，公司名和薪水。

+-----+------------+--------+
|Id   | Company    | Salary |
+-----+------------+--------+
|1    | A          | 2341   |
|2    | A          | 341    |
|3    | A          | 15     |
|4    | A          | 15314  |
|5    | A          | 451    |
|6    | A          | 513    |
|7    | B          | 15     |
|8    | B          | 13     |
|9    | B          | 1154   |
|10   | B          | 1345   |
|11   | B          | 1221   |
|12   | B          | 234    |
|13   | C          | 2345   |
|14   | C          | 2645   |
|15   | C          | 2645   |
|16   | C          | 2652   |
|17   | C          | 65     |
+-----+------------+--------+

请编写SQL查询来查找每个公司的薪水中位数。挑战点：你是否可以在不使用任何内置的SQL函数的情况下解决此问题。

+-----+------------+--------+
|Id   | Company    | Salary |
+-----+------------+--------+
|5    | A          | 451    |
|6    | A          | 513    |
|12   | B          | 234    |
|9    | B          | 1154   |
|14   | C          | 2645   |
+-----+------------+--------+

提交答案：

# Write your MySQL query statement below
select Id, Company, Salary
from
(select Id, Company, Salary, ROW_NUMBER() over(partition by Company order by Salary) `index`, count(*) over(partition by Company) `all`
from Employee) t
where t.index IN (ceiling((t.all+1) / 2), ceiling(t.all / 2))

解析：

  • 使用ROW_NUMBER()结合over()函数给每个分组（按company分）的Salary按照降序;
  • 由于最终需要使用当前序号跟当前组的数量来进行比较，所以使用count(*) over(partition by Company)记录每组的总数量；
  • 当前index位于中间位置，使用ceiling()上取整函数，这样对于all为奇数来说不受影响。
570. 至少有5名直接下属的经理

Employee 表包含所有员工和他们的经理。每个员工都有一个 Id，并且还有一列是经理的 Id。

+------+----------+-----------+----------+
|Id    |Name 	  |Department |ManagerId |
+------+----------+-----------+----------+
|101   |John 	  |A 	      |null      |
|102   |Dan 	  |A 	      |101       |
|103   |James 	  |A 	      |101       |
|104   |Amy 	  |A 	      |101       |
|105   |Anne 	  |A 	      |101       |
|106   |Ron 	  |B 	      |101       |
+------+----------+-----------+----------+

给定 Employee 表，请编写一个SQL查询来查找至少有5名直接下属的经理。对于上表，您的SQL查询应该返回：

+-------+
| Name  |
+-------+
| John  |
+-------+

注意:
没有人是自己的下属。

提交答案：

# Write your MySQL query statement below
select Name
from Employee
where Id IN
(select ManagerId
from Employee
group by ManagerId
having count(*) >= 5)

解析：使用分组函数首先筛选出ManagerId，当前Id属于{ManagerId}集合即可。

571. 给定数字的频率查询中位数

Numbers 表保存数字的值及其频率。

+----------+-------------+
|  Number  |  Frequency  |
+----------+-------------|
|  0       |  7          |
|  1       |  1          |
|  2       |  3          |
|  3       |  1          |
+----------+-------------+

在此表中，数字为 0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3，所以中位数是 (0 + 0) / 2 = 0。

+--------+
| median |
+--------|
| 0.0000 |
+--------+

请编写一个查询来查找所有数字的中位数并将结果命名为 median 。

提交答案：

# Write your MySQL query statement below
select avg(Number) `median`
from
(select Number, (select sum(Frequency) from Numbers) `all`, sum(Frequency) over(order by Number) `a`, sum(Frequency) over(order by Number DESC) `b`
from Numbers) t
where t.a>=t.all/2 and t.b>=t.all/2

解析：

之前在牛客上做过一道极其类似的题，思路是：

  • 当某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数。

  • sum(Frequency) over(order by Number)表示在全表上累加从第一行到当前行。

  • 这样计算出的中位数可能有两个，所以取平均值。

574. 当选者

表: Candidate

+-----+---------+
| id  | Name    |
+-----+---------+
| 1   | A       |
| 2   | B       |
| 3   | C       |
| 4   | D       |
| 5   | E       |
+-----+---------+

表: Vote

+-----+--------------+
| id  | CandidateId  |
+-----+--------------+
| 1   |     2        |
| 2   |     4        |
| 3   |     3        |
| 4   |     2        |
| 5   |     5        |
+-----+--------------+
id 是自动递增的主键，
CandidateId 是 Candidate 表中的 id.

请编写 sql 语句来找到当选者的名字，上面的例子将返回当选者 B.

+------+
| Name |
+------+
| B    |
+------+

注意:

  1. 你可以假设没有平局，换言之，最多只有一位当选者。

提交答案：

# Write your MySQL query statement below
select c.Name
from
(select CandidateId
from
(select CandidateId, count(*) `cnt`
from Vote
group by CandidateId) t
where cnt=(select max(cnt) from (select CandidateId, count(*) `cnt`
            from Vote
            group by CandidateId) t)) t1
JOIN Candidate c ON c.id=t1.CandidateId

解析：能通过，但是写麻烦了。

忘记了group by CandidateId之后，可以order by来按照每个分组的数量给分组排序，最后使用limit获取数量最多的。

附上简单做法：

select Name
from (
  select CandidateId as id
  from Vote
  group by CandidateId
  order by count(id) desc
  limit 1
) as Winner join Candidate
on Winner.id = Candidate.id
577. 员工奖金

选出所有 bonus < 1000 的员工的 name 及其 bonus。

Employee 表单

+-------+--------+-----------+--------+
| empId |  name  | supervisor| salary |
+-------+--------+-----------+--------+
|   1   | John   |  3        | 1000   |
|   2   | Dan    |  3        | 2000   |
|   3   | Brad   |  null     | 4000   |
|   4   | Thomas |  3        | 4000   |
+-------+--------+-----------+--------+
empId 是这张表单的主关键字

Bonus 表单

+-------+-------+
| empId | bonus |
+-------+-------+
| 2     | 500   |
| 4     | 2000  |
+-------+-------+
empId 是这张表单的主关键字

输出示例：

+-------+-------+
| name  | bonus |
+-------+-------+
| John  | null  |
| Dan   | 500   |
| Brad  | null  |
+-------+-------+

提交答案：

# Write your MySQL query statement below
select e.name,b.bonus
from Employee e
LEFT JOIN
Bonus b
ON e.empId=b.empId
where b.bonus < 1000 or b.bonus is null

解析：

主要考虑的点在于如何把bonus为null的员工给展示出来，所以先做left连接查询，再进行where条件判断。

578. 查询回答率最高的问题

从 survey_log 表中获得回答率最高的问题，survey_log 表包含这些列：id, action, question_id, answer_id, q_num, timestamp。

id 表示用户 id；action 有以下几种值：“show”，“answer”，“skip”；当 action 值为 “answer” 时 answer_id 非空，而 action 值为 “show” 或者 “skip” 时 answer_id 为空；q_num 表示当前会话中问题的编号。

请编写 SQL 查询来找到具有最高回答率的问题。

示例：

输入：
+------+-----------+--------------+------------+-----------+------------+
| id   | action    | question_id  | answer_id  | q_num     | timestamp  |
+------+-----------+--------------+------------+-----------+------------+
| 5    | show      | 285          | null       | 1         | 123        |
| 5    | answer    | 285          | 124124     | 1         | 124        |
| 5    | show      | 369          | null       | 2         | 125        |
| 5    | skip      | 369          | null       | 2         | 126        |
+------+-----------+--------------+------------+-----------+------------+
输出：
+-------------+
| survey_log  |
+-------------+
|    285      |
+-------------+
解释：
问题 285 的回答率为 1/1，而问题 369 回答率为 0/1，因此输出 285 。

**提示：**回答率最高的含义是：同一问题编号中回答数占显示数的比例最高。

提交答案：

# Write your MySQL query statement below
select question_id `survey_log`
from
(select question_id, sum(CASE WHEN answer_id is null THEN 0 ELSE 1 END)/count(*) `rating`
from survey_log
group by question_id
order by rating DESC
limit 1) t

解析：

  • group by question_id通过问题分组，count(*)按问题分组统计所有action的数量；
  • sum(CASE WHEN answer_id is null THEN 0 ELSE 1 END)计算按问题分组之后action为answer(也就是answer_id不为null)的数量；
  • 按占比rating降序取第一个即可。
579. 查询员工的累计薪水

Employee 表保存了一年内的薪水信息。

请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。

结果请按 Id 升序，然后按 Month 降序显示。

示例：
输入：

| Id | Month | Salary |
|----|-------|--------|
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 1  | 2     | 30     |
| 2  | 2     | 30     |
| 3  | 2     | 40     |
| 1  | 3     | 40     |
| 3  | 3     | 60     |
| 1  | 4     | 60     |
| 3  | 4     | 70     |

输出：

| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |
| 2  | 1     | 20     |
| 3  | 3     | 100    |
| 3  | 2     | 40     |

解释：

员工 ‘1’ 除去最近一个月（月份 ‘4’），有三个月的薪水记录：月份 ‘3’ 薪水为 40，月份 ‘2’ 薪水为 30，月份 ‘1’ 薪水为 20。

所以近 3 个月的薪水累计分别为 (40 + 30 + 20) = 90，(30 + 20) = 50 和 20。

| Id | Month | Salary |
|----|-------|--------|
| 1  | 3     | 90     |
| 1  | 2     | 50     |
| 1  | 1     | 20     |

员工 ‘2’ 除去最近的一个月（月份 ‘2’）的话，只有月份 ‘1’ 这一个月的薪水记录。

| Id | Month | Salary |
|----|-------|--------|
| 2  | 1     | 20     |

员工 ‘3’ 除去最近一个月（月份 ‘4’）后有两个月，分别为：月份 ‘3’ 薪水为 60 和 月份 ‘2’ 薪水为 40。所以各月的累计情况如下：

| Id | Month | Salary |
|----|-------|--------|
| 3  | 3     | 100    |
| 3  | 2     | 40     |

提交答案：

# Write your MySQL query statement below
select Id, Month, sum(Salary) over(partition by Id order by Month ASC range between 2 preceding and 0 following) `Salary`
from Employee
where (Id, Month) NOT IN
(select Id, max(Month)
from Employee
group by Id)
order by Id, Month DESC

解析：

  • 这个题理刚开始理解错了，以为是当前月+表里面存在的最近两个月份的薪资累加和，然后发现不是这样的；

  • 实际上求的是月份为month、month-1、month-2的累加薪资；

  • 1️⃣找到每个id对应的最大月份方便后续排除

  select Id, max(Month)
  from Employee
  group by Id
  • 2️⃣筛选Employee里(Id, Month)不在 第一步的结果里即可，In连接符可以用多字段哦
where (Id, Month) NOT IN (subquery)

  • 3️⃣之前看over函数的时候没有注意还有条件range和rows，解释一下：

    • range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内；
    • rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）；

    什么意思呢？对应本道题而言：

    使用range就是去计算order by Month里Month的加减法，也就是计算7月、6月、5月，不会管表里是不是真的有6月和5月；

    使用rows是去每个Id下按月份排好序的前面三个，比如记录里7月之前是4月和三月，就计算这三个月。

发现有些题真不是看一看就能对的！真的要自己做一遍！
以上是leetcode7道sql题的解答，如有考虑错误或者解法问题，请指正。🙂

MiSeRyⅢ
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
【LeetCode-SQL】571. 给定数字的频率查询中位数
HeavenDan的博客
04-01 748
目录一、题目二、解决1、sum() over() + avg()2、join三、参考 一、题目 Numbers 表： +-------------+------+ | Column Name | Type | +-------------+------+ | num | int | | frequency | int | +-------------+------+ num 是这张表的主键。这张表的每一行表示某个数字在该数据库中的出现频率。 中位数 是将数据样本中半数较高值和半数较
Leetcode 579. Find Cumulative Salary of an Employee
chelseajcole的专栏
08-09 1857
drop table employees2 Create table employees2(Id int,[Month] int,salary int); insert into employees2 values(1,1,20); insert into employees2 values(2,1,20); insert into employees2 values(1,2,30); ins...
[SQL]LeetCode577.员工奖金 | Employee Bonus
weixin_30478619的博客
10-25 156
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★➤微信公众号：山青咏芝（shanqingyongzhi）➤博客园地址：山青咏芝（https://www.cnblogs.com/strengthen/）➤GitHub地址：https://github.com/strengthen/LeetCode➤原文地址：https://www.cnblogs.com/streng...
常见问题where&group by ——《面试中被录取的候选人》LeetCode Plus 会员专享题【详细解析】Hive / MySQL
w0714的博客
12-20 3544
今天给大家分享的题是LeetCode的第2041题——面试中被录取的候选人。
【LeetCode-SQL】579. 查询员工的累计薪水
HeavenDan的博客
04-01 2236
目录一、题目二、解决1、sum() over()2、join三、参考 一、题目 Employee 表保存了一年内的薪水信息。 请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。 结果请按 Id 升序，然后按 Month 降序显示。 示例： 输入： | Id | Month | Salary | |----|-------|--------| | 1 | 1 | 20 | | 2 | 1 | 20
leetcode176-Leetcode-SQL:LeetcodeSQL实践
07-01
第 176 章Leetcode-SQL Leetcode SQL 实践，包括表创建示例和解决方案。 Easy-175-合并两个表.txt Easy-176 秒最高薪水.txt
Leetcode-SQL
02-18
Leetcode-SQL介绍该存储库涵盖Leetcode SQL问题，并由MySQL解决Topcis的LeetCode SQL问题＃ 问题解决方案等级概念175简单左加入176简单限位偏移/合并/ Ifnull 177中等的放178中等的视窗功能（Dense_Rank） 180中等的...
Leetcode-SQL-
04-02
Leetcode SQL质疑三个级别：容易，中等和困难
leetcode答案-LeetCode-SQL:LeetCodeSQL问题的已解决答案
06-30
leetcode 答案LeetCode-SQL LeetCode SQL 问题的已解决答案
查询员工的累计薪水（困难）
weixin_45551676的博客
06-16 462
Employee 表保存了一年内的薪水信息。 请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。 结果请按 Id 升序，然后按 Month 降序显示。 | Id | Month | Salary | |----|-------|--------| | 1 | 1 | 20 | | 2 | 1 | 20 | | 1 | 2 | 30 | | 2 | 2 | 30
LeetCode MySQL 579. 查询员工的累计薪水（over(rows n preceding)选取窗口1+前n条）
Michael是个半路程序员
08-01 1486
文章目录1. 题目2. 解题 1. 题目 Employee 表保存了一年内的薪水信息。 请你编写 SQL 语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算）。 结果请按 Id 升序，然后按 Month 降序显示。 示例： 输入： | Id | Month | Salary | |----|-------|--------| | 1 | 1 | 20 | | 2 | 1 | 20 | | 1 | 2 | 3
2021-04-05
小蚂蚁的博客
04-05 127
超过经理收入的员工——mysql面试题 需求：Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。 数据样式： 展示效果： create table If Not Exists 5_Employee (Id int, Name varchar(255), Salary int, ManagerId int); truncate table 5_Employee; inser
每日一题-33（查询员工的累计薪水）
Txixi的博客
12-06 641
题33： 根据下表编写SQL语句，对于每个员工，查询他除最近一个月（即最大月）之外，剩下每个月的近三个月的累计薪水（不足三个月也要计算），结果请按Id升序，然后按Month降序显示。 解题思路：用sum（）over（partition by…order by…rows…）聚合窗口函数。 （1）按照员工ID分组，月份升序，对薪水Salary进行逐月累计求和； （2）（1）获得的累计薪水还要加上一个限制条件：最近三个月。所以再加上一个子句，取当前月和前两个月的薪水累计，即可得到每个员工每个月最近三个月的累计薪
Leetcode No.181 超过经理收入的员工（表自身联结）
qq_32755875的博客
07-04 118
难度：简单 Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。 +----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam
SQL LeetCode刷题系列（二）——超过经理收入的员工
liuzuoping的博客
10-06 1059
文章目录题目描述题解 题目描述 Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。 题解 SELECT E1.Name AS Employee FROM Employe...
leetcode(mysql) 收入超过经理的员工
weixin_38477351的博客
04-22 789
收入超过经理的员工 1、题目： Employee表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。 +----+-------+--------+-----------+ | Id | Name | Salary | M...
【LeetCode刷题（数据库）】181. 超过经理收入的员工
qq_33197518的博客
10-27 285
Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。 ±—±------±-------±----------+ | Id | Name | Salary | ManagerId | ±—±------±-------±----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL
sql练习题--给定数字的频率查询中位数
加油小白的博客
05-10 226
Numbers 表保存数字的值及其频率。 +----------+-------------+ | Number | Frequency | +----------+-------------| | 0 | 7 | | 1 | 1 | | 2 | 3 | | 3 | 1 | +----------+-------------+ 在此表中，数字为 0, 0, 0,
LeetCode-----571. 给定数字的频率查询中位数
Samooyou的博客
07-23 205
题目技术：1、获取保留小数的方法：round(xxx,n),对于xxx的值保留n位小数，并且是根据四舍五入保留，（eg：select round(3.1415926,4) ; 得到 值为 3.1416）;（同时round()能够搭配其他的函数使用保留小数，例如本题）； 2、sum()聚合函数搭配over()开窗函数使用，(开窗函数并不会改变表的字段结构，只会增加一个新的字段) 用法： sum(C) over (partition by A order by B rows between 开...
leetcode高阶sql
最新发布
11-10
LeetCode是一个在线的技术面试平台，提供了许多算法和数据结构的题目，同时也提供了许多SQL的题目。下面是一些LeetCode高阶SQL的例子： 1. 部门工资最高的员工 ```sql SELECT Department.Name as Department, ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值