MySQL(LC算法)

MySQL 知识图谱

Join 连接大全

175. Combine Two Tables

Table: Person

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+

PersonId is the primary key column for this table.

Table: Address
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+

AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

select p.FirstName,p.LastName,a.City,a.State
from person p left join address a
on p.personid = a.personid;

1）注意审题： 题目说：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息： FirstName, LastName, City, State 也就是说，地址信息（City, State）的查询结果是Null是OK的。但是，姓名（FirstName, LastName）必须有。

2） 为啥不用Where？ 因为where的实质就是根据你给的条件（personID相等），选取两表的公共部分。但是，因为PERSON表不是所有人都有地址信息的，但是ADDRESS表只显示有地址信息的人，这样选取出来的就是有地址信息的人，漏掉了没有地址信息的人。所以大家注意，where的本质就是过滤。

3） 如何连接？应该用PERSON表左连接（left join）ADDRESS表，保留person表的所有信息

176. Second Highest Salary

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

• 要想获取第二高，需要排序，使用 order by（默认是升序 asc，即从小到大），若想降序则使用关键字 desc
• 去重，如果有多个相同的数据，使用关键字 distinct 去重
• 判断临界输出，如果不存在第二高的薪水，查询应返回 null，使用 ifNull（查询，null）方法
• 起别名，使用关键字 as …
• 因为去了重，又按顺序排序，使用 limit（）方法，查询第二大的数据，即第二高的薪水，即 limit(1,1) （因为默认从0开始，所以第一个1是查询第二大的数，第二个1是表示往后显示多少条数据，这里只需要一条）

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

177. Nth Highest Salary

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                    |
+------------------------+

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      select ifnull((
        select distinct salary 
        from employee 
        order by salary desc 
        limit n,1),null) as getNthHighestSalary
  );
END

• 题目是 176.第二高的薪水 的变形，将查询第二名变成查询 第N名
• 别名中不能带参数，一开始看到测试用例表，使用的别名是getNthHighestSalary(2)，就用了getNthHighestSalary(N)做别名，一开始报错还不知道是哪，后面删去变量即可
• limit()方法中不能参与运算，因为索引从0开始，所以要 - 1，最好在外面就设定好 set N = N - 1

需要注意的点就是：

limit是不可以做运算的

178. Rank Scores

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       |
+-------+---------+

Important Note: For MySQL solutions, to escape reserved words used as column names, you can use an apostrophe before and after the keyword. For example Rank.

---

思路：

1. 从两张相同的表scores分别命名为s1，s2。
2. s1中的score与s2中的score比较大小。意思是在输出s1.score的前提下，有多少个s2.score大于等于它。比如当s1.salary=3.65的时候，s2.salary中[4.00,4.00,3.85,3.65,3.65]有5个成绩大于等于他，但是利用count(distinct s2.score)去重可得s1.salary3.65的rank为3
3. group by s1.id 不然的话只会有一条数据
4. 最后根据s1.score排序desc

select s1.score,count(distinct s2.score) as rank
from scores as s1,scores as s2
where s1.score<=s2.score
group by s1.id
order by s1.score desc;

---

最后的结果包含两个部分，第一部分是降序排列的分数，第二部分是每个分数对应的排名。

第一部分不难写：

select a.Score as Score
from Scores 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 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 a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC

180. Consecutive Numbers

Table: Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id is the primary key for this table.

Write an SQL query to find all numbers that appear at least three times consecutively.

Return the result table in any order.

The query result format is in the following example:

Logs table:
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

Result table:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
1 is the only number that appears consecutively for at least three times.

方法一：

• 根据num列相等对logs表进行自连接
• 根据l1.id进行聚合分组
• 若分组中满足l1.id与l2.id的差在0到2之间的l2.id大于2个，则说明该num连续出现3次

select distinct l1.num consecutivenums
from logs l1 join logs l2
on l1.num = l2.num
group by l1.id
having sum(l1.id - l2.id between 0 and 2) > 2

方法二：

• 根据num列相等，以及id之差的范围在0到2之间，对logs表进行自连接
• 根据l1.id进行聚合分组
• 统计分组中l2.id的数目，若大于2即满足题意

select distinct l1.num consecutivenums
from logs l1 join logs l2
on l1.num = l2.num and l1.id - l2.id between 0 and 2
group by l1.id
having count(l2.id) > 2

方法三：

select distinct a.num as ConsecutiveNums
from Logs as a, Logs as b, Logs as c
where a.num = b.num and b.num=c.num and a.id = b.id-1 and b.id = c.id-1

181. Employees Earning More Than Their Managers

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+
| Employee |
+----------+
| Joe      |
+----------+

答案

select e1.name as `Employee` from Employee e1, Employee e2 
where e1.ManagerId = e2.id
and e1.salary > e2.salary

182. Duplicate Emails

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

For example, your query should return the following for the above table:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

-- 解法1
select email from person group by email having count(email)>1

-- 解法2
select email from (select count(1) as t,email from person group by email)r  where r.t>1;

-- 解法3
select distinct(p1.Email) from Person p1  
join Person  p2 on p1.Email = p2.Email AND p1.Id!=p2.Id

183. Customers Who Never Order

Suppose that a website contains two tables, the Customers table and the Orders table. Write a SQL query to find all customers who never order anything.

Table: Customers.

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Table: Orders.

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

Using the above tables as example, return the following:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

select c.name as `Customers` 
from Customers c left join Orders o
on c.id = o.customerid
where o.customerid is null

SELECT Name 'Customers'
FROM Customers
WHERE Id NOT IN(
    SELECT CustomerId 
    FROM Orders
)