力扣数据库(end09.02)

175 组合两个表

表1: Person

+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表2: Address

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

FirstName, LastName, City, State

这题的难度是简单，就是考了一个表的连接，答：

# Write your MySQL query statement below
select FirstName, LastName, City, State
from
Person as p
left join
Address as a
on
p.personid = a.personid;

176 第二高的薪水

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

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

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

答：
用了ifnull( )这个函数
格式:IFNULL(expression_1,expression_2)
如果expression_1不为null，则infull函数返回expression_1；否则返回expression_2

# Write your MySQL query statement below
select IFNULL(
(select distinct salary
from
employee
order by salary desc limit 1,1),
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                    |
+------------------------+

这里用到了sql的自定义函数

create funetion 函数名（参数）
return 返回值数据类型
[with {Encryption | Schemabinding }]
[as]
begin
SQL语句(必须有return 变量或值)
End

create funetion SumRes(@sco nvarchar(20))
returns real //返回值类型
as
begin

//例子
declare @sum real //变量
declare @code varchar(11) //变量

set @code = @sco + '%' //赋值
select @sum = sum(result) from LearnResult where scode like @code //查询结果赋值

return @sum 
end

//引用/使用函数
select 用户名.函数名 as 字段别名
select dbo.AvgResult('sooo2') as result 

本题的思路和上一题差不多，也就是将它设计为函数，用M替代2

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    declare M int default 0;
    set M=N-1;
    RETURN (
        select IFNULL(
        (select distinct salary
        from
        employee
        order by salary desc limit M,1),
        null)
    );
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

题解

select score as Score, dense_rank() over (order by score desc) as 'Rank'
from scores;

SQL中的排序函数

1. rank函数

RANK ( ) OVER 
( [ PARTITION BY <partition_column> ] ORDER BY <order_by_column> )

• partition by 指定一个列或多个列作为排名的分区依据
• order by 指定一个列或者多个列对每个区的输出数据进行排序

有两人有相同分数，相同排名N，下一人排名跳过重复人数2，排名N+2

2. dense_rank()
   相同的分数有相同的排名N，下一人排名N+1

3. row_number()
   依次递增，相同分数不同排名

4. ntile(N)
   将分数均分为N个等级，依据排名划分等级
   如果不能被平均分配到不同个组的时候，比如参数导致有不能被整除的时候。当发生这种情况是那么将不能被整除的行按序放到每一个组内。

分数	rank	dence_rank	row_number	ntile(4)
50	1	1	1	1
60	2	2	2	1
60	2	2	3	1
70	4	3	4	2
80	5	4	5	2
90	6	5	6	3
90	6	5	7	3
90	6	5	8	4
100	9	6	9	4

180 连续出现的数字

思路，一种是多表连接，但这种方法在连续出现多次的要求下复杂度会变得很多。

select distinct t1.num as ConsecutiveNums
from logs t1 join logs t2
on (t1.id + 1 = t2.id and t1.num = t2.num)
join logs t3 on (t2.id + 1 = t3.id and t2.num = t3.num);

另一种，这种其实我没有想太明白，看一下做法是。

SELECT DISTINCT Num FROM (
SELECT Num,COUNT(1) as SerialCount FROM 
(SELECT Id,Num,
row_number() over(order by id) -
ROW_NUMBER() over(partition by Num order by Id) as SerialNumberSubGroup
FROM ContinueNumber) as Sub
GROUP BY Num,SerialNumberSubGroup HAVING COUNT(1) >= 3) as Result

核心思想是，如果一个num是连续出现的，那么他出现的id-它出现的次数是一个定值x

• 假设某树第k次出现时id=i
• 第一种情况，该数连续出现，下一次它出现的id=i+1，为第k+1次出现
• 第二种情况，该数不连续出现，下一次它出现id=i+m(m != 1)，为第k+1次出现
• 因此，在连续出现的情况下，一定有 i+n - k+n = … = i - k = x

select id, num, row_number() over( order by id) as nid, row_number() over (partition by num order by id)as prank from continuousnum;
+------+------+-----+-------+
| id   | num  | nid | prank |
+------+------+-----+-------+
|    1 |    1 |   1 |     1 |
|    2 |    1 |   2 |     2 |
|    3 |    1 |   3 |     3 |
|    5 |    1 |   5 |     4 |
|    4 |    2 |   4 |     1 |
|    6 |    2 |   6 |     2 |
|    7 |    2 |   7 |     3 |
|    8 |    3 |   8 |     1 |
|    9 |    3 |   9 |     2 |
|   10 |    3 |  10 |     3 |
+------+------+-----+-------+
select t.id, t.num, t.nid-t.prank as diff from(
select id, num, row_number() over( order by id) as nid, row_number() over (partition by num order by id)as prank from continuousnum) t;
+------+------+------+
| id   | num  | diff |
+------+------+------+
|    1 |    1 |    0 |
|    2 |    1 |    0 |
|    3 |    1 |    0 |
|    5 |    1 |    1 |
|    4 |    2 |    3 |
|    6 |    2 |    4 |
|    7 |    2 |    4 |
|    8 |    3 |    7 |
|    9 |    3 |    7 |
|   10 |    3 |    7 |
+------+------+------+
10 rows in set (0.00 sec)

再得到上面的表之后，我们要做的是根据num和diff进行分组，找到出现次数超过三次的num

select distinct num from(
select t2.num, count(*) from(
select t.id, t.num, t.nid-t.prank as diff from(
select id, num, row_number() over( order by id) as nid, row_number() over (partition by num order by id)as prank from continuousnum) t) t2 group by t2.num,diff having count(*) >= 3) as t3;

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 a.name as 'Employee' from employee a
left join employee b
on a.managerid = b.id
where a.salary > b.salary;

182 查找重复的电子邮箱

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

示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

根据以上输入，你的查询应返回以下结果：

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

说明：所有电子邮箱都是小写字母。

select distinct a.Email from
person a
join
person b
on aEmail = b.Email and a.id != b.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       |
+-----------+

测试用例中，有order是空的表，有重名的人

select distinct c.name as Customers
from
customers c
join
orders o
on c.id not in (select distinct customerid from orders);

这种题把distinct搞掉就有点离谱

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 在销售部的工资最高。
按之前我的写法都是

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 maxsalary from employee group by departmentid) t
on e.salary = t.maxsalary and e.departmentid = t.departmentid;

