177. Nth Highest Salary

Write a SQL query to get the nth highest salary from the Employee table.
找到第n大的工资

+----+--------+
| 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.
没有第n大返回null
Answer:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N = N - 1;
  RETURN (
      # Write your MySQL query statement below.
      select Salary from Employee group by Salary order by Salary desc limit N,1
  );
END
#原本我想到的就是这个解法,但是以为不能在外部写sql语句,就想了好久

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

Answer:

# Write your MySQL query statement below
SELECT Department.Name AS Department,
         a.Name AS Employee,
         a.Salary AS Salary
FROM Employee AS a
INNER JOIN Department
    ON a.DepartmentId = Department.Id
WHERE a.Salary = 
    (SELECT max(Salary)
    FROM Employee
    WHERE a.DepartmentId = DepartmentId);
#子句就找出了不同部门的最大工资

262. Trips and Users

The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id |        Status      |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 1  |     1     |    10     |    1    |     completed      |2013-10-01|
| 2  |     2     |    11     |    1    | cancelled_by_driver|2013-10-01|
| 3  |     3     |    12     |    6    |     completed      |2013-10-01|
| 4  |     4     |    13     |    6    | cancelled_by_client|2013-10-01|
| 5  |     1     |    10     |    1    |     completed      |2013-10-02|
| 6  |     2     |    11     |    6    |     completed      |2013-10-02|
| 7  |     3     |    12     |    6    |     completed      |2013-10-02|
| 8  |     2     |    12     |    12   |     completed      |2013-10-03|
| 9  |     3     |    10     |    12   |     completed      |2013-10-03| 
| 10 |     4     |    13     |    12   | cancelled_by_driver|2013-10-03|
+----+-----------+-----------+---------+--------------------+----------+

The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).

+----------+--------+--------+
| Users_Id | Banned |  Role  |
+----------+--------+--------+
|    1     |   No   | client |
|    2     |   Yes  | client |
|    3     |   No   | client |
|    4     |   No   | client |
|    10    |   No   | driver |
|    11    |   No   | driver |
|    12    |   No   | driver |
|    13    |   No   | driver |
+----------+--------+--------+

Write a SQL query to find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.
找到在Oct 1, 2013到Oct 1, 2013未被ban的用户请求的取消率

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

Answer:

# Write your MySQL query statement below
SELECT Trips.Request_at AS Day,
    round(sum(if(status != 'completed', 1, 0)) / sum(1), 2) 'Cancellation Rate'
    FROM Trips
INNER JOIN Users
    ON Trips.Client_Id = Users.Users_Id
WHERE Users.Banned = 'No'
    and to_days('2013-10-01') <= to_days(Trips.Request_at)
    and to_days('2013-10-03') >= to_days(Trips.Request_at)
GROUP BY  Trips.Request_at;
#开始以为只有用户取消的才算,司机取消的不算
#分母每有一条就自增,而分子status不为completed才增加

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

Answer:

# Write your MySQL query statement below
SELECT Department.Name AS Department,
         e1.Name AS Employee,
         e1.Salary AS Salary
FROM Employee AS e1
INNER JOIN Department
    ON e1.DepartmentId = Department.Id
WHERE 
    (SELECT count(distinct Salary)
    FROM Employee AS e2
    WHERE e2.Salary > e1.Salary
            AND e1.DepartmentId = e2.DepartmentId) < 3;
#子句找出三个最大值,用count(distinct Salary)来控制查找数量

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 second highest salary is 200. If there is no second highest salary, then the query should return null.
Answer:

# Write your MySQL query statement below
SELECT max(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < 
    (SELECT max(Salary)
    FROM Employee);
#
#SecondHighestSalary是提交后才知道的要求
#先选出比最大值小的,再选出最大的就是第二大.

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.
删除重复的email,只保留最小的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  |
+----+------------------+

Answer:

# Write your MySQL query statement below
delete p
FROM Person AS p, Person AS q
WHERE p.Id > q.Id
        AND p.Email = q.Email;
#
#删除Email值相等Id更大的,剩下即为最小

0x02 Medium

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

Answer:

# Write your MySQL query statement below
SELECT a.Score,(SELECT count(distinct Score)
    FROM Scores
    WHERE Score >= a.Score) AS Rank
FROM Scores AS a
ORDER BY  a.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.

# Write your MySQL query statement below
SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM Logs AS l1, Logs AS l2, Logs AS l3
WHERE (l1.Id = l2.Id-1
        AND l1.Num = l2.Num
        AND l2.Id = l3.Id-1
        AND l2.Num = l3.Num)
#判断这个数的后两位是否与他的值相等

0x00 起因

最近深觉基础不够扎实,借助leetcode巩固一下自己sql语句的编写。

0x01 Easy

182. Duplicate Emails

Write a SQL query to find all duplicate emails in a table named Person.
找出重复的emails

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

Note: All emails are in lowercase.
Answer:

# Write your MySQL query statement below
select Email from Person group by Email having count(*) > 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      |
+----------+

Answer:

# Write your MySQL query statement below
select e1.Name as Employee from Employee as e1, Employee as e2 where e1.ManagerId = e2.Id and e1.Salary > e2.Salary
#
# 很直观,选出ManagerId=Id,e1.Salary>e2.Salary

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

# Write your MySQL query statement below
select Person.FirstName, Person.LastName, Address.City, Address.State from Person LEFT join Address ON Person.PersonId=Address.PersonId;
#
# 无论是否有信息并且跨表很明显是需要使用join,注意区分left,right和inner。

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

Answer:

# Write your MySQL query statement below
select Name as Customers from Customers where Id not in (select CustomerId from Orders);
#
# 选出第二个表的Id,再选出不在第二个表中Id的Id

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

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

Answer:

# Write your MySQL query statement below
select w1.Id from Weather as w1, Weather as w2 where w1.Temperature > w2.Temperature and TO_DAYS(w1.Date) = TO_DAYS(w2.Date)+1;
#
# 难点在于TO_DAYS(),能返回一个天数.昨天就比今天小一.