LeetCode SQL题笔记

197.上升的温度

表： Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id 是该表具有唯一值的列。
该表包含特定日期的温度信息

编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。

返回结果 无顺序要求 。

结果格式如下例子所示。

示例 1：

输入：
Weather 表：
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+
输出：
+----+
| id |
+----+
| 2  |
| 4  |
+----+
解释：
2015-01-02 的温度比前一天高（10 -> 25）
2015-01-04 的温度比前一天高（20 -> 30）

解析(datediff函数)

这道题首先是要得到id，其次是需要比较日期和温度，温度容易比较，使用> <等即可，但是日期类型不能直接比较，否则会有问题，如1号的前一天是30号或者31号，因此需要使用日期比较函数，这里使用的是datediff(日期1， 日期2)函数，该函数返回的是日期1 - 日期2相差的天数，如datediff('2023-01-11', '2023-01-10')返回的是1，而datediff('2023-01-10', '2023-01-11')返回的是-1。

使用什么符号、函数进行比较解决了，接下来需要思考如何解答这道题。需要判断的是“今天”比“昨天”的温度高的，也就是说datediff(今天, 昨天)应该返回1，但是在一个表直接比较两个日期不容易实现，因此可以尝试使用自连接。

select w2.id id
from weather w1, weather w2
where datediff(w2.recordDate , w1.recordDate) = 1
and w2.temperature > w1.temperature;

以上sql表示，从左表w1（驱动表）取出所有数据，然后依次用每一条数据对比右表w2（被驱动表）的数据，对比的条件就是datediff(w2.recordDate , w1.recordDate) = 1 and w2.temperature > w1.temperature，当满足这个条件时，即返回“今天”（w2.id）的数据，因为条件是w2日期比较大，如果需要返回“昨天”的数据即返回w1.id。

586.订单最多的客户

表: Orders

+-----------------+----------+
| Column Name     | Type     |
+-----------------+----------+
| order_number    | int      |
| customer_number | int      |
+-----------------+----------+
在 SQL 中，Order_number是该表的主键。
此表包含关于订单ID和客户ID的信息。

查找下了 最多订单 的客户的 customer_number 。

测试用例生成后， 恰好有一个客户 比任何其他客户下了更多的订单。

查询结果格式如下所示。

示例 1:

输入: 
Orders 表:
+--------------+-----------------+
| order_number | customer_number |
+--------------+-----------------+
| 1            | 1               |
| 2            | 2               |
| 3            | 3               |
| 4            | 3               |
+--------------+-----------------+
输出: 
+-----------------+
| customer_number |
+-----------------+
| 3               |
+-----------------+
解释: 
customer_number 为 '3' 的顾客有两个订单，比顾客 '1' 或者 '2' 都要多，因为他们只有一个订单。
所以结果是该顾客的 customer_number ，也就是 3 。

进阶： 如果有多位顾客订单数并列最多，你能找到他们所有的 customer_number 吗？

解析

这道题需要查询最多订单的客户，表中的订单号order_number是主键，也就是唯一的，而客户号customer_number可以重复，因此相同的客户号的数量即为订单数量，很容易想到使用分组聚合，使用count进行查询每个客户的订单数量，然后再进行倒序排序取第一个即可查询到最多数量的客户号。

select customer_number
from orders
group by customer_number
order by count(customer_number) desc
limit 1;

题目中进阶要求，如果多位顾客订单数并列最多，把所有的查询出来。首先可以明确，如果多个顾客订单数量都是最多的，上述代码中查询到的是最多数量，因此多个顾客订单数量最多也是这个数的大小。因此只需要再包一层查询进行嵌套，比较一下当前顾客的数量是不是等于当前数量即可。

select customer_number
from orders
group by customer_number
having count(customer_number) = (
    select count(customer_number) cnt
    from orders
    group by customer_number
    order by cnt desc
    limit 1
);

略作修改，如果直接对count(customer_number)排序会报错，需要在select中进行查询取别名，然后再对别名进行排序。

610.判断三角形

表: Triangle

+-------------+------+
| Column Name | Type |
+-------------+------+
| x           | int  |
| y           | int  |
| z           | int  |
+-------------+------+
在 SQL 中，(x, y, z)是该表的主键列。
该表的每一行包含三个线段的长度。

对每三个线段报告它们是否可以形成一个三角形。

以 任意顺序 返回结果表。

查询结果格式如下所示。

示例 1:

输入: 
Triangle 表:
+----+----+----+
| x  | y  | z  |
+----+----+----+
| 13 | 15 | 30 |
| 10 | 20 | 15 |
+----+----+----+
输出: 
+----+----+----+----------+
| x  | y  | z  | triangle |
+----+----+----+----------+
| 13 | 15 | 30 | No       |
| 10 | 20 | 15 | Yes      |
+----+----+----+----------+

解析(case when条件判断)

题目不难，很容易理解。问题在于如何判断并且输出YesorNo。

这里使用case when进行判断。

select x, y, z, 
    case
        when x + y > z and x + z > y and y + z > x then 'Yes'
        else 'No'
    end 'triangle'
from triangle;

619.只出现一次的最大数字

MyNumbers 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| num         | int  |
+-------------+------+
该表可能包含重复项（换句话说，在SQL中，该表没有主键）。
这张表的每一行都含有一个整数。

单一数字 是在 MyNumbers 表中只出现一次的数字。

找出最大的 单一数字 。如果不存在 单一数字 ，则返回 null 。

查询结果如下例所示。

示例 1：

输入：
MyNumbers 表：
+-----+
| num |
+-----+
| 8   |
| 8   |
| 3   |
| 3   |
| 1   |
| 4   |
| 5   |
| 6   |
+-----+
输出：
+-----+
| num |
+-----+
| 6   |
+-----+
解释：单一数字有 1、4、5 和 6 。
6 是最大的单一数字，返回 6 。

示例 2：

输入：
MyNumbers table:
+-----+
| num |
+-----+
| 8   |
| 8   |
| 7   |
| 7   |
| 3   |
| 3   |
| 3   |
+-----+
输出：
+------+
| num  |
+------+
| null |
+------+
解释：输入的表中不存在单一数字，所以返回 null 。

解析(max函数)

题目容易理解，可以分为两部分解决，首先是查询到只出现了一次的数字，然后从中取最大值。

其中的比较难处理的是如果没有出现一次的数字，需要返回null。

这里使用嵌套查询和max函数。其中max函数在查询表为空时会返回null值。

select max(num) num
from (
    select num
    from mynumbers
    group by num
    having count(num) = 1
) t

1084.销售分析III

表： Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
| unit_price   | int     |
+--------------+---------+
product_id 是该表的主键（具有唯一值的列）。
该表的每一行显示每个产品的名称和价格。

