leetcode数据库题集持续更新中

Combine Two Tables

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

//考察左联接

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

答案1 

SELECT a.name FROM Employee a, Employee b WHERE a.ManagerId = b.Id AND a.Salary > b.Salary;

答案2  使用外连接
SELECT a.name FROM Employee a left join Employee b on a.ManagerId = b.Id where a.Salary > b.Salary;

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

select Email from Person p group by Email having count(*) > 1;
//使用group by 以及 having ,count

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

//使用外连接768 ms
select c.Name from Customers c left join Orders o on c.Id=o.CustomerId where o.CustomerId is null;
//使用not in+子查询828 ms
SELECT Name FROM Customers c WHERE c.Id NOT IN (SELECT CustomerId FROM Orders o);
//使用not exists+子查询718 ms
SELECT Name FROM Customers c WHERE NOT EXISTS (SELECT CustomerId FROM Orders o WHERE o.CustomerId = c.id);

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

SELECT a.Id FROM Weather a INNER JOIN Weather b ON TO_DAYS(a.Date) = TO_DAYS(b.Date)+1 WHERE a.Temperature > b.Temperature;

记得使用TO_DAYS 不然最有一个CASE过不了。。

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 second highest salary is 200. If there is no second highest salary, then the query should return null.
注意两个特例 一个只有一个数据行，还有一种情况是有两行数据，但工资相等，这样也要返回null 所有要用group by

SELECT(SELECT Salary FROM Employee GROUP BY Salary ORDER BY Salary desc limit 1,1);

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

DELETE FROM Person WHERE Id NOT IN (SELECT Email FROM Person GROUP BY Email ORDER BY Id);//运行错误 在MySQL中，禁止在FROM子句中指定被更新的目标表

DELETE a FROM Person a INNER JOIN Person b WHERE a.Email=b.Email and a.id>b.id;

此时的delete语句需要在from前面写明a;

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

SELECT    d.Name AS Department, t.Name AS Employee, t.Salary AS Salary FROM (
SELECT DepartmentId,Salary,Name,        
              @rank := IF(DepartmentId != @prevDeptId,1,IF(@prevSalary != Salary,@rank+1,@rank)) AS Rank,
              @prevDeptId := DepartmentId AS prevDeptId,
              @prevSalary := Salary AS prevSalary
FROM Employee , (SELECT @rank :=0, @prevDepId := NULL, @prevSalary := NULL) r ORDER BY DepartmentId ASC,Salary DESC
) t INNER JOIN Department d ON t.DepartmentId=d.id WHERE t.rank<=3;

   
   

    
    
Department Highest Salary
 
    
    Total Accepted: 1965 
    
    Total Submissions: 10642
    
    My Submissions
   
   
   
   

    
    

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

SELECT    d.Name AS Department, t.Name AS Employee, t.Salary AS Salary FROM (

SELECT DepartmentId,

       Salary,

       Name,        

        @rank := IF(DepartmentId != @prevDeptId,1,

                    IF(@prevSalary != Salary,@rank+1,@rank)) AS Rank,
 //该句当Salary 为0时候，mysql会判断为false,故该查询存在case过不了                            

              @prevDeptId := DepartmentId AS prevDeptId,

              @prevSalary := Salary AS prevSalary

    FROM Employee e, (SELECT @rank :=0, @prevDepId := NULL, @prevSalary := NULL) r 

    ORDER BY DepartmentId ASC,Salary DESC

) t INNER JOIN Department d ON t.DepartmentId=d.Id WHERE t.rank=1;

正确的：

SELECT d.NAME AS Department, t.NAME AS Employee, Salary FROM (
  SELECT    DepartmentId,
            NAME,
            Salary, 
            @rank := IF(@prevDeptId != DepartmentId, 1, 
                         IF(@prevSalary = Salary, @rank, @rank + 1) ) AS Rank,
            @prevDeptId := DepartmentId AS prevDeptId,
            @prevSalary := Salary AS prevSalary
  FROM      Employee e, (SELECT @rank := 0, @prevDeptId := NULL, @prevSalary := NULL) r
  ORDER BY  DepartmentId ASC, Salary DESC
) t INNER JOIN Department d ON t.DepartmentId = d.ID
 WHERE t.rank =1

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

select DISTINCT(Num) as ConsecutiveNums from (
    select Num,Id,
           @times := IF(@preNum = Num,@times+1,1) as times,
           @preNum := Num
    from Logs L,(select @times:=0,@preNum:=NULL) r order by L.ID 
) t where t.times =3; 
//注意加入distinct

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

select Score,Rank from (
    select Id,
    Score,
    @Rank := IF(@preNum = Score,@Rank,@Rank+1) as Rank,
    @preNum := Score 
    from Scores s,(select @preNum := NULL,@Rank := 0) r order by Score desc 
) t;

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 n^th highest salary where n = 2 is 200. If there is no n^th highest salary, then the query should return null.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct(Salary) from Employee order by Salary desc limit M,1
  );
END