另一种写法其实可以是

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;

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()函数来做

select d.name as Department, t.name as Employee, t.Salary from 
(select name, salary, departmentid, dense_rank() over (partition by departmentid order by salary desc) as deptrank from employee) t
join
department d
on t.departmentid = d.id
where t.deptrank <= 3;

不用函数的话，公司前三意味着有不超过3个工资比这些值大
所以我们做一个自连接，算出每个工资有多少更大的值

//
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
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;
select d.name as 'Department', e1.name as 'Employee', e1.Salary
from employee e1 
where 3 > (
select count( distinct e2.salary) from
employee e2
where e2.salary >= e1.salary and e2.departmentid = e1.departmentid);

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 语句。

delete p1 FROM Person p1,Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id;

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 的温度比前一天高（20 -> 30）

题解

select w1.id from Weather w1, Weather w2
where DATEDIFF(w1.recordDate, w2.recordDate) = 1 and w1.Temperature > w2.Temperature; 

datediff和timestampdiff

datediff(date1, date2)
得到的结果是date1与date2相差的天数。
如果date1比date2大，结果为正；如果date1比date2小，结果为负。

timestampdiff(时间类型, time1, time2)
得到的结果是time1与time2相差的时间类型。
time1大于time2，结果为负，time1小于time2，结果为正。

262 行程和用户

表：Trips

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| Id          | int      |
| Client_Id   | int      |
| Driver_Id   | int      |
| City_Id     | int      |
| Status      | enum     |
| Request_at  | date     |     
+-------------+----------+
Id 是这张表的主键。
这张表中存所有出租车的行程信息。每段行程有唯一 Id ，其中 Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。
Status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。

表：Users

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| Users_Id    | int      |
| Banned      | enum     |
| Role        | enum     |
+-------------+----------+
Users_Id 是这张表的主键。
这张表中存所有用户，每个用户都有一个唯一的 Users_Id ，Role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。
Banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。

写一段 SQL 语句查出 “2013-10-01” 至 “2013-10-03” 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 Banned 为 No 的用户，禁止用户即 Banned 为 Yes 的用户。

取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。

返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。

查询结果格式如下例所示：

Trips 表：
+----+-----------+-----------+---------+---------------------+------------+
| 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 |
+----+-----------+-----------+---------+---------------------+------------+

Users 表：
+----------+--------+--------+
| 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 |
+----------+--------+--------+

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

2013-10-01：
  - 共有 4 条请求，其中 2 条取消。
  - 然而，Id=2 的请求是由禁止用户（User_Id=2）发出的，所以计算时应当忽略它。
  - 因此，总共有 3 条非禁止请求参与计算，其中 1 条取消。
  - 取消率为 (1 / 3) = 0.33
2013-10-02：
  - 共有 3 条请求，其中 0 条取消。
  - 然而，Id=6 的请求是由禁止用户发出的，所以计算时应当忽略它。
  - 因此，总共有 2 条非禁止请求参与计算，其中 0 条取消。
  - 取消率为 (0 / 2) = 0.00
2013-10-03：
  - 共有 3 条请求，其中 1 条取消。
  - 然而，Id=8 的请求是由禁止用户发出的，所以计算时应当忽略它。
  - 因此，总共有 2 条非禁止请求参与计算，其中 1 条取消。
  - 取消率为 (1 / 2) = 0.50