表：Sales

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| seller_id   | int     |
| product_id  | int     |
| buyer_id    | int     |
| sale_date   | date    |
| quantity    | int     |
| price       | int     |
+------ ------+---------+
这个表可能有重复的行。
product_id 是 Product 表的外键（reference 列）。
该表的每一行包含关于一个销售的一些信息。

编写解决方案，报告2019年春季才售出的产品。即仅在2019-01-01至2019-03-31（含）之间出售的商品。

以 任意顺序 返回结果表。

结果格式如下所示。

示例 1:

输入：
Product table:
+------------+--------------+------------+
| product_id | product_name | unit_price |
+------------+--------------+------------+
| 1          | S8           | 1000       |
| 2          | G4           | 800        |
| 3          | iPhone       | 1400       |
+------------+--------------+------------+
Sales table:
+-----------+------------+----------+------------+----------+-------+
| seller_id | product_id | buyer_id | sale_date  | quantity | price |
+-----------+------------+----------+------------+----------+-------+
| 1         | 1          | 1        | 2019-01-21 | 2        | 2000  |
| 1         | 2          | 2        | 2019-02-17 | 1        | 800   |
| 2         | 2          | 3        | 2019-06-02 | 1        | 800   |
| 3         | 3          | 4        | 2019-05-13 | 2        | 2800  |
+-----------+------------+----------+------------+----------+-------+
输出：
+-------------+--------------+
| product_id  | product_name |
+-------------+--------------+
| 1           | S8           |
+-------------+--------------+
解释:
id 为 1 的产品仅在 2019 年春季销售。
id 为 2 的产品在 2019 年春季销售，但也在 2019 年春季之后销售。
id 为 3 的产品在 2019 年春季之后销售。
我们只返回 id 为 1 的产品，因为它是 2019 年春季才销售的产品。

解析(count条件计数)

这道题是要寻找仅在第一季度销售的产品，转换思路是：在第一季度销售的产品数量和销售的总产品数量是相等的。

因此可以利用count函数的条件计数来做。

select s.product_id, p.product_name
from Sales s
inner join Product p
on s.product_id = p.product_id
group by s.product_id
having count(s.sale_date between '2019-01-01' and '2019-03-31' or null) = count(*);

其中count(s.sale_date between '2019-01-01' and '2019-03-31' or null)中的or null是固定写法，不写的话等价于count(*)。也可以写成count(if(s.sale_date between '2019-01-01' and '2019-03-31', 1, null))

MySQL常用四舍五入函数

函数	说明
floor(x)	返回不大于x的最大整数（下取整）
ceil(x), ceiling(x)	返回不小于x的最小整数（上取整）
truncate(x, d)	返回数值x保留到小数点后d位的值，直接截断，不四舍五入
round(x, d)	保留小数点后d位的值，截断时四舍五入
format(x, d)	将数字x格式化，保留到小数点后d位，截断时四舍五入

MySQL常用字符串函数

函数	说明
left(str, len)	字符串从左向右截取len长度的字符串
right(str, len)	字符串从右向左截取len长度的字符串
substring(str, pos [, len])	字符串从pos位置开始截取长度为len的字符串，如果len省略，则截取到最后
upper(str)	大写字符串
lower(str)	小写字符串
concat(str1, str2 [, ...])	连接字符串
length(str)	返回字符串的存储长度
char_length(str)	返回字符串的字符长度

MySQL时间日期函数

函数	说明
datediff(endtime, starttime)	比较两个日期相差多少天，返回endtime - starttime相差的数值
timediff(endtime, starttime)	比较两个日期相差多长时间，返回endtime - starttime相差的时间，以hh:MM:ss形式返回
timestampdiff(unit, starttime, endtime)	按照unit指定的单位返回endtime - starttime，如second day等
weekday(time)	返回星期的索引，0表示星期一，1表示星期二，以此类推
date_format(time, format)	date_format(now(), '%Y-%m-%d %H:%i:%s')格式化成年月日时分秒的形式，注意分钟是%i
str_to_date(time, format)	将字符串转成日期

mysql between and 边界问题-CSDN博客

SQL语句between and边界问题 - 楼兰胡杨 - 博客园 (cnblogs.com)

sql执行顺序

mysql 中的select,from,where,group by等 关键字 执行顺序与别名问题_mysql join where group 顺序-CSDN博客

SQL高级:窗口函数+聚合函数 - 知乎 (zhihu.com)

1179.重新格式化部门表

表 Department：

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

重新格式化表格，使得 每个月 都有一个部门 id 列和一个收入列。

以 任意顺序 返回结果表。

结果格式如以下示例所示。

示例 1：

输入：
Department table:
+------+---------+-------+
| 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 列用于各个月份）。

解析(group by原理，行转列)

一道行转列问题。参考力扣题解。

首先需要了解group by的原理，可以参考链接。

select
    id
    , sum(case when month = 'Jan' then revenue end) Jan_Revenue
    , sum(case when month = 'Feb' then revenue end) Feb_Revenue
    , sum(case when month = 'Mar' then revenue end) Mar_Revenue
    , sum(case when month = 'Apr' then revenue end) Apr_Revenue
    , sum(case when month = 'May' then revenue end) May_Revenue
    , sum(case when month = 'Jun' then revenue end) Jun_Revenue
    , sum(case when month = 'Jul' then revenue end) Jul_Revenue
    , sum(case when month = 'Aug' then revenue end) Aug_Revenue
    , sum(case when month = 'Sep' then revenue end) Sep_Revenue
    , sum(case when month = 'Oct' then revenue end) Oct_Revenue
    , sum(case when month = 'Nov' then revenue end) Nov_Revenue
    , sum(case when month = 'Dec' then revenue end) Dec_Revenue
from Department
group by id;

这里主要解释一下代码中为什么要使用sum聚合函数。

分组后的虚拟表可以看成如下形式：

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

因为case when条件只读取单元格中第一个数据，而分组后的单元格month列的单元格可能会有多个数据，因此需要遍历单元格所有数据，所以需要使用聚合函数进行遍历。每一个查询属性在单元格中只会对应一个数据或者没有对应数据，也就是结果为revenue或者null，因此结果没有偏差。

1795.每个产品在不同商店的价格

表：Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store1      | int     |
| store2      | int     |
| store3      | int     |
+-------------+---------+
在 SQL 中，这张表的主键是 product_id（产品Id）。
每行存储了这一产品在不同商店 store1, store2, store3 的价格。
如果这一产品在商店里没有出售，则值将为 null。

请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。

输出结果表中的 顺序不作要求 。

查询输出格式请参考下面示例。

示例 1：

输入：
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+
输出：
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+
解释：
产品 0 在 store1、store2、store3 的价格分别为 95、100、105。
产品 1 在 store1、store3 的价格分别为 70、80。在 store2 无法买到。

解析(列转行)

