LeetCode数据库SQL练习（十二）

YBCarry_段松啓
327 阅读11分钟

周内每天的销售情况

表：Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| order_date    | date    | 
| item_id       | varchar |
| quantity      | int     |
+---------------+---------+
(order_id, item_id) 是该表主键
该表包含了订单信息
order_date 是id为 item_id 的商品被id为 customer_id 的消费者订购的日期.

表：Items

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| item_id             | varchar |
| item_name           | varchar |
| item_category       | varchar |
+---------------------+---------+
item_id 是该表主键
item_name 是商品的名字
item_category 是商品的类别

你是企业主，想要获得分类商品和周内每天的销售报告。
写一个SQL语句，报告 周内每天 每个商品类别下订购了多少单位。
返回结果表单 按商品类别排序 。

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

Orders 表：
+------------+--------------+-------------+--------------+-------------+
| order_id   | customer_id  | order_date  | item_id      | quantity    |
+------------+--------------+-------------+--------------+-------------+
| 1          | 1            | 2020-06-01  | 1            | 10          |
| 2          | 1            | 2020-06-08  | 2            | 10          |
| 3          | 2            | 2020-06-02  | 1            | 5           |
| 4          | 3            | 2020-06-03  | 3            | 5           |
| 5          | 4            | 2020-06-04  | 4            | 1           |
| 6          | 4            | 2020-06-05  | 5            | 5           |
| 7          | 5            | 2020-06-05  | 1            | 10          |
| 8          | 5            | 2020-06-14  | 4            | 5           |
| 9          | 5            | 2020-06-21  | 3            | 5           |
+------------+--------------+-------------+--------------+-------------+

Items 表：
+------------+----------------+---------------+
| item_id    | item_name      | item_category |
+------------+----------------+---------------+
| 1          | LC Alg. Book   | Book          |
| 2          | LC DB. Book    | Book          |
| 3          | LC SmarthPhone | Phone         |
| 4          | LC Phone 2020  | Phone         |
| 5          | LC SmartGlass  | Glasses       |
| 6          | LC T-Shirt XL  | T-Shirt       |
+------------+----------------+---------------+

Result 表：
+------------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 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 类别的商品

  • 日期转星期：

    • weekday(date)：
      • 返回一个日期的工作日索引值，即星期一为0，星期二为1，星期日为6。
      • 对于无效日期返回NULL。
    • date_format(date, '%W')

  • SQL：

    • select
item_category category,
sum(if(wd = 0, quantity, 0)) monday,
sum(if(wd = 1, quantity, 0)) tuesday,
sum(if(wd = 2, quantity, 0)) wednesday,
sum(if(wd = 3, quantity, 0)) thursday,
sum(if(wd = 4, quantity, 0)) friday,
sum(if(wd = 5, quantity, 0)) saturday,
sum(if(wd = 6, quantity, 0)) sunday
from
    (select
    *, weekday(order_date) wd
    from
    Orders o right join Items i
    using(item_id)) t
group by item_category
order by item_category

按日期分组销售产品

表 Activities：

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

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

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

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

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

字段拼接：

  • concat()：

    • 将多个字符串连接成一个字符串。
    • concat(str1, str2,...)
    • 返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

  • concat_ws()：

    • 将多个字符串连接成一个字符串，可以指定分隔符
    • concat_ws就是concat with separator
    • concat_ws(separator, str1, str2, ...)
    • 第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

  • group_concat()：

    • 将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
    • group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
    • 通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

  • SQL：

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

上月播放的儿童适宜电影

表: TVProgram

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| program_date  | date    |
| content_id    | int     |
| channel       | varchar |
+---------------+---------+
(program_date, content_id) 是该表主键.
该表包含电视上的节目信息.
content_id 是电视一些频道上的节目的 id.

表: Content

+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| content_id       | varchar |
| title            | varchar |
| Kids_content     | enum    |
| content_type     | varchar |
+------------------+---------+
content_id 是该表主键.
Kids_content 是枚举类型, 取值为('Y', 'N'), 其中: 
'Y' 表示儿童适宜内容, 而'N'表示儿童不宜内容.
content_type 表示内容的类型, 比如电影, 电视剧等.

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

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

