超全MySQL题（104道、含MySQL新特性解法）由浅入深、笔试必备！（第八部分92-104）

本文链接：https://blog.csdn.net/qq_48784015/article/details/108432724

92. 活跃用户

写一个 SQL 查询, 找到活跃用户的 id 和 name，活跃用户是指那些至少连续 5 天登录账户的用户。返回的结果表按照 id 排序.

展示数据：

+----+----------+
| id | name     |
+----+----------+
| 7  | Jonathan |
+----+----------+
id = 1 的用户 Winston 仅仅在不同的 2 天内登录了 2 次, 所以, Winston 不是活跃用户.
id = 7 的用户 Jonathon 在不同的 6 天内登录了 7 次, , 6 天中有 5 天是连续的, 所以, Jonathan 是活跃用户.

建表语句：

Create table If Not Exists 92_Accounts (id int, name varchar(10));
Create table If Not Exists 92_Logins (id int, login_date date);
Truncate table 92_Accounts;
insert into 92_Accounts (id, name) values ('1', 'Winston');
insert into 92_Accounts (id, name) values ('7', 'Jonathan');
Truncate table 92_Logins;
insert into 92_Logins (id, login_date) values ('7', '2020-05-30');
insert into 92_Logins (id, login_date) values ('1', '2020-05-30');
insert into 92_Logins (id, login_date) values ('7', '2020-05-31');
insert into 92_Logins (id, login_date) values ('7', '2020-06-01');
insert into 92_Logins (id, login_date) values ('7', '2020-06-02');
insert into 92_Logins (id, login_date) values ('7', '2020-06-02');
insert into 92_Logins (id, login_date) values ('7', '2020-06-03');
insert into 92_Logins (id, login_date) values ('1', '2020-06-07');
insert into 92_Logins (id, login_date) values ('7', '2020-06-10');

后续问题:
如果活跃用户是那些至少连续 n 天登录账户的用户, 你能否写出通用的解决方案?

select 
     t3.id,name
from 
    (select
           distinct id
    from 
          (select
                 id,
                 login_date,
                 lead(login_date,4,null) over(partition by id order by login_date) ld
           from 
               (select
                     id,
                     login_date 
                from 
                     Logins
                group by 
                     id,login_date)t1
           )t2
    where datediff(ld,login_date)=4 
    )t3
left join
      Accounts a
on 
      t3.id = a.id;

93. 矩形面积

写一个 SQL 语句, 报告由表中任意两点可以形成的所有可能的矩形.

结果表中的每一行包含三列 (p1, p2, area) 如下:

p1 和 p2 是矩形两个对角的 id 且 p1 < p2.
矩形的面积由列 area 表示.

请按照面积大小降序排列，如果面积相同的话, 则按照 p1 和 p2 升序对结果表排序

+----------+-------------+-------------+
| p1       | p2          | area        |
+----------+-------------+-------------+
| 2        | 3           | 6           |
| 1        | 2           | 2           |
+----------+-------------+-------------+

p1 应该小于 p2 并且面积大于 0.
p1 = 1 且 p2 = 2 时, 面积等于 |2-4| * |8-7| = 2.
p1 = 2 且 p2 = 3 时, 面积等于 |4-2| * |7-10| = 6.
p1 = 1 且 p2 = 3 时, 是不可能为矩形的, 因为面积等于 0.

Create table If Not Exists 93_Points (id int, x_value int, y_value int);
Truncate table 93_Points;
insert into 93_Points (id, x_value, y_value) values ('1', '2', '8');
insert into 93_Points (id, x_value, y_value) values ('2', '4', '7');
insert into 93_Points (id, x_value, y_value) values ('3', '2', '10');

最终SQL：

select a.id P1,b.id P2,abs(a.x_value-b.x_value)*abs(a.y_value-b.y_value) as area
from Points a,Points b
where a.id<b.id and a.x_value != b.x_value and a.y_value != b.y_value
order by area desc,P1 ,P2

94. 计算税后工资

写一条查询 SQL 来查找每个员工的税后工资

每个公司的税率计算依照以下规则

如果这个公司员工最高工资不到 1000 ，税率为 0%
如果这个公司员工最高工资在 1000 到 10000 之间，税率为 24%
如果这个公司员工最高工资大于 10000 ，税率为 49%

按任意顺序返回结果，税后工资结果取整

结果表格式如下例所示：