这是一道列转行问题，解题思路如下：

• 一列一列处理：把原来的列名作为新列(store)的value，原来的列的value作为另一个新列(price)的value。
• 将所有结果union all合并
• 值为null的需要跳过

select product_id, 'store1' store, store1 price
from Products
where store1 is not null
union all
select product_id, 'store2' store, store2 price
from Products
where store2 is not null
union all
select product_id, 'store3' store, store3 price
from Products
where store3 is not null;

这里新列'store1' store等需要加引号，因为这是要把常量值为'store1'的作为store列，如果不加引号，则是认为store1这列的值作为store列。

1251.平均售价

表：Prices

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| start_date    | date    |
| end_date      | date    |
| price         | int     |
+---------------+---------+
(product_id，start_date，end_date) 是 prices 表的主键（具有唯一值的列的组合）。
prices 表的每一行表示的是某个产品在一段时期内的价格。
每个产品的对应时间段是不会重叠的，这也意味着同一个产品的价格时段不会出现交叉。

表：UnitsSold

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| purchase_date | date    |
| units         | int     |
+---------------+---------+
该表可能包含重复数据。
该表的每一行表示的是每种产品的出售日期，单位和产品 id。

编写解决方案以查找每种产品的平均售价。average_price 应该 四舍五入到小数点后两位。

返回结果表 无顺序要求 。

结果格式如下例所示。

示例 1：

输入：
Prices table:
+------------+------------+------------+--------+
| product_id | start_date | end_date   | price  |
+------------+------------+------------+--------+
| 1          | 2019-02-17 | 2019-02-28 | 5      |
| 1          | 2019-03-01 | 2019-03-22 | 20     |
| 2          | 2019-02-01 | 2019-02-20 | 15     |
| 2          | 2019-02-21 | 2019-03-31 | 30     |
+------------+------------+------------+--------+
UnitsSold table:
+------------+---------------+-------+
| product_id | purchase_date | units |
+------------+---------------+-------+
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |
+------------+---------------+-------+
输出：
+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+
解释：
平均售价 = 产品总价 / 销售的产品数量。
产品 1 的平均售价 = ((100 * 5)+(15 * 20) )/ 115 = 6.96
产品 2 的平均售价 = ((200 * 15)+(30 * 30) )/ 230 = 16.96

解析

这道题难点在于使用聚合函数将两个表的units和price进行相加再求平均值无法使用聚合函数对两个表操作，因此需要先利用product_id进行左外连接合并成一个表然后进行连接操作。使用左外连接的原因是产品可能没有卖出去，但是定价是存在的，所以保证每个产品都被查询到，需要使用左外连接。连接后需要满足条件，售卖的日期需要是在定价开始日期到结束日期之间的，左外连接是以左表为基础的全连接，会连接出来不满足条件的数据行，因此需要根据这个条件进行过滤，同时为了保证没有售卖的数据可以保留下来，需要再添加一个null的特殊处理。

一个测试用例，进行左外连接没有过滤的数据如下所示：

| product_id | start_date | end_date   | price | product_id | purchase_date | units |
| ---------- | ---------- | ---------- | ----- | ---------- | ------------- | ----- |
| 1          | 2019-02-17 | 2019-02-28 | 5     | 1          | 2019-03-01    | 15    |
| 1          | 2019-02-17 | 2019-02-28 | 5     | 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01 | 2019-03-22 | 20    | 1          | 2019-03-01    | 15    |
| 1          | 2019-03-01 | 2019-03-22 | 20    | 1          | 2019-02-25    | 100   |
| 2          | 2019-02-01 | 2019-02-20 | 15    | 2          | 2019-03-22    | 30    |
| 2          | 2019-02-01 | 2019-02-20 | 15    | 2          | 2019-02-10    | 200   |
| 2          | 2019-02-21 | 2019-03-31 | 30    | 2          | 2019-03-22    | 30    |
| 2          | 2019-02-21 | 2019-03-31 | 30    | 2          | 2019-02-10    | 200   |
| 3          | 2019-02-21 | 2019-03-31 | 30    | null       | null          | null  |

最后使用聚合函数进行求平均价格，注意的是如果该产品没有卖出去，在连接后会出现null的情况，可以使用ifnull函数进行特殊判断。

select 
    p.product_id
    , round(ifnull(sum(units * price) / sum(units), 0), 2) average_price
from Prices p
left join UnitsSold u
on u.product_id = p.product_id
where u.purchase_date between p.start_date and p.end_date or u.purchase_date is null
group by p.product_id;

1280.学生们参加各科测试的次数

学生表: Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+
在 SQL 中，主键为 student_id（学生ID）。
该表内的每一行都记录有学校一名学生的信息。

科目表: Subjects

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
在 SQL 中，主键为 subject_name（科目名称）。
每一行记录学校的一门科目名称。

考试表: Examinations

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+
这个表可能包含重复数据（换句话说，在 SQL 中，这个表没有主键）。
学生表里的一个学生修读科目表里的每一门科目。
这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。

查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。

查询结构格式如下所示。

示例 1：

输入：
Students table:
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1          | Alice        |
| 2          | Bob          |
| 13         | John         |
| 6          | Alex         |
+------------+--------------+
Subjects table:
+--------------+
| subject_name |
+--------------+
| Math         |
| Physics      |
| Programming  |
+--------------+
Examinations table:
+------------+--------------+
| student_id | subject_name |
+------------+--------------+
| 1          | Math         |
| 1          | Physics      |
| 1          | Programming  |
| 2          | Programming  |
| 1          | Physics      |
| 1          | Math         |
| 13         | Math         |
| 13         | Programming  |
| 13         | Physics      |
| 2          | Math         |
| 1          | Math         |
+------------+--------------+
输出：
+------------+--------------+--------------+----------------+
| student_id | student_name | subject_name | attended_exams |
+------------+--------------+--------------+----------------+
| 1          | Alice        | Math         | 3              |
| 1          | Alice        | Physics      | 2              |
| 1          | Alice        | Programming  | 1              |
| 2          | Bob          | Math         | 1              |
| 2          | Bob          | Physics      | 0              |
| 2          | Bob          | Programming  | 1              |
| 6          | Alex         | Math         | 0              |
| 6          | Alex         | Physics      | 0              |
| 6          | Alex         | Programming  | 0              |
| 13         | John         | Math         | 1              |
| 13         | John         | Physics      | 1              |
| 13         | John         | Programming  | 1              |
+------------+--------------+--------------+----------------+
解释：
结果表需包含所有学生和所有科目（即便测试次数为0）：
Alice 参加了 3 次数学测试, 2 次物理测试，以及 1 次编程测试；
Bob 参加了 1 次数学测试, 1 次编程测试，没有参加物理测试；
Alex 啥测试都没参加；
John  参加了数学、物理、编程测试各 1 次。