TVProgram 表:
+--------------------+--------------+-------------+
| program_date       | content_id   | channel     |
+--------------------+--------------+-------------+
| 2020-06-10 08:00   | 1            | LC-Channel  |
| 2020-05-11 12:00   | 2            | LC-Channel  |
| 2020-05-12 12:00   | 3            | LC-Channel  |
| 2020-05-13 14:00   | 4            | Disney Ch   |
| 2020-06-18 14:00   | 4            | Disney Ch   |
| 2020-07-15 16:00   | 5            | Disney Ch   |
+--------------------+--------------+-------------+

Content 表:
+------------+----------------+---------------+---------------+
| content_id | title          | Kids_content  | content_type  |
+------------+----------------+---------------+---------------+
| 1          | Leetcode Movie | N             | Movies        |
| 2          | Alg. for Kids  | Y             | Series        |
| 3          | Database Sols  | N             | Series        |
| 4          | Aladdin        | Y             | Movies        |
| 5          | Cinderella     | Y             | Movies        |
+------------+----------------+---------------+---------------+

Result 表:
+--------------+
| title        |
+--------------+
| Aladdin      |
+--------------+
"Leetcode Movie" 是儿童不宜的电影.
"Alg. for Kids" 不是电影.
"Database Sols" 不是电影
"Alladin" 是电影, 儿童适宜, 并且在 2020 年 6 月份播放.
"Cinderella" 不在 2020 年 6 月份播放.
  • SQL： 
    • select
distinct t2.title
from
(select
content_id
from
TVProgram
where date_format(program_date, '%Y-%m') = '2020-06') t1
join
(select
content_id, title
from
Content
where Kids_content = 'Y' and content_type = 'Movies') t2
using(content_id)

可以放心投资的国家

表Person:

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| id             | int     |
| name           | varchar |
| phone_number   | varchar |
+----------------+---------+
id 是该表主键.
该表每一行包含一个人的名字和电话号码.
电话号码的格式是:'xxx-yyyyyyy', 其中xxx是国家码(3个字符), yyyyyyy是电话号码(7个字符), x和y都表示数字. 同时, 国家码和电话号码都可以包含前导0.

表Country:

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| name           | varchar |
| country_code   | varchar |
+----------------+---------+
country_code是该表主键.
该表每一行包含国家名和国家码. country_code的格式是'xxx', x是数字.

表Calls:

+-------------+------+
| Column Name | Type |
+-------------+------+
| caller_id   | int  |
| callee_id   | int  |
| duration    | int  |
+-------------+------+
该表无主键, 可能包含重复行.
每一行包含呼叫方id, 被呼叫方id和以分钟为单位的通话时长. caller_id != callee_id

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

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

Person 表:
+----+----------+--------------+
| id | name     | phone_number |
+----+----------+--------------+
| 3  | Jonathan | 051-1234567  |
| 12 | Elvis    | 051-7654321  |
| 1  | Moncef   | 212-1234567  |
| 2  | Maroua   | 212-6523651  |
| 7  | Meir     | 972-1234567  |
| 9  | Rachel   | 972-0011100  |
+----+----------+--------------+

Country 表:
+----------+--------------+
| name     | country_code |
+----------+--------------+
| Peru     | 051          |
| Israel   | 972          |
| Morocco  | 212          |
| Germany  | 049          |
| Ethiopia | 251          |
+----------+--------------+

Calls 表:
+-----------+-----------+----------+
| caller_id | callee_id | duration |
+-----------+-----------+----------+
| 1         | 9         | 33       |
| 2         | 9         | 4        |
| 1         | 2         | 59       |
| 3         | 12        | 102      |
| 3         | 12        | 330      |
| 12        | 3         | 5        |
| 7         | 9         | 13       |
| 7         | 1         | 3        |
| 9         | 7         | 1        |
| 1         | 7         | 7        |
+-----------+-----------+----------+

Result 表:
+----------+
| 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 + 4 + 59 + 102 + 330 + 5 + 13 + 3 + 1 + 7)) / 20 = 55.70000
所以, Peru是唯一的平均通话时长大于全球平均通话时长的国家, 也是唯一的推荐投资的国家.

  • SQL1：

    • select