+------------+-------------+---------------+--------+
| company_id | employee_id | employee_name | salary |
+------------+-------------+---------------+--------+
| 1          | 1           | Tony          | 1020   |
| 1          | 2           | Pronub        | 10863  |
| 1          | 3           | Tyrrox        | 5508   |
| 2          | 1           | Pam           | 300    |
| 2          | 7           | Bassem        | 450    |
| 2          | 9           | Hermione      | 700    |
| 3          | 7           | Bocaben       | 76     |
| 3          | 2           | Ognjen        | 1672   |
| 3          | 13          | Nyancat       | 2508   |
| 3          | 15          | Morninngcat   | 5911   |
+------------+-------------+---------------+--------+
对于公司 1 ，最高工资是 21300 ，其每个员工的税率为 49%
对于公司 2 ，最高工资是 700 ，其每个员工税率为 0%
对于公司 3 ，最高工资是 7777 ，其每个员工税率是 24%
税后工资计算 = 工资 - ( 税率 / 100）*工资
对于上述案例，Morninngcat 的税后工资 = 7777 - 7777 * ( 24 / 100) = 7777 - 1866.48 = 5910.52 ，取整为 5911

Create table If Not Exists 94_Salaries (company_id int, employee_id int, employee_name varchar(13), salary int);
Truncate table 94_Salaries;
insert into 94_Salaries (company_id, employee_id, employee_name, salary) values ('1', '1', 'Tony', '2000');
insert into 94_Salaries (company_id, employee_id, employee_name, salary) values ('1', '2', 'Pronub', '21300');
insert into 94_Salaries (company_id, employee_id, employee_name, salary) values ('1', '3', 'Tyrrox', '10800');
insert into 94_Salaries (company_id, employee_id, employee_name, salary) values ('2', '1', 'Pam', '300');
insert into 94_Salaries (company_id, employee_id, employee_name, salary) values ('2', '7', 'Bassem', '450');
insert into 94_Salaries (company_id, employee_id, employee_name, salary) values ('2', '9', 'Hermione', '700');
insert into 94_Salaries (company_id, employee_id, employee_name, salary) values ('3', '7', 'Bocaben', '100');
insert into 94_Salaries (company_id, employee_id, employee_name, salary) values ('3', '2', 'Ognjen', '2200');
insert into 94_Salaries (company_id, employee_id, employee_name, salary) values ('3', '13', 'Nyancat', '3300');
insert into 94_Salaries (company_id, employee_id, employee_name, salary) values ('3', '15', 'Morninngcat', '7777');

最终SQL:

select 
     company_id,
     employee_id,
     employee_name,
     round(case
               when maxsalary<1000 then salary
               when maxsalary<10000 then salary*(1-0.24)
               else salary*(1-0.49)
           end ,0) salary
from
    (select 
          *,
          max(salary) over(partition by company_id ) maxsalary
    from Salaries )t1 ;

95. 周内每天的销售情况

写一个SQL语句，报告 周内每天 每个商品类别下订购了多少单位。返回结果表单 按商品类别排序 。

查询结果格式如下例所示

+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Category   | Monday    | Tuesday   | Wednesday | Thursday  | Friday    | Saturday  | Sunday    |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| Book       | 20        | 5         | 0         | 0         | 10        | 0         | 0         |
| Glasses    | 0         | 0         | 0         | 0         | 5         | 0         | 0         |
| Phone      | 0         | 0         | 5         | 1         | 0         | 0         | 10        |
| T-Shirt    | 0         | 0         | 0         | 0         | 0         | 0         | 0         |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
在周一(2020-06-01, 2020-06-08)，Book分类(ids: 1, 2)下，总共销售了20个单位(10 + 10)
在周二(2020-06-02)，Book分类(ids: 1, 2)下，总共销售了5个单位
在周三(2020-06-03)，Phone分类(ids: 3, 4)下，总共销售了5个单位
在周四(2020-06-04)，Phone分类(ids: 3, 4)下，总共销售了1个单位
在周五(2020-06-05)，Book分类(ids: 1, 2)下，总共销售了10个单位，Glasses分类(ids: 5)下，总共销售了5个单位
在周六, 没有商品销售
在周天(2020-06-14, 2020-06-21)，Phone分类(ids: 3, 4)下，总共销售了10个单位(5 + 5)
没有销售 T-Shirt 类别的商品

Create table If Not Exists 95_Orders (order_id int, customer_id int, order_date date, item_id varchar(30), quantity int);
Create table If Not Exists 95_Items (item_id varchar(30), item_name varchar(30), item_category varchar(30));
Truncate table 95_Orders;
insert into 95_Orders (order_id, customer_id, order_date, item_id, quantity) values ('1', '1', '2020-06-01', '1', '10');
insert into 95_Orders (order_id, customer_id, order_date, item_id, quantity) values ('2', '1', '2020-06-08', '2', '10');
insert into 95_Orders (order_id, customer_id, order_date, item_id, quantity) values ('3', '2', '2020-06-02', '1', '5');
insert into 95_Orders (order_id, customer_id, order_date, item_id, quantity) values ('4', '3', '2020-06-03', '3', '5');
insert into 95_Orders (order_id, customer_id, order_date, item_id, quantity) values ('5', '4', '2020-06-04', '4', '1');
insert into 95_Orders (order_id, customer_id, order_date, item_id, quantity) values ('6', '4', '2020-06-05', '5', '5');
insert into 95_Orders (order_id, customer_id, order_date, item_id, quantity) values ('7', '5', '2020-06-05', '1', '10');
insert into 95_Orders (order_id, customer_id, order_date, item_id, quantity) values ('8', '5', '2020-06-14', '4', '5');
insert into 95_Orders (order_id, customer_id, order_date, item_id, quantity) values ('9', '5', '2020-06-21', '3', '5');
Truncate table 95_Items;
insert into 95_Items (item_id, item_name, item_category) values ('1', 'LC Alg. Book', 'Book');
insert into 95_Items (item_id, item_name, item_category) values ('2', 'LC DB. Book', 'Book');
insert into 95_Items (item_id, item_name, item_category) values ('3', 'LC SmarthPhone', 'Phone');
insert into 95_Items (item_id, item_name, item_category) values ('4', 'LC Phone 2020', 'Phone');
insert into 95_Items (item_id, item_name, item_category) values ('5', 'LC SmartGlass', 'Glasses');
insert into 95_Items (item_id, item_name, item_category) values ('6', 'LC T-Shirt XL', 'T-shirt');

最终SQL：

select 
     item_category as category, 
     sum(case when num = 2 then quantity else 0 end) as Monday,
     sum(case when num = 3 then quantity else 0 end) as Tuesday,
     sum(case when num = 4 then quantity else 0 end) as Wednesday,
     sum(case when num = 5 then quantity else 0 end) as Thursday,
     sum(case when num = 6 then quantity else 0 end) as Friday,
     sum(case when num = 7 then quantity else 0 end) as Saturday,
     sum(case when num = 1 then quantity else 0 end) as Sunday
from
    (select
          item_category,
          quantity,
          dayofweek(order_date) as num
     from 
          95_items i
     left join
          95_orders o 
     on 
          i.item_id=o.item_id) t
group by 
     item_category
order by
     item_category;

96. 按日期分组销售产品

编写一个 SQL 查询来查找每个日期、销售的不同产品的数量及其名称。
每个日期的销售产品名称应按词典序排列。
返回按 sell_date 排序的结果表。

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

+------------+----------+------------------------------+
| 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)，只需返回该物品名。