思路，先筛选出那些没有被banner的订单，然后再用分组函数

select t.Request_at as Day,
   Round(sum(if(t.STATUS = 'completed',0,1))/count(t.STATUS),2) as 'Cancellation Rate'
from
   (select * from Trips where
    Client_Id in (select Users_Id from Users where Banned != 'Yes')
    and
    Driver_Id in (select Users_Id from Users where Banned != 'Yes')
    ) as t
group by t.Request_at;

提交，错了

输入：
{"headers": {"Trips": ["Id", "Client_Id", "Driver_Id", "City_Id", "Status", "Request_at"], "Users": ["Users_Id", "Banned", "Role"]}, "rows": {"Trips": [["1", "1", "10", "1", "cancelled_by_client", "2013-10-04"]], "Users": [["1", "No", "client"], ["10", "No", "driver"]]}}
输出：
{"headers": ["Day", "Cancellation Rate"], "values": [["2013-10-04", 1.00]]}
预期结果：
{"headers":["Day","Cancellation Rate"],"values":[]}

仔细看题目才发现原来还有个时间范围！查出 “2013-10-01” 至 “2013-10-03” 期间

select t.Request_at as Day,
   Round(sum(if(t.STATUS = 'completed',0,1))/count(t.STATUS),2) as 'Cancellation Rate'
from
   (select * from Trips where
    Client_Id in (select Users_Id from Users where Banned != 'Yes')
    and
    Driver_Id in (select Users_Id from Users where Banned != 'Yes')
    and
    request_at BETWEEN '2013-10-01' AND '2013-10-03'
    ) as t
group by t.Request_at;

看见一种很厉害的做法，把Round(sum(if(t.STATUS = 'completed',0,1))/count(t.STATUS),2)用round(avg(Status!='completed'), 2)来计算。

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-row_number的排名会是一个定值，如下所示，我们需要的是5，6，7，8这四条数据，我们可以发现他们的diff为2，出现了四次。

select *, row_number() over (order by id),(id - row_number() over (order by id) ) as diff from stadium where people >= 100;
+------+------------+--------+---------------------------------+------+
| id   | visit_date | people | row_number() over (order by id) | diff |
+------+------------+--------+---------------------------------+------+
|    2 | 2017-01-02 |    109 |                               1 |    1 |
|    3 | 2017-01-03 |    150 |                               2 |    1 |
|    5 | 2017-01-05 |    145 |                               3 |    2 |
|    6 | 2017-01-06 |   1455 |                               4 |    2 |
|    7 | 2017-01-07 |    199 |                               5 |    2 |
|    8 | 2017-01-09 |    188 |                               6 |    2 |
+------+------------+--------+---------------------------------+------+
6 rows in set (0.00 sec)

下一步需要找出diff值出现了三次及以上的

select diff from t group by diff having count(diff) > 2;

组装

with t
as
( select *,(id - row_number() over (order by id) ) as diff from stadium where people >= 100 )
select id, visit_date, people from t
where diff in (select diff from t group by diff having count(diff) >2);
+------+------------+--------+
| id   | visit_date | people |
+------+------------+--------+
|    5 | 2017-01-05 |    145 |
|    6 | 2017-01-06 |   1455 |
|    7 | 2017-01-07 |    199 |
|    8 | 2017-01-09 |    188 |
+------+------------+--------+
4 rows in set (0.00 sec)

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

注意：

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

题解

select 
    (case 
        when mod(id,2) = 0 then id -1 
        when mod(id,2) <> 0 and id <> (select count(id) from seat) then id + 1
        else id end) as id,
student from seat order by id;

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 salary set sex = if(sex='m','f','m');

有一个离谱的答案

update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));

1179 重新格式化部门

部门表 Department：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| revenue       | int     |
| month         | varchar |
+---------------+---------+
(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。

编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。

查询结果格式如下面的示例所示：

Department 表：

+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+

查询得到的结果表：

+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+

注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。

# Write your MySQL query statement below
select id,
sum(case when month ='Jan' then revenue end) as 'Jan_Revenue',
sum(case when month ='Feb' then revenue end) as 'Feb_Revenue',
sum(case when month ='Mar' then revenue end) as 'Mar_Revenue',
sum(case when month ='Apr' then revenue end) as 'Apr_Revenue',
sum(case when month ='May' then revenue end) as 'May_Revenue',
sum(case when month ='Jun' then revenue end) as 'Jun_Revenue',
sum(case when month ='Jul' then revenue end) as 'Jul_Revenue',
sum(case when month ='Aug' then revenue end) as 'Aug_Revenue',
sum(case when month ='Sep' then revenue end) as 'Sep_Revenue',
sum(case when month ='Oct' then revenue end) as 'Oct_Revenue',
sum(case when month ='Nov' then revenue end) as 'Nov_Revenue',
sum(case when month ='Dec' then revenue end) as 'Dec_Revenue'
from department 
group by id
order by id;