t4.name country
from
(select
distinct t3.name,
avg(t3.duration) over(partition by t3.name) avg,
avg(t3.duration) over() avgg
from
    ((select
    t2.name, c1.duration
    from
        (select
        id, c.name
        from
            (select
            id, LEFT(phone_number, 3) country_code
            from
            Person) t1
        join Country c
        using(country_code)) t2
    right join Calls c1
    on t2.id = c1.caller_id)
    union all
    (select
    t2.name, c2.duration
    from
        (select
        id, c.name
        from
            (select
            id, LEFT(phone_number, 3) country_code
            from
            Person) t1
        join Country c
        using(country_code)) t2
    right join Calls c2
    on t2.id = c2.callee_id)) t3) t4
where t4.avg > t4.avgg

  • SQL2：

    • select
c.name country
from
    (select
    avg(duration) avg_c ,LEFT(phone_number, 3) cid
    from
        (select
        caller_id id, duration
        from
        Calls
        union all
        select
        callee_id id, duration
        from
        Calls) t
    left join Person p
    using(id)
    group by cid) t1
left join country c on t1.cid = country_code
where t1.avg_c > (select
                  avg(duration)
                  from
                  Calls)

  • SQL3：

    • select
c.name country
from
Calls, Person, Country c
where (caller_id = id or callee_id = id)
      and country_code = LEFT(phone_number, 3)
group by country_code
having avg(duration) > (select
                        avg(duration)
                        from
                        Calls)

消费者下单频率

表: Customers

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
| country       | varchar |
+---------------+---------+
customer_id 是该表主键.
该表包含公司消费者的信息.

表: Product

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| description   | varchar |
| price         | int     |
+---------------+---------+
product_id 是该表主键.
该表包含公司产品的信息.
price 是本产品的花销.

表: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| customer_id   | int     |
| product_id    | int     |
| order_date    | date    |
| quantity      | int     |
+---------------+---------+
order_id 是该表主键.
该表包含消费者下单的信息.
customer_id 是买了数量为"quantity", id为"product_id"产品的消费者的 id.
Order_date 是订单发货的日期, 格式为('YYYY-MM-DD').

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

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

Customers
+--------------+-----------+-------------+
| customer_id  | name      | country     |
+--------------+-----------+-------------+
| 1            | Winston   | USA         |
| 2            | Jonathan  | Peru        |
| 3            | Moustafa  | Egypt       |
+--------------+-----------+-------------+

Product
+--------------+-------------+-------------+
| product_id   | description | price       |
+--------------+-------------+-------------+
| 10           | LC Phone    | 300         |
| 20           | LC T-Shirt  | 10          |
| 30           | LC Book     | 45          |
| 40           | LC Keychain | 2           |
+--------------+-------------+-------------+

Orders
+--------------+-------------+-------------+-------------+-----------+
| order_id     | customer_id | product_id  | order_date  | quantity  |
+--------------+-------------+-------------+-------------+-----------+
| 1            | 1           | 10          | 2020-06-10  | 1         |
| 2            | 1           | 20          | 2020-07-01  | 1         |
| 3            | 1           | 30          | 2020-07-08  | 2         |
| 4            | 2           | 10          | 2020-06-15  | 2         |
| 5            | 2           | 40          | 2020-07-01  | 10        |
| 6            | 3           | 20          | 2020-06-24  | 2         |
| 7            | 3           | 30          | 2020-06-25  | 2         |
| 9            | 3           | 30          | 2020-05-08  | 3         |
+--------------+-------------+-------------+-------------+-----------+

Result 表:
+--------------+------------+
| 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.

  • SQL1：

    • select
o.customer_id, c.name
from
Customers c, Product p, Orders o
where c.customer_id = o.customer_id
  and p.product_id = o.product_id
group by o.customer_id
having sum(case when date_format(o.order_date, '%Y-%m') = '2020-06' then p.price * o.quantity else 0 end) >= 100
   and sum(case when date_format(o.order_date, '%Y-%m') = '2020-07' then p.price * o.quantity else 0 end) >= 100

  • SQL2：

    • select
