leetcode sql的免费试题

目录

Mysql

176. 第二高的薪水

177. 第N高的薪水

178. 分数排名

180. 连续出现的数字

181. 超过经理收入的员工

182. 查找重复的电子邮箱

183. 从不订购的客户

184. 部门工资最高的员工

185. 部门工资前三高的所有员工

196. 删除重复的电子邮箱

197. 上升的温度

595. 大的国家

596. 超过5名学生的课

601. 体育馆的人流量

620. 有趣的电影

626. 换座位

627. 变更性别

---

Mysql

 

176. 第二高的薪水

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

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

解法一：利用 limit 进行限制 此方法可适用于求第N高的薪水，且数据越复杂，速度优势越明显

select(select distinct(Salary)  from Employee  order by Salary desc limit  1 ,1)
 as SecondHighestSalary ;
或者
select IFNULL((select distinct(Salary)  from Employee  order by Salary desc 
limit  1 ,1),null)as SecondHighestSalary ;

解法二：去掉最高薪水后，再取最高薪水；

SELECT max(Salary) SecondHighestSalary
 FROM Employee  
where Salary not in (select max(Salary) from Employee ); 
或者
SELECT max(Salary) SecondHighestSalary
 FROM Employee  
where Salary != (select max(Salary) from Employee ); 
或者
SELECT MAX(Salary) as SecondHighestSalary 
FROM employee 
WHERE Salary<(SELECT MAX(Salary) FROM employee);

解法二：使用开窗函数dense_rank()对其对薪水进行降序排号，选择序号为2的即可，注意只输出一个薪水值,此时薪水值都一样可以使用distinct min ,max选一个都行

select (select distinct wp.Salary from (select *,dense_rank() over
 (order by Salary desc) ranks from Employee ) wp where wp.ranks='2') 
as SecondHighestSalary;
或者
select IFNULL((select distinct (wp.Salary) from 
(select *,dense_rank() over (order by Salary desc)
 ranks from Employee ) wp where wp.ranks='2'),null) 
as SecondHighestSalary;
或者
select (select min(wp.Salary) from (select *,dense_rank() 
over (order by Salary desc) 
ranks from Employee ) wp where wp.ranks='2')
 as SecondHighestSalary;
或者
select IFNULL((select min(wp.Salary) from (select *,dense_rank()
 over (order by Salary desc) ranks from Employee ) wp where wp.ranks='2'),null) 
as SecondHighestSalary;
或者
select (select max(wp.Salary) from (select *,dense_rank() over 
(order by Salary desc) 
ranks from Employee ) wp where wp.ranks='2') 
as SecondHighestSalary;
或者
select IFNULL((select min(wp.Salary) from (select *,dense_rank() over
 (order by Salary desc) ranks from Employee ) wp where wp.ranks='2'),null) 
as SecondHighestSalary;

177. 第N高的薪水

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+
例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

解法一：利用 limit 进行限制 此方法可适用于求第N高的薪水，且数据越复杂，速度优势越明显,(此时不能写   limit N-1,1)

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set N=N-1;
  RETURN (
      # Write your MySQL query statement below.
select(select distinct(Salary)  from Employee  order by Salary desc limit  N,1)
 as SecondHighestSalary );
END
或者
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 SecondHighestSalary);
END

解法二：使用开窗函数dense_rank()对其对薪水进行降序排号，选择序号为N的即可，注意只输出一个薪水值,此时薪水值都一样可以使用distinct min ,max选一个都行

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
select (select distinct wp.Salary from (select *,dense_rank() over
 (order by Salary desc) ranks from Employee ) wp where wp.ranks=N) 
as SecondHighestSalary);
END
或者
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
select IFNULL((select distinct (wp.Salary) from 
(select *,dense_rank() over (order by Salary desc)
 ranks from Employee ) wp where wp.ranks=N),null) 
as SecondHighestSalary
  );
END
或者
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
select (select min(wp.Salary) from (select *,dense_rank() over
 (order by Salary desc) ranks from Employee ) wp where wp.ranks=N) 
as SecondHighestSalary);
END
或者
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
select IFNULL((select min(wp.Salary) from 
(select *,dense_rank() over (order by Salary desc)
 ranks from Employee ) wp where wp.ranks=N),null) 
as SecondHighestSalary);
END
或者
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
select (select max(wp.Salary) from (select *,dense_rank() over
 (order by Salary desc) ranks from Employee ) wp where wp.ranks=N) 
