LeetCode 数据库解题汇总 MySql版

175. Combine Two Tables [Easy]

• Description

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
复制代码

• SQL Schema

DROP TABLE IF EXISTS Person;
CREATE TABLE Person(
  PERSONID INT,
  FIRSTNAME VARCHAR(50),
  LASTNAME VARCHAR(50),
  PRIMARY KEY (PERSONID)
);

DROP TABLE IF EXISTS Address;
CREATE TABLE Address(
  ADDRESSID INT,
  PERSONID INT,
  CITY VARCHAR(50),
  STATE VARCHAR(50),
  PRIMARY KEY (ADDRESSID)
);

insert into Person values (1, 'F1', 'L1'), (2, 'F2', 'L2'), (3, 'F3', 'L3');
insert into Address values (1, 1, 'SZ', 'FT'), (3, 2, 'GZ', 'PY');
复制代码

• Solution
• left join

select FirstName, LastName, City, State
from Person left join Address on Person.PersonId = Address.PersonId
复制代码

176. Second Highest Salary [Easy]

• Description

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                 |
+---------------------+
复制代码

• SQL Schema

DROP table if exists Employee;
create table Employee(
  Id int,
  Salary int,
  primary key (Id)
);

insert into Employee values (1, 100), (2, 200), (3, 300);
复制代码

• Solution
• 因为找不到时要返回null，所以外部再加一层select

select (select DISTINCT Salary from Employee order by Salary desc limit 1, 1) SecondHighestSalary
复制代码

177. Nth Highest Salary [Medium]

• Description

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                    |
+------------------------+
复制代码

• SQL Schema

DROP table if exists Employee;
create table Employee (
  Id     int,
  Salary int,
  primary key (Id)
);

insert into Employee
values (1, 100),
       (2, 200),
       (3, 300);
复制代码

• Solution

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N - 1;
  RETURN (
      SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N, 1
  );
END
复制代码

178. Rank Scores [Medium]

• Description

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    |
+-------+------+
复制代码

• SQL Schema

DROP table if exists Scores;
create table Scores (
  Id     int,
  Scores double,
  primary key (Id)
);

insert into Scores
values (1, 3.5),
       (2, 3.65),
       (3, 4.00),
       (4, 3.85),
       (5, 4.00),
       (6, 3.65);

复制代码

• Solution

select s1.Score, COUNT(DISTINCT s2.Score) Rank
from Scores s1
       inner join Scores s2 on s1.Score <= s2.Score
group by s1.Id
order by s1.Score desc
复制代码

180. Consecutive Numbers [Medium]

• Description

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               |
+-----------------+
复制代码

• SQL Schema

DROP table if exists Logs;
create table Logs (
  Id    int,
  Num int,
  primary key (Id)
);

insert into Logs
values (1, 1),
       (2, 1),
       (3, 1),
       (4, 2),
       (5, 1),
       (6, 2);
复制代码

• Solution
• 用3张表做连接，取id连续且num相等，然后注意需要对结果去重

解法1：
select distinct t1.Num as ConsecutiveNums
from Logs t1,
     Logs t2,
     Logs t3
where t1.id = t2.id - 1
  and t2.id = t3.id - 1
  and t1.num = t2.num
  and t2.num = t3.num

解法2：
select distinct t1.Num as ConsecutiveNums
from Logs t1
join Logs t2 on t1.id = t2.id - 1
join Logs t3 on t2.id = t3.id - 1
where t1.num = t2.num and t2.num = t3.num
复制代码

181. Employees Earning More Than Their Managers [Easy]

• Description

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      |
+----------+
复制代码

• SQL Schema

DROP table if exists Employee;
create table Employee (
  Id  int,
  Name varchar(20),
  Salary int,
  ManagerId int,
  primary key (Id)
);

insert into Employee
values (1, 'Joe', 700, 3),
       (2, 'Henry', 800, 4),
       (3, 'Sam', 600, NULL),
       (4, 'Max', 900, NUll);
复制代码

• Solution
• 很直观

select t1.name as Employee
from Employee t1 join Employee t2 on t1.ManagerId = t2.id
where t1.Salary > t2.Salary
复制代码

182. Duplicate Emails [Easy]

• Description

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

Note: All emails are in lowercase.
复制代码

• SQL Schema

DROP table if exists Person;
create table Person
(
    Id    int,
    Email varchar(20),
    primary key (Id)
);