建表语句：

Create table If Not Exists 96_Activities (sell_date date, product varchar(20));
Truncate table 96_Activities;
insert into 96_Activities (sell_date, product) values ('2020-05-30', 'Headphone');
insert into 96_Activities (sell_date, product) values ('2020-06-01', 'Pencil');
insert into 96_Activities (sell_date, product) values ('2020-06-02', 'Mask');
insert into 96_Activities (sell_date, product) values ('2020-05-30', 'Basketball');
insert into 96_Activities (sell_date, product) values ('2020-06-01', 'Bible');
insert into 96_Activities (sell_date, product) values ('2020-06-02', 'Mask');
insert into 96_Activities (sell_date, product) values ('2020-05-30', 'T-Shirt');

最终SQL：

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

97. 上月播放的儿童适宜电影

写一个 SQL 语句, 报告在 2020 年 6 月份播放的儿童适宜电影的去重电影名.返回的结果表单没有顺序要求.

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

+--------------+
| title        |
+--------------+
| Aladdin      |
+--------------+
"Leetcode Movie" 是儿童不宜的电影.
"Alg. for Kids" 不是电影.
"Database Sols" 不是电影
"Alladin" 是电影, 儿童适宜, 并且在 2020 年 6 月份播放.
"Cinderella" 不在 2020 年 6 月份播放.

Create table If Not Exists 97_TVProgram (program_date date, content_id int, channel varchar(30));
Create table If Not Exists 97_Content (content_id varchar(30), title varchar(30), Kids_content ENUM('Y', 'N'), content_type varchar(30));
Truncate table 97_TVProgram;
insert into 97_TVProgram (program_date, content_id, channel) values ('2020-06-10 08:00', '1', 'LC-Channel');
insert into 97_TVProgram (program_date, content_id, channel) values ('2020-05-11 12:00', '2', 'LC-Channel');
insert into 97_TVProgram (program_date, content_id, channel) values ('2020-05-12 12:00', '3', 'LC-Channel');
insert into 97_TVProgram (program_date, content_id, channel) values ('2020-05-13 14:00', '4', 'Disney Ch');
insert into 97_TVProgram (program_date, content_id, channel) values ('2020-06-18 14:00', '4', 'Disney Ch');
insert into 97_TVProgram (program_date, content_id, channel) values ('2020-07-15 16:00', '5', 'Disney Ch');
Truncate table 97_Content;
insert into 97_Content (content_id, title, Kids_content, content_type) values ('1', 'Leetcode Movie', 'N', 'Movies');
insert into 97_Content (content_id, title, Kids_content, content_type) values ('2', 'Alg. for Kids', 'Y', 'Series');
insert into 97_Content (content_id, title, Kids_content, content_type) values ('3', 'Database Sols', 'N', 'Series');
insert into 97_Content (content_id, title, Kids_content, content_type) values ('4', 'Aladdin', 'Y', 'Movies');
insert into 97_Content (content_id, title, Kids_content, content_type) values ('5', 'Cinderella', 'Y', 'Movies');

最终SQL：

select 
     distinct title   
from 
     97_TVProgram t 
left join
     97_Content c
on 
     t.content_id  = c.content_id 
where
     Kids_content ='Y' 
     and
     date_format(program_date ,'%Y-%m')='2020-06'
     and
     content_type='Movies';