as SecondHighestSalary);
END
或者
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
select IFNULL((select max(wp.Salary) from 
(select *,dense_rank() over (order by Salary desc)
 ranks from Employee ) wp where wp.ranks=N),null) 
as SecondHighestSalary);
END

178. 分数排名

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+
重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 `Rank`

解法一：使用开窗函数dense_rank()排序即可

select s.Score as Score,dense_rank() over 
(order by Score desc) as `Rank` from Scores s;

解法二：自链接

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

第一部分不难写：

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

180. 连续出现的数字

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

解法一：三次自连接，这种方法比较好理解，可以运行通过，这题官方给的答案就是这个，说的id是连续的，但是id字段如果不是自增1的话感觉会出问题。

select distinct l1.num ConsecutiveNums 
from Logs l1,Logs l2,Logs l3
where 
    l1.id=l2.id-1 and l2.id=l3.id-1
  and 
    l1.num=l2.num and l2.num=l3.num;

解法二：case when+变量或if+变量

select distinct num ConsecutiveNums  from(
    select num,
        case 
            when @prev = num then @count := @count+1
            else (@prev := num) and (@count := 1)
        end count
    from Logs,(select @prev := null, @count := 0)  t
)  temp 
where temp.count>=3;
或
select distinct num ConsecutiveNums  from(
    select num,
        if(@prev = num,@count := @count+1,@count := 1) count,
        @prev := num 
    from Logs,(select @prev := null, @count := 0)  t
)  temp 
where temp.count>=3;

解法三：使用开窗函数mysql5.7不支持，8.0支持，leetcode上使用的低版本，这上面可以使用Sql server。

这个参考大佬的（参考链接），考虑比较全面。

思路：

（1）、由于要获取至少连续三次出现的数字，看到这个题肯定是会变的，如果是至少连续出现四次呢(100次呢)，咱们连接四个表(连接一千个?)？这种方法肯定是不可取的。
（2）、找规律，找出这连续起来的数字有什么规律呢，我们会发现连续的数字是相同的数字，但是id有可能不是连续的，我们就需要通过对结果集进行再次编号，让其变成连续的。
（3）、首先我们获取到对每条数据编号从1开始使用row_number()函数使用id来排序既row_number() over(order by id)

SELECT id,num,row_number() over (ORDER BY id) as dif FROM logs;

此时结果：

（4）、然后我们通过另一种方式排序将，这些num值一样的进行排序，然后对其编号同样使用row_bumber()使用num来分组使用id排序 over(partition by num order by id)

SELECT id,num,row_number() over 
(partition by num ORDER BY id) as dif FROM logs;

（5）、通过3、4步骤我们能得到什么呢，两个相减之后我们可以得到，只要是相等的，则相减的值是一样的。而且如果不连续的话相减值也不一样。

select id,num,row_number() over(order by id)-row_number() 
over(partition by num order by id) dif from Logs;

（6）、最后在通过num和orde两个共同分组找到一样的一共有几个，我们就可以找到连续的了。

select dif,num,count(*) 
from (select id,num,row_number() over(order by id)-row_number() 
over(partition by num order by id) dif from Logs) a 
group by dif,num

（7）、筛选出大于3的

select distinct Num ConsecutiveNums 
from (select dif,num,count(*) 
from (select id,num,row_number() over(order by id)-row_number()
 over(partition by num order by id) dif from Logs) a 
group by dif,num having count(*)>=3) b;

181. 超过经理收入的员工

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+
给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

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

解法一：自连接

SELECT  e1.Name as Employee FROM Employee e1,Employee e2 
WHERE e1.ManagerId=e2.Id AND e1.Salary > e2.Salary;

解法二：内连接

SELECT e1.Name as Employee FROM Employee e1 JOIN Employee e2 
ON e1.ManagerId=e2.Id AND e1.Salary > e2.Salary;

解法三：子查询（效率比较低，不建议使用，能关联就不要子查询）

SELECT name Employee FROM Employee e WHERE e.Salary >
 (SELECT Salary FROM Employee WHERE Id = e.ManagerId);

182. 查找重复的电子邮箱

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
根据以上输入，你的查询应返回以下结果：

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

解法一：使用 GROUP BY 和 HAVING 条件

SELECT Email FROM person GROUP BY Email HAVING count(Email)>1;