insert into Person
values (1, 'a@b.com'),
       (2, 'c@d.com'),
       (3, 'a@b.com');
复制代码

• Solution

select email
from (
         select email, count(email) as times
         from Person
         group by email) t
where t.times > 1
复制代码

183. Customers Who Never Order [Easy]

• Description

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       |
+-----------+
复制代码

• SQL Schema

DROP table if exists Customers;
create table Customers
(
    Id    int,
    Name varchar(20),
    primary key (Id)
);

insert into Customers
values (1, 'Joe'),
       (2, 'Henry'),
       (3, 'Sam'),
       (4, 'Max');


DROP table if exists Orders;
create table Orders
(
    Id    int,
    CustomerId int,
    primary key (Id)
);

insert into Orders
values (1, 3),
       (2, 1);
复制代码

• Solution
• 左连接

select c.Name as Customers
from Customers c left join Orders o
    on c.id = o.CustomerId
where o.CustomerId is null
复制代码

184. Department Highest Salary [Medium]

• Description

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  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | 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, your SQL query should return the following rows (order of rows does not matter).

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

Explanation:

Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.
复制代码

• SQL Schema

DROP table if exists Employee;
create table Employee
(
    Id    int,
    Name varchar(20),
    Salary int,
    DepartmentId int ,
    primary key (Id)
);

insert into Employee
values (1, 'Joe', 70000, 1),
       (2, 'Jim', 90000, 1),
       (3, 'Henry', 80000, 2),
       (4, 'Sam', 60000, 2),
       (5, 'Max', 90000, 1);

DROP table if exists department;
create table Department
(
    Id int,
    Name varchar(20),
    primary key (Id)
);

insert into Department
values (1, "IT"),
       (2, "Sales");
复制代码

• Solution
• 先使用group by和max函数获取每个部门的最大薪资，然后在进行一次连接查询

select t.Name as Department, e1.Name as Employee, e1.Salary as Salary
from Employee e1
    join (
        select MAX(Salary) as Salary, e.DepartmentId, d.Name
        from Employee e join Department d on e.DepartmentId = d.Id
        group by DepartmentId) t
    on e1.DepartmentId = t.DepartmentId and e1.Salary = t.Salary
复制代码

185.Department Top Three Salaries [Hard]

• Description

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  |
+------------+----------+--------+
复制代码

• SQL Schema

DROP table if exists Employee;
create table Employee
(
    Id    int,
    Name varchar(20),
    Salary int,
    DepartmentId int ,
    primary key (Id)
);

insert into Employee
values (1, 'Joe', 70000, 1),
       (2, 'Henry', 80000, 2),
       (3, 'Sam', 60000, 2),
       (4, 'Max', 90000, 1),
       (5, 'Janet', 69000, 1),
       (6, 'Randy', 85000, 1);

DROP table if exists department;
create table Department
(
    Id int,
    Name varchar(20),
    primary key (Id)
);

insert into Department
values (1, "IT"),
       (2, "Sales");
复制代码

• Solution
• 内外两张Employee连接查询，查询出Employee表中同部门，比自己薪水高的数目小于3的记录，即是每个部门的前三

select d.Name as Department, e1.Name as Employee, e1.Salary
from Employee e1, Department d
where (select count(distinct e2.Salary)
        from Employee e2
        where e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary) < 3
and e1.DepartmentId = d.id
order by e1.DepartmentId desc, e1.Salary desc
复制代码

196. Delete Duplicate Emails [Easy]

• Description

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

Your output is the whole Person table after executing your sql. Use delete statement.
复制代码

• SQL Schema

DROP table if exists Person;
create table Person
(
    Id    int,
    Email varchar(50),
    primary key (Id)
);

insert into Person
values (1, 'joe@example.com'),
       (2, 'bob@example.com'),
       (3, 'joe@example.com');
复制代码

• Solution

连接：
delete p1
from Person p1,
     Person p2
where p1.Email = p2.Email
and p1.Id > p2.Id

子查询：注意内部多做了一次select查询，否则执行会报错
delete
from Person
where Id in (
          select id
          from (select distinct p2.id
                   from Person p1 join Person p2 on p1.Email = p2.Email and p1.Id < p2.Id) as m)
复制代码

197. Rising Temperature [Easy]