98. 可以放心投资的国家

写一段 SQL, 找到所有该公司可以投资的国家（该国的平均通话时长要严格地大于全球平均通话时长）.返回的结果表没有顺序要求.

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

+----------+
| country  |
+----------+
| Peru     |
+----------+
国家Peru的平均通话时长是 (102 + 102 + 330 + 330 + 5 + 5) / 6 = 145.666667
国家Israel的平均通话时长是 (33 + 4 + 13 + 13 + 3 + 1 + 1 + 7) / 8 = 9.37500
国家Morocco的平均通话时长是 (33 + 4 + 59 + 59 + 3 + 7) / 6 = 27.5000 
全球平均通话时长 = (2 * (33 + 3 + 59 + 102 + 330 + 5 + 13 + 3 + 1 + 7)) / 20 = 55.70000
所以, Peru是唯一的平均通话时长大于全球平均通话时长的国家, 也是唯一的推荐投资的国家.

Create table If Not Exists 98_Person (id int, name varchar(15), phone_number varchar(11));
Create table If Not Exists 98_Country (name varchar(15), country_code varchar(3));
Create table If Not Exists 98_Calls (caller_id int, callee_id int, duration int);
Truncate table 98_Person;
insert into 98_Person (id, name, phone_number) values ('3', 'Jonathan', '051-1234567');
insert into 98_Person (id, name, phone_number) values ('12', 'Elvis', '051-7654321');
insert into 98_Person (id, name, phone_number) values ('1', 'Moncef', '212-1234567');
insert into 98_Person (id, name, phone_number) values ('2', 'Maroua', '212-6523651');
insert into 98_Person (id, name, phone_number) values ('7', 'Meir', '972-1234567');
insert into 98_Person (id, name, phone_number) values ('9', 'Rachel', '972-0011100');
Truncate table 98_Country;
insert into 98_Country (name, country_code) values ('Peru', '051');
insert into 98_Country (name, country_code) values ('Israel', '972');
insert into 98_Country (name, country_code) values ('Morocco', '212');
insert into 98_Country (name, country_code) values ('Germany', '049');
insert into 98_Country (name, country_code) values ('Ethiopia', '251');
Truncate table 98_Calls;
insert into 98_Calls (caller_id, callee_id, duration) values ('1', '9', '33');
insert into 98_Calls (caller_id, callee_id, duration) values ('2', '9', '4');
insert into 98_Calls (caller_id, callee_id, duration) values ('1', '2', '59');
insert into 98_Calls (caller_id, callee_id, duration) values ('3', '12', '102');
insert into 98_Calls (caller_id, callee_id, duration) values ('3', '12', '330');
insert into 98_Calls (caller_id, callee_id, duration) values ('12', '3', '5');
insert into 98_Calls (caller_id, callee_id, duration) values ('7', '9', '13');
insert into 98_Calls (caller_id, callee_id, duration) values ('7', '1', '3');
insert into 98_Calls (caller_id, callee_id, duration) values ('9', '7', '1');
insert into 98_Calls (caller_id, callee_id, duration) values ('1', '7', '7');

最终SQL：

-- 方法一
select 
     c2.name as country 
from 
     Calls c1,
     Person p,
     Country c2
where
    (p.id=c1.caller_id or p.id=c1.callee_id) 
     and c2.country_code=left(p.phone_number,3)
group by 
     c2.name 
having
     avg(duration)>(select avg(duration) from Calls)
     
-- 方法二
with people_country as
(
    select id, c.name country
    from Person p left join Country c
    on left(p.phone_number,3) = c.country_code
)

select country
from
(
    select country, avg(duration) avgtime
    from
    (
        select caller_id id, duration
        from Calls
        union all
        select callee_id, duration
        from Calls
    ) t left join people_country
    using(id)
    group by country
) temp
where avgtime > 
    (
        select avg(duration) avgtime
        from
        (
            select caller_id, duration
            from Calls
            union all
            select callee_id, duration
            from Calls
        ) t
    )

99. 消费者下单频率

写一个 SQL 语句, 报告消费者的 id 和名字, 其中消费者在 2020 年 6 月和 7 月, 每月至少花费了$100.结果表无顺序要求.

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

+--------------+------------+
| customer_id  | name       |  
+--------------+------------+
| 1            | Winston    |
+--------------+------------+ 
Winston 在2020年6月花费了$300(300 * 1), 在7月花费了$100(10 * 1 + 45 * 2).
Jonathan 在2020年6月花费了$600(300 * 2), 在7月花费了$20(2 * 10).
Moustafa 在2020年6月花费了$110 (10 * 2 + 45 * 2), 在7月花费了$0.

建表语句：