解析

第一次做这道题，因为样例输出学生没有参加的科目的考试输出次数为0，因此首先想到了使用左外连接，让Students是左表，然后连接另外两个表，但是这只能保证找到所有学生，并不能找到所有学生和所有科目。因此需要转变思路。

要保证有所有学生的所有科目，而Students和Subjects表是完整的学生和科目数据，因此可以使用交叉连接获得所有学生的考所有科目的信息，然后再从Examinations表中获取每个学生考每科的次数，进行连接即可。

select 
    stu.student_id
    , stu.student_name
    , sub.subject_name
    , ifnull(exams.attended_exams, 0) attended_exams
from Students stu
cross join Subjects sub
left join (
    select student_id, subject_name, count(*) attended_exams
    from Examinations
    group by student_id, subject_name
) exams
on stu.student_id = exams.student_id
and sub.subject_name = exams.subject_name
order by stu.student_id, sub.subject_name;

参考了其他人的题解后，对代码进行了修改。

select 
    stu.student_id
    , stu.student_name
    , sub.subject_name
    , count(exams.subject_name) attended_exams
from Students stu
cross join Subjects sub
left join Examinations exams
on stu.student_id = exams.student_id
and sub.subject_name = exams.subject_name
group by stu.student_id, sub.subject_name
order by stu.student_id, sub.subject_name;

1484.按日期分组销售产品

表 Activities：

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+
该表没有主键(具有唯一值的列)。它可能包含重复项。
此表的每一行都包含产品名称和在市场上销售的日期。

编写解决方案找出每个日期、销售的不同产品的数量及其名称。 每个日期的销售产品名称应按词典序排列。 返回按 sell_date 排序的结果表。 结果表结果格式如下例所示。

示例 1:

输入：
Activities 表：
+------------+-------------+
| sell_date  | product     |
+------------+-------------+
| 2020-05-30 | Headphone   |
| 2020-06-01 | Pencil      |
| 2020-06-02 | Mask        |
| 2020-05-30 | Basketball  |
| 2020-06-01 | Bible       |
| 2020-06-02 | Mask        |
| 2020-05-30 | T-Shirt     |
+------------+-------------+
输出：
+------------+----------+------------------------------+
| sell_date  | num_sold | products                     |
+------------+----------+------------------------------+
| 2020-05-30 | 3        | Basketball,Headphone,T-shirt |
| 2020-06-01 | 2        | Bible,Pencil                 |
| 2020-06-02 | 1        | Mask                         |
+------------+----------+------------------------------+
解释：
对于2020-05-30，出售的物品是 (Headphone, Basketball, T-shirt)，按词典序排列，并用逗号 ',' 分隔。
对于2020-06-01，出售的物品是 (Pencil, Bible)，按词典序排列，并用逗号分隔。
对于2020-06-02，出售的物品是 (Mask)，只需返回该物品名。

解析(group_concat函数)

这道题难点在于group_concat函数的使用。参考了博客

这个函数的语法范式：group_concat([distinct] 需要连接的字段名 [order by 排序字段 desc/asc] [separator '分隔符'])

select 
    sell_date
    , count(distinct product) num_sold
    , group_concat(
        distinct product
        order by product asc
        separator ','
    ) products
from Activities
group by sell_date;

1517.查找拥有有效邮箱的用户

表: Users

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
| mail          | varchar |
+---------------+---------+
user_id 是该表的主键（具有唯一值的列）。
该表包含了网站已注册用户的信息。有一些电子邮件是无效的。

编写一个解决方案，以查找具有有效电子邮件的用户。

一个有效的电子邮件具有前缀名称和域，其中：

1. 前缀 名称是一个字符串，可以包含字母（大写或小写），数字，下划线 '_' ，点 '.' 和/或破折号 '-' 。前缀名称 必须 以字母开头。
2. 域 为 '@leetcode.com' 。

以任何顺序返回结果表。

结果的格式如以下示例所示：

示例 1：

输入：
Users 表:
+---------+-----------+-------------------------+
| user_id | name      | mail                    |
+---------+-----------+-------------------------+
| 1       | Winston   | winston@leetcode.com    |
| 2       | Jonathan  | jonathanisgreat         |
| 3       | Annabelle | bella-@leetcode.com     |
| 4       | Sally     | sally.come@leetcode.com |
| 5       | Marwan    | quarz#2020@leetcode.com |
| 6       | David     | david69@gmail.com       |
| 7       | Shapiro   | .shapo@leetcode.com     |
+---------+-----------+-------------------------+
输出：
+---------+-----------+-------------------------+
| user_id | name      | mail                    |
+---------+-----------+-------------------------+
| 1       | Winston   | winston@leetcode.com    |
| 3       | Annabelle | bella-@leetcode.com     |
| 4       | Sally     | sally.come@leetcode.com |
+---------+-----------+-------------------------+
解释：
用户 2 的电子邮件没有域。 
用户 5 的电子邮件带有不允许的 '#' 符号。
用户 6 的电子邮件没有 leetcode 域。 
用户 7 的电子邮件以点开头。

解析(正则表达式的应用)

^ 表示以后面的字符为开头 [] 表示括号内任意字符

- 表示连续

* 表示重复前面任意字符任意次数 \ 用来转义后面的特殊字符，以表示字符原本的样子，而不是将其作为特殊字符使用 $ 表示以前面的字符为结尾

因此，前缀必须以字母开头可以表示^[a-zA-Z]，前缀名包含若干数字、下划线、句点、横杠[a-zA-Z0-9_\.\-]*，以@leetcode.com结尾则是\@leetcode\.com$。因为. - @属于特殊字符，要使用本身的时候需要加\转义。

select *
from Users
where mail regexp '^[a-zA-Z][a-zA-Z0-9_\\.\\-]*\\@leetcode\\.com$';

正则表达式中使用\.进行转义，而在MySQL中需要对\进行转义，因此需要再加一个\。

1527.患某种疾病的患者

患者信息表： Patients

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| patient_id   | int     |
| patient_name | varchar |
| conditions   | varchar |
+--------------+---------+
在 SQL 中，patient_id （患者 ID）是该表的主键。
'conditions' （疾病）包含 0 个或以上的疾病代码，以空格分隔。
这个表包含医院中患者的信息。

查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1 。

按 任意顺序 返回结果表。

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

示例 1:

输入：
Patients表：
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 1          | Daniel       | YFEV COUGH   |
| 2          | Alice        |              |
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 |
| 5          | Alain        | DIAB201      |
+------------+--------------+--------------+
输出：
+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 | 
+------------+--------------+--------------+
解释：Bob 和 George 都患有代码以 DIAB1 开头的疾病。

解析(like)