解法二：使用 GROUP BY 和临时表

SELECT e.Email FROM 
(SELECT COUNT(Email) count,p.Email FROM person p 
GROUP BY Email HAVING count >1) e;
或
SELECT Email FROM
(SELECT Email, count(Email) as num FROM Person GROUP BY Email) 
as e WHERE num > 1;

解法三：自连接

SELECT DISTINCT p1.Email FROM Person AS p1,Person AS p2 
WHERE p1.Email =p2.Email AND p1.Id!=p2.Id;

183. 从不订购的客户

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

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

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+
例如给定上述表格，你的查询应返回：

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

解法一：使用子查询和 NOT IN 子句

SELECT C.Name Customers FROM Customers C WHERE C.Id NOT IN 
(SELECT DISTINCT CustomerId FROM Orders);

解法二：左连接

SELECT C.Name Customers FROM Customers C 
LEFT JOIN Orders O ON C.Id=O.CustomerId WHERE O.CustomerId is null;

解法三：使用查询出有订单的客户，然后使用not in判断当前元祖在不在这个元祖集合中

SELECT C.Name Customers FROM Customers c WHERE NOT EXISTS
 (SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id);

184. 部门工资最高的员工

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 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            |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

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

Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

解法一：使用开窗函数rank()或dense_rank()实现（mysql8.0才支持开窗函数）

rank()：1、1、3、3、5。

dense_rank()：1、1、2、2、3。

SELECT t2.Department,t2.Name Employee,t2.Salary from (
SELECT t1.*,rank()over(partition by Department order by salary desc) ranks from(
SELECT em.*,dt.Name Department FROM Employee em,Department dt WHERE em.DepartmentId=dt.Id
) t1
) t2 WHERE t2.ranks=1;

解法二：子查询

首先找出每个分组及其最大值，然后判断表中每条数据的分组和Salary是否和这个相等。

SELECT
	dt.NAME AS Department,
	em.NAME AS Employee,Salary 
FROM
	Employee em,
	Department dt
WHERE
	em.DepartmentId = dt.Id
AND
	(em.DepartmentId,Salary)
IN
	(SELECT DepartmentId, max( Salary ) 
        FROM Employee 
        GROUP BY DepartmentId);

select d.Name as Department, e.Name as Employee, Salary
from Employee e join Department d on e.DepartmentId = d.Id
where (e.DepartmentId, e.Salary) in (
    select DepartmentId, max(Salary)
    from Employee
    group by DepartmentId
);

解法三： 连接查询

连接临时表（Employee和查询出来的每个分组的最大值连接）

select d.Name as Department, e.Name as Employee, e.Salary
from Employee e join Department d on e.DepartmentId = d.Id join (
    select DepartmentId, max(Salary) as Salary
    from Employee
    group by DepartmentId) t on e.DepartmentId = t.DepartmentId and e.Salary = t.Salary;

自连接（Employee和自身连接，on的条件为部门相同且同时约束Salary的大小。）

select d.Name as Department, e1.Name as Employee, e1.Salary
from Employee e1 join Department d on e1.DepartmentId = d.Id join Employee e2 
on e1.DepartmentId = e2.DepartmentId and e1.Salary <= e2.Salary
group by e1.Id
having count(distinct e2.Salary) = 1;

参考博客：https://zkkkillua.github.io/

185. 部门工资前三高的所有员工

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+
Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

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

解法一：使用开窗函数dense_rank()实现（mysql8.0才支持开窗函数）

SELECT t2.Department,t2.Name Employee,t2.Salary from (
SELECT t1.*,dense_rank()over(partition by Department order by salary desc) ranks from(
SELECT em.*,dt.Name Department FROM Employee em,Department dt WHERE em.DepartmentId=dt.Id
) t1
) t2 WHERE t2.ranks<=3;

解法二：子查询

可以使用子查询，对于每一条数据在子查询中查找工资更高的，并通过count()统计个数。

由于子查询跟外部查询相关，因此这里的子查询是相关子查询，对于外部的每一条数据都要进行一次子查询，效率较低。

select d.Name as Department, e1.name as Employee, e1.Salary
from Employee e1 join Department d on e1.DepartmentId = d.Id
where 3 > (
    select count(distinct e2.Salary)
    from Employee e2
    where e1.DepartmentId = e2.DepartmentId and e1.Salary < e2.Salary
);

解法三： 自连接