customer_id, name
from
    (select
    c.customer_id, name
    from
    Customers c, Orders o, Product p
    where c.customer_id = o.customer_id
      and p.product_id = o.product_id
      and month(order_date) in (6, 7)
    group by o.customer_id, month(order_date)
    having sum(quantity * price) >= 100) t
group by name
having count(*) = 2

查找拥有有效邮箱的用户

用户表： Users

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
| mail          | varchar | 
+---------------+---------+
user_id （用户 ID）是该表的主键。
这个表包含用户在某网站上注册的信息。有些邮箱是无效的。

写一条 SQL 语句，查询拥有有效邮箱的用户。

有效的邮箱包含符合下列条件的前缀名和域名：

  • 前缀名是包含字母（大写或小写）、数字、下划线 '_'、句点 '.' 和/或横杠 '-' 的字符串。前缀名必须以字母开头。
  • 域名是 '@leetcode.com' 。

按任意顺序返回结果表。

查询格式如下所示：

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 号用户的邮箱以句点（.）开头。
  • REGEXP：
    • regular expression 正则表达式 -^ 表示以后面的字符为开头
    • [] 表示括号内任意字符
    • - 表示连续
    • * 表示重复前面任意字符任意次数
    • \ 用来转义特殊字符，以表示字符原本的样子
    • $ 表示以前面的字符为结尾
    • 前缀名以字母开头：^[a-zA-Z]
    • 前缀名包含字母（大写或小写）、数字、下划线_、句点. 和 或 横杠-：[a-zA-Z0-9\_\.\-]*

'^[a-zA-A]+[a-zA-Z0-9_\\./\\-]*@leetcode\\.com$'
'^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode[.]com$'

  • SQL： 
    • select
*
from
Users
where mail regexp '^[a-zA-Z][a-zA-Z0-9/\\_\\.\\-]*@leetcode[.]com'

患某种疾病的患者

患者信息表： Patients

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

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

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

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 开头的疾病。

  • \s: 表示空格

  • SQL1：

    • select
*
from
Patients
where conditions rlike '^DIAB1|.*\\sDIAB1'

  • SQL2：

    • select
*
from
Patients
where conditions regexp '(^DIAB1| DIAB1)'

最近的三笔订单

表：Customers

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
+---------------+---------+
customer_id 是该表主键
该表包含消费者的信息

表：Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| customer_id   | int     |
| cost          | int     |
+---------------+---------+
order_id 是该表主键
该表包含id为customer_id的消费者的订单信息
每一个消费者 每天一笔订单

写一个 SQL 语句，找到每个用户的最近三笔订单。如果用户的订单少于 3 笔，则返回他的全部订单。
返回的结果按照 customer_name升序排列。如果排名有相同，则继续按照 customer_id 升序排列。如果排名还有相同，则继续按照 order_date 降序排列。

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

Customers
+-------------+-----------+
| customer_id | name      |
+-------------+-----------+
| 1           | Winston   |
| 2           | Jonathan  |
| 3           | Annabelle |
| 4           | Marwan    |
| 5           | Khaled    |
+-------------+-----------+

Orders
+----------+------------+-------------+------+
| order_id | order_date | customer_id | cost |
+----------+------------+-------------+------+
| 1        | 2020-07-31 | 1           | 30   |
| 2        | 2020-07-30 | 2           | 40   |
| 3        | 2020-07-31 | 3           | 70   |
| 4        | 2020-07-29 | 4           | 100  |
| 5        | 2020-06-10 | 1           | 1010 |
| 6        | 2020-08-01 | 2           | 102  |
| 7        | 2020-08-01 | 3           | 111  |
| 8        | 2020-08-03 | 1           | 99   |
| 9        | 2020-08-07 | 2           | 32   |
| 10       | 2020-07-15 | 1           | 2    |
+----------+------------+-------------+------+

Result table：
+---------------+-------------+----------+------------+
| 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 有 4 笔订单, 排除了 "2020-06-10" 的订单, 因为它是最老的订单。
Annabelle 只有 2 笔订单, 全部返回。
Jonathan 恰好有 3 笔订单。
Marwan 只有 1 笔订单。
结果表我们按照 customer_name 升序排列，customer_id 升序排列，order_date 降序排列。