题目很简单，难点在于如何匹配conditions中的字符串。在每一个疾病代码中，要有以DIAB1开头的才可以，而不是包含这个子字符串。因此可以用两种情况考虑：

1. 排在第一个疾病代码中，所以使用like匹配是like 'DIAB1%'
2. 排在非第一个疾病代码中，所以使用like匹配是like '% DIAB1%'，因为每一个疾病代码使用空格分开，所以保证以该字符串开头，需要在前边加上空格。

select *
from Patients
where conditions like 'DIAB1%' or conditions like '% DIAB1%';

方法二：

可以使用正则表达式进行匹配，正则表达式：^DIAB1|\sDIAB1

select *
from Patients
where conditions regexp '^DIAB1|\\sDIAB1';

1661.每台机器的进程平均运行时间

表: Activity

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| machine_id     | int     |
| process_id     | int     |
| activity_type  | enum    |
| timestamp      | float   |
+----------------+---------+
该表展示了一家工厂网站的用户活动。
(machine_id, process_id, activity_type) 是当前表的主键（具有唯一值的列的组合）。
machine_id 是一台机器的ID号。
process_id 是运行在各机器上的进程ID号。
activity_type 是枚举类型 ('start', 'end')。
timestamp 是浮点类型,代表当前时间(以秒为单位)。
'start' 代表该进程在这台机器上的开始运行时间戳 , 'end' 代表该进程在这台机器上的终止运行时间戳。
同一台机器，同一个进程都有一对开始时间戳和结束时间戳，而且开始时间戳永远在结束时间戳前面。

现在有一个工厂网站由几台机器运行，每台机器上运行着 相同数量的进程 。编写解决方案，计算每台机器各自完成一个进程任务的平均耗时。

完成一个进程任务的时间指进程的'end' 时间戳 减去 'start' 时间戳。平均耗时通过计算每台机器上所有进程任务的总耗费时间除以机器上的总进程数量获得。

结果表必须包含machine_id（机器ID） 和对应的 average time（平均耗时） 别名 processing_time，且四舍五入保留3位小数。

以 任意顺序 返回表。

具体参考例子如下。

示例 1:

输入：
Activity table:
+------------+------------+---------------+-----------+
| machine_id | process_id | activity_type | timestamp |
+------------+------------+---------------+-----------+
| 0          | 0          | start         | 0.712     |
| 0          | 0          | end           | 1.520     |
| 0          | 1          | start         | 3.140     |
| 0          | 1          | end           | 4.120     |
| 1          | 0          | start         | 0.550     |
| 1          | 0          | end           | 1.550     |
| 1          | 1          | start         | 0.430     |
| 1          | 1          | end           | 1.420     |
| 2          | 0          | start         | 4.100     |
| 2          | 0          | end           | 4.512     |
| 2          | 1          | start         | 2.500     |
| 2          | 1          | end           | 5.000     |
+------------+------------+---------------+-----------+
输出：
+------------+-----------------+
| machine_id | processing_time |
+------------+-----------------+
| 0          | 0.894           |
| 1          | 0.995           |
| 2          | 1.456           |
+------------+-----------------+
解释：
一共有3台机器,每台机器运行着两个进程.
机器 0 的平均耗时: ((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894
机器 1 的平均耗时: ((1.550 - 0.550) + (1.420 - 0.430)) / 2 = 0.995
机器 2 的平均耗时: ((4.512 - 4.100) + (5.000 - 2.500)) / 2 = 1.456

解析

题目很容易看懂，难点在于如何找到每个进程的运行时间，也就是某个machine_id下的某个process_id的结束时间戳减开始时间戳。原来的表这两个值是在同一列，无法直接减，需要通过操作将这两个值放在同一行进行操作。

可以使用自连接操作，在连接时对连接条件进行判断，手动指定一个表留下开始时间戳，另一个表留下结束时间戳，然后根据machine_id分组，最后计算平均值即可。

select a1.machine_id, round(avg(a2.timestamp - a1.timestamp), 3) processing_time
from Activity a1
inner join Activity a2
on a1.machine_id = a2.machine_id
and a1.process_id = a2.process_id
and a1.activity_type = 'start'
and a2.activity_type = 'end'
group by a1.machine_id;

使用avg函数可以直接计算平均值。他会遍历一个分组的所有的行，每行执行的操作是a2.timestamp - a1.timestamp，最后计算平均值。如有不懂可以再看一下1179题。

1789.员工的直属部门

表：Employee

+---------------+---------+
| Column Name   |  Type   |
+---------------+---------+
| employee_id   | int     |
| department_id | int     |
| primary_flag  | varchar |
+---------------+---------+
这张表的主键为 employee_id, department_id (具有唯一值的列的组合)
employee_id 是员工的ID
department_id 是部门的ID，表示员工与该部门有关系
primary_flag 是一个枚举类型，值分别为('Y', 'N'). 如果值为'Y',表示该部门是员工的直属部门。 如果值是'N',则否

一个员工可以属于多个部门。当一个员工加入超过一个部门的时候，他需要决定哪个部门是他的直属部门。请注意，当员工只加入一个部门的时候，那这个部门将默认为他的直属部门，虽然表记录的值为'N'.

请编写解决方案，查出员工所属的直属部门。

返回结果 没有顺序要求 。

返回结果格式如下例子所示：

示例 1：

输入：
Employee table:
+-------------+---------------+--------------+
| employee_id | department_id | primary_flag |
+-------------+---------------+--------------+
| 1           | 1             | N            |
| 2           | 1             | Y            |
| 2           | 2             | N            |
| 3           | 3             | N            |
| 4           | 2             | N            |
| 4           | 3             | Y            |
| 4           | 4             | N            |
+-------------+---------------+--------------+
输出：
+-------------+---------------+
| employee_id | department_id |
+-------------+---------------+
| 1           | 1             |
| 2           | 1             |
| 3           | 3             |
| 4           | 3             |
+-------------+---------------+
解释：
- 员工 1 的直属部门是 1
- 员工 2 的直属部门是 1
- 员工 3 的直属部门是 3
- 员工 4 的直属部门是 3

解析(union)

这道题分析可以了解到，一个员工的直属部门分为两种情况：

• 只有一个部门的，无论primary_flag是Y还是N，直属部门就是department_id。
• 有多个部门的，当primary_flag是Y的为直属部门。

因此可以使用union合并查询结果，第一次查询部门只有一个的员工，那么他的直属部门是确定的；第二次查询primary_key是Y的，那么他的直属部门也是确定的了。如果部门只有一个的员工中的primary_flag是Y，那么第二次查询也会查到这个数据，但是通过union操作会去除重复数据，因此不会影响最终结果。

select employee_id, department_id
from Employee
group by employee_id
having count(*) = 1
union
select employee_id, department_id
from Employee
where primary_flag = 'Y';

看题解了解到也可以使用窗口函数解答，但是目前没有学过窗口函数，先埋个坑🕳。

180.连续出现的数字

表：Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
在 SQL 中，id 是该表的主键。
id 是一个自增列。

找出所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

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

示例 1:

输入：
Logs 表：
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
输出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
解释：1 是唯一连续出现至少三次的数字。

解析(连续n问题，窗口函数)

窗口函数的内容可以参考这篇博客。

连续n问题解题思路首先是利用窗口函数构造一个新的列，给每个num编号。

select 
	*
	, row_number() over (partition by num order by id) id1
from Logs

上述查询会构建一个新的列，按照num分组，id升序的顺序重新编号。

| id | num | id1 |
| -- | --- | --- |
| 1  | 1   | 1   |
| 2  | 1   | 2   |
| 3  | 1   | 3   |
| 5  | 1   | 4   |
| 4  | 2   | 1   |
| 6  | 2   | 2   |
| 7  | 2   | 3   |

观察id - id1的值，可以发现，连续相同的数的这个差值是相同的。但是仍然会有问题，题目中id为自增主键，自增主键不一定完全连续，如果中间断开，则上述规律可能会不成立。因此可以再利用窗口函数row_number()构造一个新的列，使其完全连续。

select 
    *
    , row_number() over (partition by num order by id) id1
    , row_number() over (order by id) id2
from Logs

第二次窗口函数构建的列没有进行划分分组，直接按照id升序进行编号。即使id从中间某个数断开，id2也并不会断开。因此id2 - id1的差值满足以上规律。

| id | num | id1 | id2 |
| -- | --- | --- | --- |
| 1  | 1   | 1   | 1   |
| 2  | 1   | 2   | 2   |
| 3  | 1   | 3   | 3   |
| 4  | 2   | 1   | 4   |
| 5  | 1   | 4   | 5   |
| 6  | 2   | 2   | 6   |
| 7  | 2   | 3   | 7   |

实际测试中，如果只按照id2 - id1进行分组，仍然会出问题。

如以下数据：

+----+-----+-----+-----+
| id | num | id1 | id2 |
+----+-----+-----+-----+
| 1  | 1   | 1   | 1   |
| 2  | 2   | 1   | 2   |
| 3  | 1   | 2   | 3   |
| 4  | 1   | 3   | 4   |
+----+-----+-----+-----+

因为窗口函数按照num分组后，即使id = 1 3 4的数据从中间断开，但是编号并没有断开，导致id = 2 3 4的数并不相同连续，但是id2 - id1却是相同的。因此最终分组时要按照id2 - id1和num分组，即使差值相同，但是num不同，仍然可以区分出来。

最终的sql为

select distinct num ConsecutiveNums
from (
    select 
    *
    , row_number() over (partition by num order by id) id1
    , row_number() over (order by id) id2
    from Logs
) tmp_logs
group by id2 - id1, num
having count(*) >= 3;

最后查询结果需要使用distinct因为后边的连续的数字中可能会有和前边的相同的，结果只要有一个数字即可，因此需要去重。

626.换座位

表: Seat

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| student     | varchar |
+-------------+---------+
id 是该表的主键（唯一值）列。
该表的每一行都表示学生的姓名和 ID。
id 是一个连续的增量。

编写解决方案来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。

按 id 升序 返回结果表。

查询结果格式如下所示。

示例 1:

输入: 
Seat 表:
+----+---------+
| 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为连续上升的，所以很容易想到将偶数的id减一，将奇数的id加一，然后合并结果。但是这样会导致奇数人数情况下最后一个学生座位发生变化，因此最后套一层窗口函数进行一次排名即可。

select
    row_number() over (order by id) id
    , student
from (
    select
        id + 1 id
        , student
    from Seat
    where mod(id, 2) = 1
    union
    select
        id - 1 id
        , student
    from Seat
    where mod(id, 2) = 0
) t
order by id;

在看了别人提交的代码后，学习到了一种更为巧妙的方法。

SELECT 
    RANK() OVER (ORDER BY (id - 1) ^ 1) AS id,
    student
FROM seat

题目中id连续，所以交换位置可以看作是1和2 3和4...交换，也就是将两个的id互换。这里运用了位运算，巧妙地实现了这个操作。

首先让id - 1，所以id是从0, 1, 2开始，然后和1进行异或操作。

0 = 0b00000000 ^ 0b00000001 = 0b00000001 = 1

1 = 0b00000001 ^ 0b00000001 = 0b00000000 = 0

2 = 0b00000010 ^ 0b00000001 = 0b00000011 = 3

3 = 0b00000011 ^ 0b00000001 = 0b00000010 = 2

巧妙地将两个相邻的数对换，最后再使用窗口函数进行一次排名即可。

一个奇数与1做异或后得到的是比它小的相邻偶数，一个偶数与1做异或后得到的是比它大的相邻奇数。

# Write your MySQL query statement below
SELECT s1.id, COALESCE(s2.student, s1.student) student
FROM Seat s1
LEFT JOIN Seat s2
ON s1.id - 1 = (s2.id - 1) ^ 1
ORDER BY s1.id

使用上述规律做连接，将每个座位的匹配到交换后的位置，这里让字段先减1再做位运算，因为上述规律是从0开始算，这里是从1开始。

1070.产品销售分析III

销售表 Sales：

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) 是这张表的主键（具有唯一值的列的组合）。
product_id 是产品表的外键（reference 列）。
这张表的每一行都表示：编号 product_id 的产品在某一年的销售额。
请注意，价格是按每单位计的。

产品表 Product：

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id 是这张表的主键（具有唯一值的列）。
这张表的每一行都标识：每个产品的 id 和 产品名称。

编写解决方案，选出每个售出过的产品 第一年 销售的 产品 id、年份、数量 和 价格。

结果表中的条目可以按 任意顺序 排列。

结果格式如下例所示：

示例 1：

输入：
Sales 表：
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+
Product 表：
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+
输出：
+------------+------------+----------+-------+
| product_id | first_year | quantity | price |
+------------+------------+----------+-------+ 
| 100        | 2008       | 10       | 5000  |
| 200        | 2011       | 15       | 9000  |
+------------+------------+----------+-------+

解析(row_number()和rank()的异同)

这道题要找产品第一年的销售情况，可以利用窗口函数，给每个产品按照年份分组，然后进行排序。注意这里要使用rank()而不是row_number()。

rank函数进行order by排序时，遇到相同值排名不会变化，直到遇到不同值排名会变化，并且会比上一个排名多重复值的长度。而row_number会一直递增。

这个题要查到的是每种第一年卖出的产品的情况，可能会存在某种产品第一年卖出去多次，这个也需要查出来。因此需要使用rank。

select product_id, year first_year, quantity, price
from (
    select
        *
        , rank() over (partition by product_id order by year) rownum 
    from Sales
) t
where rownum = 1;

1321.餐馆营业额变化增长

表: Customer

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| visited_on    | date    |
| amount        | int     |
+---------------+---------+
在 SQL 中，(customer_id, visited_on) 是该表的主键。
该表包含一家餐馆的顾客交易数据。
visited_on 表示 (customer_id) 的顾客在 visited_on 那天访问了餐馆。
amount 是一个顾客某一天的消费总额。

你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）。

计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值。average_amount 要 保留两位小数。

结果按 visited_on 升序排序。

返回结果格式的例子如下。

示例 1:

输入：
Customer 表:
+-------------+--------------+--------------+-------------+
| customer_id | name         | visited_on   | amount      |
+-------------+--------------+--------------+-------------+
| 1           | Jhon         | 2019-01-01   | 100         |
| 2           | Daniel       | 2019-01-02   | 110         |
| 3           | Jade         | 2019-01-03   | 120         |
| 4           | Khaled       | 2019-01-04   | 130         |
| 5           | Winston      | 2019-01-05   | 110         | 
| 6           | Elvis        | 2019-01-06   | 140         | 
| 7           | Anna         | 2019-01-07   | 150         |
| 8           | Maria        | 2019-01-08   | 80          |
| 9           | Jaze         | 2019-01-09   | 110         | 
| 1           | Jhon         | 2019-01-10   | 130         | 
| 3           | Jade         | 2019-01-10   | 150         | 
+-------------+--------------+--------------+-------------+
输出：
+--------------+--------------+----------------+
| visited_on   | amount       | average_amount |
+--------------+--------------+----------------+
| 2019-01-07   | 860          | 122.86         |
| 2019-01-08   | 840          | 120            |
| 2019-01-09   | 840          | 120            |
| 2019-01-10   | 1000         | 142.86         |
+--------------+--------------+----------------+
解释：
第一个七天消费平均值从 2019-01-01 到 2019-01-07 是restaurant-growth/restaurant-growth/ (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86
第二个七天消费平均值从 2019-01-02 到 2019-01-08 是 (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120
第三个七天消费平均值从 2019-01-03 到 2019-01-09 是 (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120
第四个七天消费平均值从 2019-01-04 到 2019-01-10 是 (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86

解析(窗口函数范围)

题目要求当前日期及前7天的营业额的平均值。很容易想到使用窗口函数，同时这道题也作为180题的补充

[操作] over (partition by <列名> order by <列名> rows <窗口滑动的数据范围>)

其中滑动范围用来限定要操作的数据的范围。

当前行 - current row
之前的行 - preceding
之后的行 - following
无界限 - unbounded
表示从前面的起点 - unbounded preceding
表示到后面的终点 - unbounded following

例：

取当前行和前五行：ROWS between 5 preceding and current row --共6行
取当前行和后五行：ROWS between current row and 5 following --共6行
取前五行和后五行：ROWS between 5 preceding and 5 folowing --共11行

因此最后本题查询如下：

select
    visited_on
    , sum_amount amount
    , round(sum_amount / 7, 2) average_amount
from (
    select 
        visited_on
        , sum(amount) over (order by visited_on rows 6 preceding) sum_amount
    from (
        select visited_on, sum(amount) amount
        from Customer
        group by visited_on
    ) t
) tt
where datediff(visited_on, (select min(visited_on) from Customer)) >= 6;

最内层查询首先根据日期分组，获取每天的销售总额，因为可能存在同一天有多个客户的情况。

然后对子查询进行窗口函数计算。计算从当前行和前六行的和，虽然前6行往前不足6行，但是依旧可以计算，有多少行计算多少行。从第7行开始，计算的就是完整的7天的营业额。最后取出满足条件的日期即可。

本题说明了每天都至少有一个顾客的订单，所以日期肯定是连续的。

也可以不适用窗口函数，采用自连接方法，参考题解。

1341.电影评分

表：Movies

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| movie_id      | int     |
| title         | varchar |
+---------------+---------+
movie_id 是这个表的主键(具有唯一值的列)。
title 是电影的名字。

表：Users

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
+---------------+---------+
user_id 是表的主键(具有唯一值的列)。

表：MovieRating

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| movie_id      | int     |
| user_id       | int     |
| rating        | int     |
| created_at    | date    |
+---------------+---------+
(movie_id, user_id) 是这个表的主键(具有唯一值的列的组合)。
这个表包含用户在其评论中对电影的评分 rating 。
created_at 是用户的点评日期。 

请你编写一个解决方案：

• 查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
• 查找在 February 2020 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。

字典序 ，即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。

返回结果格式如下例所示。

示例 1：

输入：
Movies 表：
+-------------+--------------+
| movie_id    |  title       |
+-------------+--------------+
| 1           | Avengers     |
| 2           | Frozen 2     |
| 3           | Joker        |
+-------------+--------------+
Users 表：
+-------------+--------------+
| user_id     |  name        |
+-------------+--------------+
| 1           | Daniel       |
| 2           | Monica       |
| 3           | Maria        |
| 4           | James        |
+-------------+--------------+
MovieRating 表：
+-------------+--------------+--------------+-------------+
| movie_id    | user_id      | rating       | created_at  |
+-------------+--------------+--------------+-------------+
| 1           | 1            | 3            | 2020-01-12  |
| 1           | 2            | 4            | 2020-02-11  |
| 1           | 3            | 2            | 2020-02-12  |
| 1           | 4            | 1            | 2020-01-01  |
| 2           | 1            | 5            | 2020-02-17  | 
| 2           | 2            | 2            | 2020-02-01  | 
| 2           | 3            | 2            | 2020-03-01  |
| 3           | 1            | 3            | 2020-02-22  | 
| 3           | 2            | 4            | 2020-02-25  | 
+-------------+--------------+--------------+-------------+
输出：
Result 表：
+--------------+
| results      |
+--------------+
| Daniel       |
| Frozen 2     |
+--------------+
解释：
Daniel 和 Monica 都点评了 3 部电影（"Avengers", "Frozen 2" 和 "Joker"） 但是 Daniel 字典序比较小。
Frozen 2 和 Joker 在 2 月的评分都是 3.5，但是 Frozen 2 的字典序比较小。

解析(union子句)

这道题思路很简单，两次查询，分别查出评分最多的人和评分最高的电影，最后使用union合并。

但是实际运行时会报错，原因是子句使用了order by limit等，使用这些后不能直接和union操作，因此需要使用括号将两个子句括起来再合并。

(
    select u.name results
    from MovieRating mr
    inner join Users u
    on mr.user_id = u.user_id
    group by mr.user_id
    order by count(*) desc, u.name asc
    limit 1
)
union all
(
    select m.title results
    from MovieRating mr
    inner join Movies m
    on mr.movie_id = m.movie_id
    where date_format(mr.created_at, '%Y-%m') = '2020-02'
    group by mr.movie_id
    order by avg(rating) desc, m.title asc
    limit 1
);

550. 游戏玩法分析IV

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
（player_id，event_date）是此表的主键（具有唯一值的列的组合）。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录，他在某一天使用某个设备注销之前登录并玩了很多游戏（可能是 0）。

编写解决方案，报告在首次登录的第二天再次登录的玩家的 比率，四舍五入到小数点后两位。换句话说，你需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

结果格式如下所示：

示例 1：

输入：
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1         | 2         | 2016-03-01 | 5            |
| 1         | 2         | 2016-03-02 | 6            |
| 2         | 3         | 2017-06-25 | 1            |
| 3         | 1         | 2016-03-02 | 0            |
| 3         | 4         | 2018-07-03 | 5            |
+-----------+-----------+------------+--------------+
输出：
+-----------+
| fraction  |
+-----------+
| 0.33      |
+-----------+
解释：
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33

解析

先求出每个玩家首次登录日期，然后进行左连接，判断每个玩家是否在第二天登录，如果未登录，则第二个表的字段会为null，依次可以判断最后的比率。

# Write your MySQL query statement below
SELECT ROUND(AVG(a.player_id IS NOT NULL), 2) fraction
FROM (
    SELECT player_id, MIN(event_date) event_date
    FROM Activity
    GROUP BY player_id
) t_login
LEFT JOIN Activity a
ON DATEDIFF(a.event_date, t_login.event_date) = 1
AND t_login.player_id = a.player_id;

1164.指定日期的产品价格

产品数据表: Products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+
(product_id, change_date) 是此表的主键（具有唯一值的列组合）。
这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。

编写一个解决方案，找出在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。

以 任意顺序 返回结果表。

结果格式如下例所示。

示例 1:

输入：
Products 表:
+------------+-----------+-------------+
| product_id | new_price | change_date |
+------------+-----------+-------------+
| 1          | 20        | 2019-08-14  |
| 2          | 50        | 2019-08-14  |
| 1          | 30        | 2019-08-15  |
| 1          | 35        | 2019-08-16  |
| 2          | 65        | 2019-08-17  |
| 3          | 20        | 2019-08-18  |
+------------+-----------+-------------+
输出：
+------------+-------+
| product_id | price |
+------------+-------+
| 2          | 50    |
| 1          | 35    |
| 3          | 10    |
+------------+-------+

解析

# Write your MySQL query statement below
SELECT product_id, IF(filter_date IS NULL, 10, new_price) price
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY filter_date DESC) rn
    FROM (
        SELECT
            *,
            IF(DATEDIFF('2019-08-16', change_date) >= 0, change_date, NULL) filter_date
        FROM Products
    ) T1
) T2
WHERE rn = 1;

首先排除不符合条件的日期，将其设置为NULL，然后使用窗口函数对其进行倒序排序加索引，这样就可以找到在规定日期之前的最近的日期，如果某个商品所有的日期都在规定日期之后，那么它的filter_date始终为NULL，进行排名后，无论哪个是1，都不会影响最终结果。

排序时，先按照不是NULL的值进行排序，然后其余为NULL的依次排列。

585. 2016年的投资

Insurance 表：

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| pid         | int   |
| tiv_2015    | float |
| tiv_2016    | float |
| lat         | float |
| lon         | float |
+-------------+-------+
pid 是这张表的主键(具有唯一值的列)。
表中的每一行都包含一条保险信息，其中：
pid 是投保人的投保编号。
tiv_2015 是该投保人在 2015 年的总投保金额，tiv_2016 是该投保人在 2016 年的总投保金额。
lat 是投保人所在城市的纬度。题目数据确保 lat 不为空。
lon 是投保人所在城市的经度。题目数据确保 lon 不为空。

编写解决方案报告 2016 年 (tiv_2016) 所有满足下述条件的投保人的投保金额之和：

• 他在 2015 年的投保额 (tiv_2015) 至少跟一个其他投保人在 2015 年的投保额相同。
• 他所在的城市必须与其他投保人都不同（也就是说 (lat, lon) 不能跟其他任何一个投保人完全相同）。

tiv_2016 四舍五入的 两位小数 。

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

示例 1：

输入：
Insurance 表：
+-----+----------+----------+-----+-----+
| pid | tiv_2015 | tiv_2016 | lat | lon |
+-----+----------+----------+-----+-----+
| 1   | 10       | 5        | 10  | 10  |
| 2   | 20       | 20       | 20  | 20  |
| 3   | 10       | 30       | 20  | 20  |
| 4   | 10       | 40       | 40  | 40  |
+-----+----------+----------+-----+-----+
输出：
+----------+
| tiv_2016 |
+----------+
| 45.00    |
+----------+
解释：
表中的第一条记录和最后一条记录都满足两个条件。
tiv_2015 值为 10 与第三条和第四条记录相同，且其位置是唯一的。

第二条记录不符合任何一个条件。其 tiv_2015 与其他投保人不同，并且位置与第三条记录相同，这也导致了第三条记录不符合题目要求。
因此，结果是第一条记录和最后一条记录的 tiv_2016 之和，即 45 。

解析

需要求的是满足两个条件的投保人的投保金额总和。

使用group by 和 count判断唯一和不唯一这两个条件。分别求出满足两个条件的信息，然后进行过滤。

# Write your MySQL query statement below
SELECT ROUND(SUM(tiv_2016), 2) tiv_2016
FROM Insurance
WHERE CONCAT(lat, lon) IN (
    SELECT CONCAT(lat, lon)
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1
)
AND tiv_2015 IN (
    SELECT tiv_2015
    FROM Insurance
    GROUP BY tiv_2015
    HAVING COUNT(*) > 1
)

❗注意：以下写法是错误的

# Write your MySQL query statement below
SELECT ROUND(SUM(tiv_2016), 2) tiv_2016
FROM (
    SELECT SUM(tiv_2016) tiv_2016
    FROM (
        SELECT *
        FROM Insurance
        GROUP BY lat, lon
        HAVING COUNT(*) = 1
    ) T1
    GROUP BY tiv_2015
    HAVING COUNT(*) > 1
) T2

该写法是先求出地理位置唯一的投保人，然后在地理位置唯一的人里判断tiv_2015这个是不是唯一的，这是错误的。

id	tiv_2015	lat, lon
1	1	1,2
2	1	1,3
3	1	1,3

如该数据，其中id=1是满足条件的。但是如果按照上述错误做法来做的话，首先排除掉了id=2,3，然后判断tiv_2015，会发现排除了id=2,3后没有重复，但实际上是有重复的。

该题本质上两个条件的比对是比对的在全局情况下是否唯一。而不是排除某些后剩下的数据里(不)唯一。