Create table If Not Exists 99_Customers (customer_id int, name varchar(30), country varchar(30));
Create table If Not Exists 99_Product (product_id int, description varchar(30), price int);
Create table If Not Exists 99_Orders (order_id int, customer_id int, product_id int, order_date date, quantity int);
Truncate table 99_Customers;
insert into 99_Customers (customer_id, name, country) values ('1', 'Winston', 'USA');
insert into 99_Customers (customer_id, name, country) values ('2', 'Jonathan', 'Peru');
insert into 99_Customers (customer_id, name, country) values ('3', 'Moustafa', 'Egypt');
Truncate table 99_Product;
insert into 99_Product (product_id, description, price) values ('10', 'LC Phone', '300');
insert into 99_Product (product_id, description, price) values ('20', 'LC T-Shirt', '10');
insert into 99_Product (product_id, description, price) values ('30', 'LC Book', '45');
insert into 99_Product (product_id, description, price) values ('40', 'LC Keychain', '2');
Truncate table 99_Orders;
insert into 99_Orders (order_id, customer_id, product_id, order_date, quantity) values ('1', '1', '10', '2020-06-10', '1');
insert into 99_Orders (order_id, customer_id, product_id, order_date, quantity) values ('2', '1', '20', '2020-07-01', '1');
insert into 99_Orders (order_id, customer_id, product_id, order_date, quantity) values ('3', '1', '30', '2020-07-08', '2');
insert into 99_Orders (order_id, customer_id, product_id, order_date, quantity) values ('4', '2', '10', '2020-06-15', '2');
insert into 99_Orders (order_id, customer_id, product_id, order_date, quantity) values ('5', '2', '40', '2020-07-01', '10');
insert into 99_Orders (order_id, customer_id, product_id, order_date, quantity) values ('6', '3', '20', '2020-06-24', '2');
insert into 99_Orders (order_id, customer_id, product_id, order_date, quantity) values ('7', '3', '30', '2020-06-25', '2');
insert into 99_Orders (order_id, customer_id, product_id, order_date, quantity) values ('9', '3', '30', '2020-05-08', '3');

最终SQL：

select
     customer_id,name
from 
     Customers
where
     customer_id in (select
                          customer_id
                     from
                         (select 
                                customer_id,
                                month(order_date) as month,
                                sum(quantity*price) as total
                          from 
                                Orders o 
                          left join
                                Product p
                          on 
                                o.product_id = p.product_id
                          where
                                month(order_date) = 6 or month(order_date)=7
                          group by
                                customer_id,month(order_date)
                          ) as t1
                    where 
                          total >=100
                    group by
                          customer_id
                    having
                          count(*)>=2 )

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

Write an SQL query to find the users who have valid emails.

A valid e-mail has a prefix name and a domain where:

The prefix name is a string that may contain letters (upper or lower case), digits, underscore '_', period '.' and/or dash '-'. The prefix name must start with a letter.
The domain is '@leetcode.com'.

Return the result table in any order.

The query result format is in the following example.

+---------+-----------+-------------------------+
| user_id | name      | mail                    |
+---------+-----------+-------------------------+
| 1       | Winston   | winston@leetcode.com    |
| 3       | Annabelle | bella-@leetcode.com     |
| 4       | Sally     | sally.come@leetcode.com |
+---------+-----------+-------------------------+
The mail of user 2 doesn't have a domain.
The mail of user 5 has # sign which is not allowed.
The mail of user 6 doesn't have leetcode domain.
The mail of user 7 starts with a period.

建表语句：

Create table If Not Exists 100_Users (user_id int, name varchar(30), mail varchar(50));
Truncate table 100_Users;
insert into 100_Users (user_id, name, mail) values ('1', 'Winston', 'winston@leetcode.com');
insert into 100_Users (user_id, name, mail) values ('2', 'Jonathan', 'jonathanisgreat');
insert into 100_Users (user_id, name, mail) values ('3', 'Annabelle', 'bella-@leetcode.com');
insert into 100_Users (user_id, name, mail) values ('4', 'Sally', 'sally.come@leetcode.com');
insert into 100_Users (user_id, name, mail) values ('5', 'Marwan', 'quarz#2020@leetcode.com');
insert into 100_Users (user_id, name, mail) values ('6', 'David', 'david69@gmail.com');
insert into 100_Users (user_id, name, mail) values ('7', 'Shapiro', '.shapo@leetcode.com');

最终SQL：

SELECT
      *
FROM
      100_Users
WHERE 
      mail REGEXP '^[a-zA-Z]+[\\w_\\.\\-]*@leetcode.com$'   
ORDER BY
      user_id;

101. 患某种疾病的患者

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

按任意顺序返回结果表。

+------------+--------------+--------------+
| patient_id | patient_name | conditions   |
+------------+--------------+--------------+
| 3          | Bob          | DIAB100 MYOP |
| 4          | George       | ACNE DIAB100 | 
+------------+--------------+--------------+
Bob and George both have a condition that starts with DIAB1.

Create table If Not Exists 101_Patients (patient_id int, patient_name varchar(30), conditions varchar(100));
Truncate table 101_Patients;
insert into 101_Patients (patient_id, patient_name, conditions) values ('1', 'Daniel', 'YFEV COUGH');
insert into 101_Patients (patient_id, patient_name, conditions) values ('2', 'Alice', '');
insert into 101_Patients (patient_id, patient_name, conditions) values ('3', 'Bob', 'DIAB100 MYOP');
insert into 101_Patients (patient_id, patient_name, conditions) values ('4', 'George', 'ACNE DIAB100');
insert into 101_Patients (patient_id, patient_name, conditions) values ('5', 'Alain', 'DIAB201');

