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)
记录每组的总数量;ceiling()
上取整函数,这样对于all为奇数来说不受影响。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}集合即可。
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)
表示在全表上累加从第一行到当前行。
这样计算出的中位数可能有两个,所以取平均值。
表: 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 | +------+
注意:
提交答案:
# 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
选出所有 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条件判断。
从 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)的数量;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
Employee
里(Id, Month)
不在 第一步的结果里即可,In连接符可以用多字段哦where (Id, Month) NOT IN (subquery)
3️⃣之前看over函数的时候没有注意还有条件range
和rows
,解释一下:
什么意思呢?对应本道题而言:
使用range就是去计算order by Month
里Month的加减法,也就是计算7月、6月、5月,不会管表里是不是真的有6月和5月;
使用rows是去每个Id下按月份排好序的前面三个,比如记录里7月之前是4月和三月,就计算这三个月。
发现有些题真不是看一看就能对的!真的要自己做一遍!
以上是leetcode7道sql题的解答,如有考虑错误或者解法问题,请指正。