• Description

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) | RecordDate(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 |
+----+
复制代码

• SQL Schema

DROP table if exists Weather;
create table Weather
(
    Id    int,
    RecordDate Date,
    Temperature int,
    primary key (Id)
);

insert into Weather
values (1, '2015-01-01', 10),
       (2, '2015-01-02', 25),
       (3, '2015-01-03', 20),
       (4, '2015-01-04', 30);
复制代码

• Solution
• 日期间的差距天数使用Datediff函数

select w1.id
from Weather w1 join Weather w2
on Datediff(w1.RecordDate, w2.RecordDate) = 1
and w1.Temperature > w2.Temperature
复制代码

262. Trips and Users [Hard]

• Description

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

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+
Credits:
Special thanks to @cak1erlizhou for contributing this question, writing the problem description and adding part of the test cases.
复制代码

• SQL Schema

DROP table if exists Users;
create table Users
(
    Users_Id int,
    Banned   ENUM ('YES', 'NO'),
    Role     ENUM ('client', 'driver'),
    primary key (Users_Id)
);

insert into Users
values (1, 'No', 'client'),
       (2, 'Yes', 'client'),
       (3, 'No', 'client'),
       (4, 'No', 'client'),
       (10, 'No', 'driver'),
       (11, 'No', 'driver'),
       (12, 'No', 'driver'),
       (13, 'No', 'driver');

Drop table if exists Trips;
create table Trips(
  Id int,
  Client_id int references Users(Users_Id),
  Driver_Id int references Users(Users_Id),
  City_Id int,
  Status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'),
  Request_at date,
  primary key (Id)
);
insert into Trips
values (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');
复制代码

• Solution
• 外层查询出每日的行程数，round中查询出每日取消的行程数

select a.Request_at as Day, round((
    select count(*) from Trips as b left join Users as c on b.Client_Id = c.Users_Id
    where (b.Status = 'cancelled_by_client' or b.Status ='cancelled_by_driver')
        and c.Banned = 'No'
        and b.Request_at = a.Request_at)/count(a.Status),2) as "Cancellation Rate"
from Trips as a left join Users as d
on a.Client_Id = d.Users_Id
where d.Banned = 'No' and a.Request_at >= date("2013-10-01") and a.Request_at <= date("2013-10-03")
group by a.Request_at
order by a.Request_at
复制代码

595. Big Countries [Easy]

• Description

There is a table World
+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+
A country is big if it has an area of bigger than 3 million square km or a population of more than 25 million.

Write a SQL solution to output big countries' name, population and area.

For example, according to the above table, we should output:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+
复制代码

• SQL Schema

DROP table if exists World;
create table World
(
    name varchar(50),
    continent varchar(50),
    area integer,
    population integer,
    gpd integer,
    primary key (name)
);

insert into World
values ('Afghanistan', 'Asia', 652230, 25500100, 20343000),
       ('Albania', 'Europe', 28748, 2831741, 12960000),
       ('Algeria', 'Africa', 2381741, 37100000, 188681000),
       ('Andorra', 'Europe', 468, 78115, 3712000),
       ('Angola', 'Africa', 1246700, 20609294, 100990000);
复制代码

• Solution

select t.name, t.population, t.area
from World t
where t.population > 25000000 or t.area > 3000000
复制代码

596. Classes More Than 5 Students [Easy]

• Description

There is a table courses with columns: student and class

Please list out all classes which have more than or equal to 5 students.

For example, the table:

+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+
Should output:

+---------+
| class   |
+---------+
| Math    |
+---------+
 

Note:
The students should not be counted duplicate in each course.
复制代码

• SQL Schema

DROP table if exists courses;
create table courses
(
    student varchar(50),
    class   varchar(50)
);

insert into courses
values ('A', 'Math'),
       ('B', 'English'),
       ('C', 'Math'),
       ('D', 'Biology'),
       ('E', 'Math'),
       ('F', 'Computer'),
       ('G', 'Math'),
       ('H', 'Math'),
       ('I', 'Math');
复制代码

• Solution
• 注意课程和学生对应关系可能有重复，需要先进行一次distinct

select t2.class
from (select t1.class, count(t1.class) as count
      from (select distinct class, student from courses) t1
      group by t1.class) t2
where t2.count >= 5
复制代码

620. Not Boring Movies [Easy]

• Description

X city opened a new cinema, many people would like to go to this cinema. The cinema also gives out a poster indicating the movies’ ratings and descriptions.
Please write a SQL query to output movies with an odd numbered ID and a description that is not 'boring'. Order the result by rating.

For example, table cinema:
+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
+---------+-----------+--------------+-----------+
For the example above, the output should be:
+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+
复制代码

• SQL Schema

DROP table if exists cinema;
create table cinema
(
    id          int,
    movie       varchar(50),
    description varchar(100),
    rating      double,
    primary key (id)
);

insert into cinema
values (1, 'War', 'great3D', 8.9),
       (2, 'Science', 'fiction', 8.5),
       (3, 'irish', 'boring', 6.2),
       (4, 'Icesong', 'Fantacy', 8.6),
       (5, 'Housecard', 'Interesting', 9.1);
复制代码

• Solution

select *
from cinema t
where t.description != 'boring'
    and t.id MOD 2 = 1
order by t.rating desc
复制代码

601. Human Traffic of Stadium [Hard]

• Description

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, visit_date, people

Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).

For example, the table stadium:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+
For the sample data above, the output is:

+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+
Note:
Each day only have one row record, and the dates are increasing with id increasing.
复制代码

• SQL Schema

DROP table if exists stadium;
create table stadium
(
    id      int,
    visit_date DATE,
    people int,
    primary key (id)
);

insert into stadium
values (1, '2017-01-01', 10),
       (2, '2017-01-02', 109),
       (3, '2017-01-03', 150),
       (4, '2017-01-04', 99),
       (5, '2017-01-05', 145),
       (6, '2017-01-06', 1455),
       (7, '2017-01-07', 199),
       (8, '2017-01-08', 188);
复制代码

• Solution
• 3张表做连接，找出日期相邻的3天，且每天的人流量均大于等于100，最后做排序

select distinct s1.*
from stadium s1,
     stadium s2,
     stadium s3
where s1.people >= 100 and s2.people >= 100 and s3.people >= 100
    AND (
        (s1.id + 1 = s2.id and s1.id + 2 = s3.id)
        or
        (s1.id - 1 = s2.id and s1.id + 1 = s3.id)
        or
        (s1.id - 2 = s2.id and s1.id - 1 = s3.id)
    )
order by s1.id asc
复制代码

626. Exchange Seats [Medium]

• Description

Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids.
The column id is continuous increment.
Mary wants to change seats for the adjacent students.
Can you write a SQL query to output the result for Mary?
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+
For the sample input, the output is:
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+
Note:
If the number of students is odd, there is no need to change the last one's seat.
复制代码

• SQL Schema

DROP table if exists seat;
create table seat
(
    id      int,
    student varchar(50),
    primary key (id)
);

insert into seat
values (1, 'Abbot'),
       (2, 'Doris'),
       (3, 'Emerson'),
       (4, 'Green'),
       (5, 'Jeames');
复制代码

• Solution
• 使用union，偶数的id-1，奇数的id+1，排除最后1个奇数id，union后再加上最后1个奇数id排序

select s1.id-1 as id, s1.student from seat s1
where s1.id mod 2 = 0 union
select s2.id+1 as id, s2.student from seat s2
where s2.id mod 2 = 1 and s2.id != (select max(s3.id) from seat s3) union
select s4.id, s4.student from seat s4
where s4.id mod 2 = 1 and  s4.id = (select max(s5.id) from seat s5)
order by id asc
复制代码

627. Swap Salary [Easy]

• Description

Given a table salary, such as the one below, that has m=male and f=female values. Swap all f and m values (i.e., change all f values to m and vice versa) with a single update statement and no intermediate temp table.
Note that you must write a single update statement, DO NOT write any select statement for this problem.
Example:

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |
After running your update statement, the above salary table should have the following rows:
| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |
复制代码

• SQL Schema

DROP table if exists salary;
create table salary
(
    id      int,
    name varchar(1),
    sex varchar(1),
    salary int,
    primary key (id)
);

insert into salary
values (1, 'A', 'm', 2500),
       (2, 'B', 'f', 1500),
       (3, 'C', 'm', 5500),
       (4, 'D', 'f', 500);
复制代码

• Solution
• 了解char和ascii函数以及^异或，字符异或自身为0,0异或其他字符得其他字符

update salary
set sex = CHAR( ASCII(sex) ^ ASCII('f') ^ ASCII('m'))
复制代码

转载于:https://juejin.im/post/5cbc850751882532bf1a2286