最终SQL：

select
     patient_id,
     patient_name,
     conditions 
from 
     Patients
where
     conditions like '%DIAB1%'

102. 最近的三笔订单

写一个 SQL 语句，找到每个用户的最近三笔订单。如果用户的订单少于 3 笔，则返回他的全部订单。

返回的结果按照 customer_name 升序排列。如果排名有相同，则继续按照 customer_id 升序排列。如果排名还有相同，则继续按照 order_date 升序排列。

+---------------+-------------+----------+------------+
| customer_name | customer_id | order_id | order_date |
+---------------+-------------+----------+------------+
| Annabelle     | 3           | 7        | 2020-08-01 |
| Annabelle     | 3           | 3        | 2020-07-31 |
| Jonathan      | 2           | 9        | 2020-08-07 |
| Jonathan      | 2           | 6        | 2020-08-01 |
| Jonathan      | 2           | 2        | 2020-07-30 |
| Marwan        | 4           | 4        | 2020-07-29 |
| Winston       | 1           | 8        | 2020-08-03 |
| Winston       | 1           | 1        | 2020-07-31 |
| Winston       | 1           | 10       | 2020-07-15 |
+---------------+-------------+----------+------------+
Winston has 4 orders, we discard the order of "2020-06-10" because it is the oldest order.
Annabelle has only 2 orders, we return them.
Jonathan has exactly 3 orders.
Marwan ordered only one time.
We sort the result table by customer_name in ascending order, by customer_id in ascending order and by order_date in descending order in case of a tie.

Create table If Not Exists 102_Customers (customer_id int, name varchar(10));
Create table If Not Exists 102_Orders (order_id int, order_date date, customer_id int, cost int);
Truncate table 102_Customers;
insert into 102_Customers (customer_id, name) values ('1', 'Winston');
insert into 102_Customers (customer_id, name) values ('2', 'Jonathan');
insert into 102_Customers (customer_id, name) values ('3', 'Annabelle');
insert into 102_Customers (customer_id, name) values ('4', 'Marwan');
insert into 102_Customers (customer_id, name) values ('5', 'Khaled');
Truncate table 102_Orders;
insert into 102_Orders (order_id, order_date, customer_id, cost) values ('1', '2020-07-31', '1', '30');
insert into 102_Orders (order_id, order_date, customer_id, cost) values ('2', '2020-7-30', '2', '40');
insert into 102_Orders (order_id, order_date, customer_id, cost) values ('3', '2020-07-31', '3', '70');
insert into 102_Orders (order_id, order_date, customer_id, cost) values ('4', '2020-07-29', '4', '100');
insert into 102_Orders (order_id, order_date, customer_id, cost) values ('5', '2020-06-10', '1', '1010');
insert into 102_Orders (order_id, order_date, customer_id, cost) values ('6', '2020-08-01', '2', '102');
insert into 102_Orders (order_id, order_date, customer_id, cost) values ('7', '2020-08-01', '3', '111');
insert into 102_Orders (order_id, order_date, customer_id, cost) values ('8', '2020-08-03', '1', '99');
insert into 102_Orders (order_id, order_date, customer_id, cost) values ('9', '2020-08-07', '2', '32');
insert into 102_Orders (order_id, order_date, customer_id, cost) values ('10', '2020-07-15', '1', '2');

Follow-up:**
Can you write a general solution for the most recent n orders?

select 
     name customer_name, 
     customer_id,
     order_id,
     order_date
from
    (select 
          name ,
          o.customer_id,
          order_id,
          order_date ,
          rank() over(partition by o.customer_id order by order_date desc) rk
     from 
          102_Orders o 
     left join
          102_Customers c
     on
          o.customer_id=c.customer_id )t1
where rk <=3
order by customer_name ,customer_id,order_date desc;

103. 格式化产品名称

写一个 SQL 语句报告：

product_name 是小写字母且不包含前后空格

sale_date 格式为 (‘YYYY-MM’)

total 是产品在本月销售的次数

返回结果以 product_name 升序排列，如果有排名相同, 再以 sale_date 升序排列。

+--------------+--------------+----------+
| product_name | sale_date    | total    |
+--------------+--------------+----------+
| lcphone      | 2000-01      | 2        |
| lckeychain   | 2000-02      | 2        | 
| lcphone      | 2000-02      | 1        | 
| matryoshka   | 2000-03      | 1        | 
+--------------+--------------+----------+

In January, 2 LcPhones were sold, please note that the product names are not case sensitive and may contain spaces.
In Februery, 2 LCKeychains and 1 LCPhone were sold.
In March, 1 matryoshka was sold.