进阶： 你能写出来最近n笔订单的通用解决方案吗?

  • SQL： 
    • select
t.name customer_name, t.customer_id, t.order_id, t.order_date
from
    (select
    c.name, o.customer_id, o.order_id, o.order_date,
    ROW_NUMBER() over(partition by o.customer_id order by order_date DESC) rn
    from
    Customers c join Orders o
    using(customer_id)) t
where t.rn <= 3
order by t.name, t.customer_id, t.order_date DESC

产品名称格式修复

表：Sales

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| sale_id      | int     |
| product_name | varchar |
| sale_date    | date    |
+--------------+---------+
sale_id 是该表主键
该表的每一行包含了产品的名称及其销售日期

因为在 2000 年该表是手工填写的，product_name可能包含前后空格，而且包含大小写。
写一个 SQL 语句报告每个月的销售情况：

  • product_name是小写字母且不包含前后空格
  • sale_date格式为('YYYY-MM')
  • total是产品在本月销售的次数

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

查询结果格式如下所示：

Sales 表：
+------------+------------------+--------------+
| sale_id    | product_name     | sale_date    |
+------------+------------------+--------------+
| 1          |      LCPHONE     | 2000-01-16   |
| 2         |    LCPhone       | 2000-01-17   |
| 3         |     LcPhOnE     | 2000-02-18   |
| 4         |      LCKeyCHAiN  | 2000-02-19   |
| 5         |   LCKeyChain     | 2000-02-28   |
| 6         | Matryoshka      | 2000-03-31   | 
+------------+------------------+--------------+

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

1 月份，卖了 2 个 LcPhones，请注意产品名称是小写的，中间可能包含空格
2 月份，卖了 2 个 LCKeychains 和 1 个 LCPhone
3 月份，卖了 1 个 matryoshka

  • 去除空格：

    • trim() 去除前后空格（保留中间空格）
    • ltrim() 去除左边空格
    • rtrim() 去除右边空格
    • replace(XXX, ' ', '') 去除全部空格

  • 字母大小写转换：

    • lower() 转为小写
    • upper() 转为小写

  • SQL：

    • select
product_name, sale_date, count(*) total
from
    (select
    lower(trim(product_name)) product_name,
    date_format(sale_date, '%Y-%m') sale_date
    from
    Sales) t
group by product_name, sale_date
order by product_name, sale_date

银行账户概要系列

1、用户表：Users

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| user_id      | int     |
| user_name    | varchar |
| credit       | int     |
+--------------+---------+
user_id 是这个表的主键。
表中的每一列包含每一个用户当前的额度信息。

交易表：Transactions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| trans_id      | int     |
| paid_by       | int     |
| paid_to       | int     |
| amount        | int     |
| transacted_on | date    |
+---------------+---------+
trans_id 是这个表的主键。
表中的每一列包含银行的交易信息。
ID 为 paid_by 的用户给 ID 为 paid_to 的用户转账。

力扣银行 (LCB) 帮助程序员们完成虚拟支付。我们的银行在表Transaction中记录每条交易信息，我们要查询每个用户的当前余额，并检查他们是否已透支（当前额度小于 0）。

写一条 SQL 语句，查询：

  • user_id用户 ID
  • user_name用户名
  • credit完成交易后的余额
  • credit_limit_breached检查是否透支 （"Yes" 或"No"）

以任意顺序返回结果表。

查询格式见如下示例：

Users 表：
+------------+--------------+-------------+
| user_id    | user_name    | credit      |
+------------+--------------+-------------+
| 1          | Moustafa     | 100         |
| 2          | Jonathan     | 200         |
| 3          | Winston      | 10000       |
| 4          | Luis         | 800         | 
+------------+--------------+-------------+

Transactions 表：
+------------+------------+------------+----------+---------------+
| trans_id   | paid_by    | paid_to    | amount   | transacted_on |
+------------+------------+------------+----------+---------------+
| 1          | 1          | 3          | 400      | 2020-08-01    |
| 2          | 3          | 2          | 500      | 2020-08-02    |
| 3          | 2          | 1          | 200      | 2020-08-03    |
+------------+------------+------------+----------+---------------+

