LeetCode之MySQL题解

leetcode_175_Combine Two Tables

Combine Two Tables

题：

Table: Person

<span style="font-size:18px;">+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId is the primary key column for this table.</span>

Table: Address

<span style="font-size:18px;">+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId is the primary key column for this table.</span>

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:

<span style="font-size:18px;">FirstName, LastName, City, State</span>

解：

<span style="font-size:18px;">select p.FirstName,p.LastName,a.City,a.State from Person p left outer join Address a using(PersonId);</span>

leetcode_176_Second Highest Salary

Second Highest Salary

题：

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

<span style="font-size:18px;">+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+</span>

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

解：

1.建表；

2.插入数据；

3.使用limit选取第二大工资；

4.使用双重select max(Salary)来选取第二大工资；

leetcode_177_Nth Highest Salary

Nth Highest Salary

题：

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

<span style="font-size:18px;">+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+</span>

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.

解：

<span style="font-size:18px;">CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare N1 int;
set N1=N-1;
  RETURN (
      # Write your MySQL query statement below.
      #select distinct Salary from Employee order by Salary desc limit N1,1
      select Salary from (select distinct Salary from Employee) Ta order by Salary desc limit N1,1
  );
END</span>

注意：上述代码中两种解题代码没有本质区别。

leetcode_178_Rank Scores

leetcode_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):

<span style="font-size:18px;">+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+</span>

解：

<span style="font-size:18px;">select ss1.Score , count(ss2.Score) as Rank from
(select * from Scores)ss1 left outer join 
(select distinct Score from Scores)ss2
on(ss1.Score<=ss2.Score) group by ss1.Id order by ss1.Score desc;</span>

leetcode_180_Consecutive Numbers

leetcode_180_Consecutive Numbers

题：

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

<span style="font-size:18px;">+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+</span>

For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

解：

<span style="font-size:18px;"># Write your MySQL query statement below
select distinct T1.Num as ConsecutiveNums from 
Logs T1,Logs T2,Logs T3 where
T1.Id+1=T2.Id and T1.Id+2=T3.Id and T1.Num=T2.Num and T1.Num=T3.Num;</span>

leetcode_181_ Employees Earning More Than Their Managers

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.

<span style="font-size:18px;">+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+</span>

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.

<span style="font-size:18px;"><span style="font-size:18px;">+----------+
| Employee |
+----------+
| Joe      |
+----------+</span></span>

解：

<span style="font-size:18px;"><span style="font-size:18px;"># Write your MySQL query statement below
select e1.Name as Employee from Employee e1 inner join
Employee e2 on e1.ManagerId=e2.Id and e1.Salary>e2.Salary;</span></span>

leetcode_182_Duplicate Emails

leetcode_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:

<span style="font-size:18px;">+---------+
| Email   |
+---------+
| a@b.com |
+---------+</span>

Note: All emails are in lowercase.

解：

<span style="font-size:18px;"># Write your MySQL query statement below
select Email from Person group by Email having count(8)>1;</span>

leetcode_183_Customers Who Never Order

leetcode_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 .

<span style="font-size:18px;">+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+</span>

Using the above tables as example, return the following:

<span style="font-size:18px;">+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+</span>

解：

<span style="font-size:18px;"># Write your MySQL query statement below
select Name as Customers from Customers left join Orders on Customers.Id=Orders.CustomerId where Orders.Id is Null;</span>

leetcode_184_Department Highest Salary

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

解：

<span style="font-size:18px;"># Write your MySQL query statement below
select D.Name AS Department, E1.Name as Employee, Temp.Salary from Employee E1
join (select DepartmentId, MAX(Salary) as Salary from Employee group by DepartmentId) Temp
using(DepartmentId, Salary)join Department D on D.Id = Temp.DepartmentId;</span>

leetcode_185_Department Top Three Salaries

leetcode_185_Department Top Three Salaries

题：

The Employee table holds all employees. Every employee has an Id, 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            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
+----+-------+--------+--------------+

The  Department  table holds all departments of the company.

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+

解：

<span style="font-size:18px;"># Write your MySQL query statement below
SELECT D.Name AS Department, E.Name AS Employee, E.Salary AS Salary 
FROM Employee E, Department D
WHERE (SELECT COUNT(DISTINCT(Salary)) FROM Employee 
       WHERE DepartmentId = E.DepartmentId AND Salary > E.Salary) < 3
AND E.DepartmentId = D.Id 
ORDER BY E.DepartmentId, E.Salary DESC;</span>

leetcode_196_Delete Duplicate Emails

leetcode_196_Delete Duplicate Emails

题：

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id is the primary key column for this table.

For example, after running your query, the above  Person  table should have the following rows:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

解：

<span style="font-size:18px;"># Write your MySQL query statement below
delete p1 from Person p1,Person p2 
where p1.Email=p2.Email and p1.Id>p2.Id;</span>

删除：

结果：

leetcode_197_Rising Temperature

Rising Temperature

题：

Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.

+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
|       1 | 2015-01-01 |               10 |
|       2 | 2015-01-02 |               25 |
|       3 | 2015-01-03 |               20 |
|       4 | 2015-01-04 |               30 |
+---------+------------+------------------+

For example, return the following Ids for the above Weather table:

+----+
| Id |
+----+
|  2 |
|  4 |
+----+

解：

<span style="font-size:18px;"># Write your MySQL query statement below
select w2.Id from Weather w1 join Weather w2 
where to_days(w1.Date)+1=to_days(w2.Date) and w2.Temperature>w1.Temperature;</span>