本文从LeetCode及Memogrocery整理而来，并非原创。其中180，184来自LeetCode，其他的来自Memogrocery（https://nifannn.github.io）
Table of Contents
178.Rank Scores180. Consecutive Numbers184. Department Highest Salary177.Nth Highest Salary571.Second Degree Follower
178.Rank Scores
Problem
Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
For example, given the above Scores
table, your query should generate the following report (order by highest score):
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
Analysis
For a score, its rank is the number of scores in the Scores table without duplicates that is larger than or equal to the score. Take above table as example, there are six scores, 3.50, 3.65, 4.00, 3.85, 4.00 and 3.65. The distinct scores are 3.50, 3.65, 3.85 and 4.00. For a score of 4.00, only 4.00 ≥ 4.00, so the rank is 1. Similarly, for a score of 3.65, 4.00 ≥ 3.65, 3.85 ≥ 3.65, 3.65 ≥ 3.65, therefore, the rank is 3.
First, we need to get all distinct scores:
SELECT DISTINCT Score FROM Scores;
Assuming that we call the table of distinct scores t, next we can fully join t and Scores and filter scores in t that is larger than or equal to each score in Scores:
SELECT s.Score, t.Score FROM
(SELECT DISTINCT Score FROM Scores) AS t, Scores AS s
WHERE s.Score <= t.Score;
For each score in Scores, count how many scores in t are larger than or equal to it:
SELECT s.Score, COUNT(t.Score) AS Rank FROM
(SELECT DISTINCT Score FROM Scores) AS t, Scores AS s
WHERE s.Score <= t.Score
GROUP BY s.Id, s.Score;
Finally, sort by score in descending order:
SELECT s.Score, COUNT(t.Score) AS Rank FROM
(SELECT DISTINCT Score FROM Scores) AS t, Scores AS s
WHERE s.Score <= t.Score
GROUP BY s.Id, s.Score
ORDER BY s.Score DESC;
Solution
SELECT s.Score, COUNT(t.Score) AS Rank FROM
(SELECT DISTINCT Score FROM Scores) AS t, Scores AS s
WHERE s.Score <= t.Score
GROUP BY s.Id, s.Score
ORDER BY s.Score DESC;
180. Consecutive Numbers
Write a SQL query to find all numbers that appear at least three times consecutively.
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
For example, given the above Logs
table, 1 is the only number that appears consecutively for at least three times.
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
Solution
Approach: Using DISTINCT
and WHERE
clause
Algorithm
Consecutive appearing means the Id of the Num are next to each others. Since this problem asks for numbers appearing at least three times consecutively, we can use 3 aliases for this table Logs, and then check whether 3 consecutive numbers are all the same.
SELECT *
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 | Num | Id | Num | Id | Num |
|---|---|---|---|---|---|
| 1 | 1 | 2 | 1 | 3 | 1 |
Note: The first two columns are from l1, then the next two are from l2, and the last two are from l3.
Then we can select any Num column from the above table to get the target data. However, we need to add a keyword DISTINCT
because it will display a duplicated number if one number appears more than 3 times consecutively.
MySQL
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
;
184. Department Highest Salary
The ·Employee· table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+
The Department
table holds all departments of the company.
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
Solution
Approach: Using JOIN
and IN
clause
Algorithm
Since the Employee
table contains the Salary and DepartmentId information, we can query the highest salary in a department.
SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId;
Note: There might be multiple employees having the same highest salary, so it is safe not to include the employee name information in this query.
| DepartmentId | MAX(Salary) |
|---|---|
| 1 | 90000 |
| 2 | 80000 |
Then, we can join table Employee
and Department
, and query the (DepartmentId, Salary) are in the temp table using IN statement as below.
MySQL
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
;
| Department | Employee | Salary |
|---|---|---|
| Sales | Henry | 80000 |
| IT | Max | 90000 |
177.Nth Highest Salary
Problem
Write a SQL query to get the nth highest salary from the Employee
table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee
table, the nth highest salary where n = 2 is 200. If there is no nth highest salary, then the query should return null.
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
Analysis
We can solve this problem with LIMIT
and OFFSET
. We can sort by salary in descending order first, then skip n-1 numbers and output the first number.
Solution
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N=N-1;
RETURN (
# Write your MySQL query statement below.
SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET N
);
END
571.Second Degree Follower
Problem
In facebook, there is a follow
table with two columns: followee, follower.
Please write a sql query to get the amount of each follower’s follower if he/she has one.
For example:
+-------------+------------+
| followee | follower |
+-------------+------------+
| A | B |
| B | C |
| B | D |
| D | E |
+-------------+------------+
should output:
+-------------+------------+
| follower | num |
+-------------+------------+
| B | 2 |
| D | 1 |
+-------------+------------+
Explaination:
Both B and D exist in the follower list, when as a followee, B’s follower is C and D, and D’s follower is E. A does not exist in follower list.
Note:
Followee would not follow himself/herself in all cases.
Please display the result in follower’s alphabet order.
Analysis
Join 2 follow tables. Let followee of the second table be equal to the follower of the first table so that follower in the second table is follower’s follower in the first table. Then group by follower of the first table and count number of follower in the second table for each follower in the first table. Finally exclude duplicates and sort by follower of the first table alphabetically.
Solution
SELECT f1.follower, COUNT(DISTINCT f2.follower) AS num FROM
follow AS f1 JOIN follow AS f2 ON f1.follower = f2.followee
GROUP BY f1.follower ORDER BY f1.follower;
以上是今天关于LeetCode的题目及解答。
下面是我个人微信号，欢迎关注。