结果表：
+------------+------------+------------+-----------------------+
| user_id    | user_name  | credit     | credit_limit_breached |
+------------+------------+------------+-----------------------+
| 1          | Moustafa   | -100       | Yes                   | 
| 2          | Jonathan   | 500        | No                    |
| 3          | Winston    | 9900       | No                    |
| 4          | Luis       | 800        | No                    |
+------------+------------+------------+-----------------------+
Moustafa 在 "2020-08-01" 支付了 $400 并在 "2020-08-03" 收到了 $200 ，当前额度 (100 -400 +200) = -$100
Jonathan 在 "2020-08-02" 收到了 $500 并在 "2020-08-08" 支付了 $200 ，当前额度 (200 +500 -200) = $500
Winston 在 "2020-08-01" 收到了 $400 并在 "2020-08-03" 支付了 $500 ，当前额度 (10000 +400 -500) = $9900
Luis 未收到任何转账信息，额度 = $800

  • SQL1：

    • select
user_id, user_name,
(credit - pb + pt) credit,
if((credit - pb + pt) < 0, 'Yes', 'No') credit_limit_breached
from
    (select
    u.*, ifnull(t1.pb, 0) pb, ifnull(t2.pt, 0) pt
    from
    Users u
    left join
    (select
    paid_by, sum(amount) pb
    from
    Transactions
    group by paid_by) t1
    on u.user_id = t1.paid_by
    left join
    (select
    paid_to, sum(amount) pt
    from
    Transactions
    group by paid_to) t2
    on u.user_id = t2.paid_to) t

  • SQL2：

    • select
u.user_id, u.user_name,
credit + ifnull(sum, 0) credit,
if((credit + ifnull(sum, 0)) < 0, 'Yes', 'No') credit_limit_breached
from
Users u
left join
(select
user_id, sum(sum) sum
from
    ((select
    paid_by user_id, -sum(amount) sum
    from Transactions
    group by paid_by)
    union
    (select
    paid_to user_id, sum(amount) sum
    from Transactions
    group by paid_to)) t
group by user_id) t1
on u.user_id = t1.user_id

2、表: Users

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| account      | int     |
| name         | varchar |
+--------------+---------+
account 是该表的主键.
表中的每一行包含银行里中每一个用户的账号.

表: Transactions

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| trans_id      | int     |
| account       | int     |
| amount        | int     |
| transacted_on | date    |
+---------------+---------+
trans_id 是该表主键.
该表的每一行包含了所有账户的交易改变情况.
如果用户收到了钱, 那么金额是正的; 如果用户转了钱, 那么金额是负的.
所有账户的起始余额为 0.

写一个 SQL,  报告余额高于 10000 的所有用户的名字和余额. 账户的余额等于包含该账户的所有交易的总和。
返回结果表单没有顺序要求。

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

Users table:
+------------+--------------+
| account    | name         |
+------------+--------------+
| 900001     | Alice        |
| 900002     | Bob          |
| 900003     | Charlie      |
+------------+--------------+

Transactions table:
+------------+------------+------------+---------------+
| trans_id   | account    | amount     | transacted_on |
+------------+------------+------------+---------------+
| 1          | 900001     | 7000       |  2020-08-01   |
| 2          | 900001     | 7000       |  2020-09-01   |
| 3          | 900001     | -3000      |  2020-09-02   |
| 4          | 900002     | 1000       |  2020-09-12   |
| 5          | 900003     | 6000       |  2020-08-07   |
| 6          | 900003     | 6000       |  2020-09-07   |
| 7          | 900003     | -4000      |  2020-09-11   |
+------------+------------+------------+---------------+

Result table:
+------------+------------+
| name       | balance    |
+------------+------------+
| Alice      | 11000      |
+------------+------------+
Alice 的余额为(7000 + 7000 - 3000) = 11000.
Bob 的余额为1000.
Charlie 的余额为(6000 + 6000 - 4000) = 8000.
  • SQL： 
    • select
u.name, sum(t.amount) balance
from
Users u join Transactions t
using(account)
group by u.name
having sum(t.amount) > 10000
avatar
文章
阅读
粉丝