由于上述相关子查询的效率较低，因此可以考虑使用自连接，通过on控制条件，使得连接起来的数据属于同一个部门，并且之前存在工资的大小关系。

这条连接查询语句的效率要比上述使用相关子查询的语句高。

select d.Name as Department, e1.name as Employee, e1.Salary
from Employee e1 join Department d on e1.DepartmentId = d.Id join Employee e2 
on e1.DepartmentId = e2.DepartmentId and e1.Salary <= e2.Salary
group by e1.Id
having 3 >= count(distinct e2.Salary);

参考博客：https://zkkkillua.github.io/

196. 删除重复的电子邮箱

编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

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

提示：执行 SQL 之后，输出是整个 Person 表。
           使用 delete 语句。

解法一：group by

因为要求保留每种邮箱中最小的Id，因此只需要根据邮箱分组，筛选出每组邮箱最小的Id，然后删除不是最小的Id即可。

delete  from Person where Id not in (
select t.Id from
(select min(Id) Id from Person group by Email) t
);

解法二：自连接

使用delete t1 from t1, t2 where..语句，而不是delete from t1 where...语句。
这条语句不是从t1和t2表连接得到的临时表中删除数据，而是根据where的条件从删除t1中的数据，from只是方便where的条件。

DELETE P1 FROM Person P1, Person P2
WHERE P1.Email=P2.Email AND P1.Id>P2.Id; 

DELETE P1 FROM Person P1 JOIN Person P2
ON P1.Email=P2.Email WHERE P1.Id>P2.Id;

参考博客：https://zkkkillua.github.io

197. 上升的温度

表 Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id 是这个表的主键
该表包含特定日期的温度信息
 

编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。

返回结果 不要求顺序 。

查询结果格式如下例：

Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

Result table:
+----+
| id |
+----+
| 2  |
| 4  |
+----+
2015-01-02 的温度比前一天高（10 -> 25）
2015-01-04 的温度比前一天高（30 -> 20）

解法一：先用 to_days()函数，确定昨天，然后再比较温度的高低

SELECT w1.id FROM weather w1,weather w2 WHERE 
TO_DAYS(w1.recordDate)-TO_DAYS(w2.recordDate)=1 
AND w1.temperature > w2.temperature;

函数 to_days( ) ：返回从0000年（公元1年）至 当前日期（所给参数）的总天数

解法二：先用datediff() 函数，确定昨天，再比较温度

SELECT w1.id FROM weather w1,weather w2 WHERE 
DATEDIFF(w1.recordDate,w2.recordDate)=1 AND
w1.temperature > w2.temperature;

函数datediff( ) : 返回两个日期之间的天数

解法三：先用subdate() 函数，来实现日期减1，确定昨天，再比较温度

SELECT w1.Id FROM Weather w1, Weather w2
WHERE w1.Temperature > w2.Temperature 
AND SUBDATE(w1.recordDate, INTERVAL 1  DAY) = w2.recordDate;

SUBDATE(DATE_FORMAT(now() , '%Y-%m-%d'),INTERVAL 10  DAY)
从今天往前的10天
SUBDATE(DATE_FORMAT(now() , '%Y-%m-%d'),INTERVAL -10  DAY)
从今天往后的10天

595. 大的国家

这里有张 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     |
+-----------------+------------+------------+--------------+---------------+
如果一个国家的面积超过 300 万平方公里，或者人口超过 2500 万，那么这个国家就是大国家。

编写一个 SQL 查询，输出表中所有大国家的名称、人口和面积。

例如，根据上表，我们应该输出:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

解法一：使用or

select w.name,w.population,w.area from World w 
where w.population > 25000000 or w.area > 3000000;

解法二：使用union 

SELECT
    name, population, area FROM world WHERE area > 3000000
UNION
SELECT name, population, area FROM world WHERE population > 25000000;

总结：

尽量使用 union 代替 or查询 ，查询优化, or会使索引失效，标称全文扫描
对于只有非索引字段来说你就老老实实的用or 或者in，因为 非索引字段本来要全表扫描而union all 只成倍增加表扫描的次数
1、 对OR语句求并集，如查询SELECT * FROM TB1 WHERE c1="xxx" OR c2=""xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果合并（union）操作，得到最终结果
2、 对AND语句求交集，如查询SELECT * FROM TB1 WHERE c1="xxx" AND c2=""xxx"时，如果c1和c2列上分别有索引，可以按照c1和c2条件进行查询，再将查询结果取交集（intersect）操作，得到最终结果