Create table If Not Exists 103_Sales (sale_id int, product_name varchar(30), sale_date date);
Truncate table 103_Sales;
insert into 103_Sales (sale_id, product_name, sale_date) values ('1', 'LCPHONE', '2000-01-16');
insert into 103_Sales (sale_id, product_name, sale_date) values ('2', 'LCPhone', '2000-01-17');
insert into 103_Sales (sale_id, product_name, sale_date) values ('3', 'LcPhOnE', '2000-02-18');
insert into 103_Sales (sale_id, product_name, sale_date) values ('4', 'LCKeyCHAiN', '2000-02-19');
insert into 103_Sales (sale_id, product_name, sale_date) values ('5', 'LCKeyChain', '2000-02-28');
insert into 103_Sales (sale_id, product_name, sale_date) values ('6', 'Matryoshka', '2000-03-31');

最终SQL：

select 
     trim(lower(product_name)) as product_name, 
     date_format(sale_date,'%Y-%m') as sale_date,
     count(*) as total 
from 
     103_Sales 
group by
     trim(lower(product_name)),
     date_format(sale_date,'%Y-%m') 
order by
     product_name asc, 
     sale_date asc;

104. The Most Recent Orders for Each Product

Write an SQL query to find the most recent order(s) of each product.

Return the result table sorted by product_name in ascending order and in case of a tie by the product_id in ascending order. If there still a tie, order them by the order_id in ascending order.

The query result format is in the following example:

+--------------+------------+----------+------------+
| product_name | product_id | order_id | order_date |
+--------------+------------+----------+------------+
| keyboard     | 1          | 6        | 2020-08-01 |
| keyboard     | 1          | 7        | 2020-08-01 |
| mouse        | 2          | 8        | 2020-08-03 |
| screen       | 3          | 3        | 2020-08-29 |
+--------------+------------+----------+------------+
keyboard's most recent order is in 2020-08-01, it was ordered two times this day.
mouse's most recent order is in 2020-08-03, it was ordered only once this day.
screen's most recent order is in 2020-08-29, it was ordered only once this day.
The hard disk was never ordered and we don't include it in the result table.

建表语句：

Create table If Not Exists 104_Customers (customer_id int, name varchar(10));
Create table If Not Exists 104_Orders (order_id int, order_date date, customer_id int, product_id int);
Create table If Not Exists 104_Products (product_id int, product_name varchar(20), price int);
Truncate table 104_Customers;
insert into 104_Customers (customer_id, name) values ('1', 'Winston');
insert into 104_Customers (customer_id, name) values ('2', 'Jonathan');
insert into 104_Customers (customer_id, name) values ('3', 'Annabelle');
insert into 104_Customers (customer_id, name) values ('4', 'Marwan');
insert into 104_Customers (customer_id, name) values ('5', 'Khaled');
Truncate table 104_Orders;
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('1', '2020-07-31', '1', '1');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('2', '2020-7-30', '2', '2');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('3', '2020-08-29', '3', '3');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('4', '2020-07-29', '4', '1');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('5', '2020-06-10', '1', '2');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('6', '2020-08-01', '2', '1');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('7', '2020-08-01', '3', '1');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('8', '2020-08-03', '1', '2');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('9', '2020-08-07', '2', '3');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('10', '2020-07-15', '1', '2');
Truncate table 104_Products;
insert into 104_Products (product_id, product_name, price) values ('1', 'keyboard', '120');
insert into 104_Products (product_id, product_name, price) values ('2', 'mouse', '80');
insert into 104_Products (product_id, product_name, price) values ('3', 'screen', '600');
insert into 104_Products (product_id, product_name, price) values ('4', 'hard disk', '450');

最终SQL：

select 
     product_name,
     product_id,
     order_id,
     order_date
from
    (select 
          product_name,
          o.product_id,
          order_id,
          order_date,
          rank() over(partition by o.product_id order by order_date desc) rk
     from
          104_Orders o 
     left join
          104_Products p
     on 
          o.product_id =p.product_id )t1
where
     rk =1
order by 
     product_name,
     product_id,
     order_id;

29’, ‘4’, ‘100’);
insert into 102_Orders (order_id, order_date, customer_id, cost) values (‘5’, ‘2020-06-10’, ‘1’, ‘1010’);
insert into 102_Orders (order_id, order_date, customer_id, cost) values (‘6’, ‘2020-08-01’, ‘2’, ‘102’);
insert into 102_Orders (order_id, order_date, customer_id, cost) values (‘7’, ‘2020-08-01’, ‘3’, ‘111’);
insert into 102_Orders (order_id, order_date, customer_id, cost) values (‘8’, ‘2020-08-03’, ‘1’, ‘99’);
insert into 102_Orders (order_id, order_date, customer_id, cost) values (‘9’, ‘2020-08-07’, ‘2’, ‘32’);
insert into 102_Orders (order_id, order_date, customer_id, cost) values (‘10’, ‘2020-07-15’, ‘1’, ‘2’);


Follow-up:**
Can you write a general solution for the most recent `n` orders?