596. 超过5名学生的课

有一个courses 表 ，有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。

例如，表：

+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+
应该输出:

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

提示：

学生在每个课中不应被重复计算。

解法一：使用 GROUP BY 子句和子查询【通过】

SELECT class FROM
    (SELECT
        class, COUNT(DISTINCT student) AS num
    FROM
        courses
    GROUP BY class) AS temp_table
WHERE
    num >= 5;

方法二：使用 GROUP BY 和 HAVING 条件【通过】

SELECT class FROM courses GROUP BY class HAVING (COUNT(DISTINCT student)>=5);

601. 体育馆的人流量

表：Stadium
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加
 

编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date 升序排列的结果表。

查询结果格式如下所示。

Stadium table:
+------+------------+-----------+
| 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-09 | 188       |
+------+------------+-----------+

Result table:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。

解法一：由于题目说了id是连续的，利用多表联合查询，分为三种情况顺序， distinct是查询中不含重复，因为如果连续4个或以上都满足条件，有些行会在查询结果中重复

select distinct a.* from stadium a,stadium b,stadium c
where a.people>=100 and b.people>=100 and c.people>=100
and (
     (a.id = b.id-1 and b.id = c.id -1) or
     (a.id = b.id-1 and a.id = c.id +1) or
     (a.id = b.id+1 and b.id = c.id +1)
) order by a.id;

解法二：使用开窗函数

（1）首先过滤出people>100的字段
（2）开窗，用id减去rank排名，并根据id进行排序。 若是连续的那么，差值一定是相同的
（3）where过滤出条数>=3的完成解题

with t as (
select id,visit_date,people,id - (row_number() over(order by id)) diff#求出差值
from stadium
where people >= 100)
select id,visit_date,people
from (
    select id,visit_date,people,count(*) over(partition by diff) c
    from t) t1
where c >= 3;#where条件过滤出条数大于3的

620. 有趣的电影

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。

作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。

 

例如，下表 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     |
+---------+-----------+--------------+-----------+
对于上面的例子，则正确的输出是为：

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

解法一：使用 MOD() 函数

select c.* from cinema c where c.description!='boring' and mod(c.id,2) = 1 order by c.rating desc;

解法二：位运算 ，二进制中，奇数和1做&操作得到1，偶数和1做&操作得到0。偶数最后一位是0，奇数是1，按位运算的话，0&1就是0，1&1就是1

不等号建议尽量使用<>

select c.* from cinema c where c.description<>'boring' and c.id&1 order by c.rating desc;

626. 换座位

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

示例：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+
假如数据输入的是上表，则输出结果如下：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+
注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

解法一：

查询id和student

若id是偶数，减1
若id是奇数，加1
主要问题在于当总数为奇数时，最后一个id应保持不变，加1会导致空出一位。

SELECT * FROM(
    SELECT id-1 AS id,student FROM seat WHERE id%2=0
    UNION
    SELECT id+1 AS id,student FROM seat WHERE id%2=1 AND (id+1) <= (SELECT COUNT(*) FROM seat)
    UNION
    SELECT id AS id,student FROM seat WHERE id%2=1 AND (id+1) > (SELECT COUNT(*) FROM seat)
) AS T1
ORDER BY id;

（参考地址）实现逻辑和上面一样，使用if语句实现：
解决此问题并不复杂：我们找到最后一位，让它保持不变就可以了。

select 
    if(id%2=0,id-1,
        if(id=(select count(distinct id) from seat),id,id+1)) 
    as id,student 
from seat 
order by id;

解法二：利用异或巧妙解法。利用异或只把偶数减2，奇数不变，从而调位。

（单数-1后，最后一位是0，异或把最后一位变回1，等于不变；偶数-1后，最后一位是1，异或把最后一位变成0，等于再减去1）

select rank() over(order by (id-1)^1) as id,student from seat;

627. 变更性别

给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。

例如：

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |
运行你所编写的更新语句之后，将会得到以下表:

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |

方法一：使用 UPDATE 和 CASE...WHEN

UPDATE  salary
SET sex = (
CASE
WHEN sex = 'm' THEN 'f'
ELSE 'm'
END
)

方法二：使用if 实现三目运算 

update  salary set sex = if(sex = 'm','f','m');