```sql
select 
     name customer_name, 
     customer_id,
     order_id,
     order_date
from
    (select 
          name ,
          o.customer_id,
          order_id,
          order_date ,
          rank() over(partition by o.customer_id order by order_date desc) rk
     from 
          102_Orders o 
     left join
          102_Customers c
     on
          o.customer_id=c.customer_id )t1
where rk <=3
order by customer_name ,customer_id,order_date desc;

103. 格式化产品名称

写一个 SQL 语句报告：

product_name 是小写字母且不包含前后空格

sale_date 格式为 (‘YYYY-MM’)

total 是产品在本月销售的次数

返回结果以 product_name 升序排列，如果有排名相同, 再以 sale_date 升序排列。

+--------------+--------------+----------+
| product_name | sale_date    | total    |
+--------------+--------------+----------+
| lcphone      | 2000-01      | 2        |
| lckeychain   | 2000-02      | 2        | 
| lcphone      | 2000-02      | 1        | 
| matryoshka   | 2000-03      | 1        | 
+--------------+--------------+----------+

In January, 2 LcPhones were sold, please note that the product names are not case sensitive and may contain spaces.
In Februery, 2 LCKeychains and 1 LCPhone were sold.
In March, 1 matryoshka was sold.

Create table If Not Exists 103_Sales (sale_id int, product_name varchar(30), sale_date date);
Truncate table 103_Sales;
insert into 103_Sales (sale_id, product_name, sale_date) values ('1', 'LCPHONE', '2000-01-16');
insert into 103_Sales (sale_id, product_name, sale_date) values ('2', 'LCPhone', '2000-01-17');
insert into 103_Sales (sale_id, product_name, sale_date) values ('3', 'LcPhOnE', '2000-02-18');
insert into 103_Sales (sale_id, product_name, sale_date) values ('4', 'LCKeyCHAiN', '2000-02-19');
insert into 103_Sales (sale_id, product_name, sale_date) values ('5', 'LCKeyChain', '2000-02-28');
insert into 103_Sales (sale_id, product_name, sale_date) values ('6', 'Matryoshka', '2000-03-31');

最终SQL：

select 
     trim(lower(product_name)) as product_name, 
     date_format(sale_date,'%Y-%m') as sale_date,
     count(*) as total 
from 
     103_Sales 
group by
     trim(lower(product_name)),
     date_format(sale_date,'%Y-%m') 
order by
     product_name asc, 
     sale_date asc;

104. The Most Recent Orders for Each Product

Write an SQL query to find the most recent order(s) of each product.

The query result format is in the following example:

+--------------+------------+----------+------------+
| product_name | product_id | order_id | order_date |
+--------------+------------+----------+------------+
| keyboard     | 1          | 6        | 2020-08-01 |
| keyboard     | 1          | 7        | 2020-08-01 |
| mouse        | 2          | 8        | 2020-08-03 |
| screen       | 3          | 3        | 2020-08-29 |
+--------------+------------+----------+------------+
keyboard's most recent order is in 2020-08-01, it was ordered two times this day.
mouse's most recent order is in 2020-08-03, it was ordered only once this day.
screen's most recent order is in 2020-08-29, it was ordered only once this day.
The hard disk was never ordered and we don't include it in the result table.

建表语句：

Create table If Not Exists 104_Customers (customer_id int, name varchar(10));
Create table If Not Exists 104_Orders (order_id int, order_date date, customer_id int, product_id int);
Create table If Not Exists 104_Products (product_id int, product_name varchar(20), price int);
Truncate table 104_Customers;
insert into 104_Customers (customer_id, name) values ('1', 'Winston');
insert into 104_Customers (customer_id, name) values ('2', 'Jonathan');
insert into 104_Customers (customer_id, name) values ('3', 'Annabelle');
insert into 104_Customers (customer_id, name) values ('4', 'Marwan');
insert into 104_Customers (customer_id, name) values ('5', 'Khaled');
Truncate table 104_Orders;
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('1', '2020-07-31', '1', '1');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('2', '2020-7-30', '2', '2');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('3', '2020-08-29', '3', '3');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('4', '2020-07-29', '4', '1');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('5', '2020-06-10', '1', '2');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('6', '2020-08-01', '2', '1');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('7', '2020-08-01', '3', '1');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('8', '2020-08-03', '1', '2');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('9', '2020-08-07', '2', '3');
insert into 104_Orders (order_id, order_date, customer_id, product_id) values ('10', '2020-07-15', '1', '2');
Truncate table 104_Products;
insert into 104_Products (product_id, product_name, price) values ('1', 'keyboard', '120');
insert into 104_Products (product_id, product_name, price) values ('2', 'mouse', '80');
insert into 104_Products (product_id, product_name, price) values ('3', 'screen', '600');
insert into 104_Products (product_id, product_name, price) values ('4', 'hard disk', '450');

最终SQL：

select 
     product_name,
     product_id,
     order_id,
     order_date
from
    (select 
          product_name,
          o.product_id,
          order_id,
          order_date,
          rank() over(partition by o.product_id order by order_date desc) rk
     from
          104_Orders o 
     left join
          104_Products p
     on 
          o.product_id =p.product_id )t1
where
     rk =1
order by 
     product_name,
     product_id,
     order_id;