101. 患某种疾病的患者

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

按任意顺序返回结果表。

  1. +------------+--------------+--------------+
  2. | patient_id | patient_name | conditions   |
  3. +------------+--------------+--------------+
  4. | 3          | Bob          | DIAB100 MYOP |
  5. | 4          | George       | ACNE DIAB100 | 
  6. +------------+--------------+--------------+
  7. Bob and George both have a condition that starts with DIAB1.
  1. Create table If Not Exists 101_Patients (patient_id int, patient_name varchar(30), conditions varchar(100));
  2. Truncate table 101_Patients;
  3. insert into 101_Patients (patient_id, patient_name, conditions) values ('1', 'Daniel', 'YFEV COUGH');
  4. insert into 101_Patients (patient_id, patient_name, conditions) values ('2', 'Alice', '');
  5. insert into 101_Patients (patient_id, patient_name, conditions) values ('3', 'Bob', 'DIAB100 MYOP');
  6. insert into 101_Patients (patient_id, patient_name, conditions) values ('4', 'George', 'ACNE DIAB100');
  7. insert into 101_Patients (patient_id, patient_name, conditions) values ('5', 'Alain', 'DIAB201');

最终SQL：

  1. select
  2.      patient_id,
  3.      patient_name,
  4.      conditions 
  5. from 
  6.      Patients
  7. where
  8.      conditions like '%DIAB1%';

102. 最近的三笔订单

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

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

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

最终SQL:

  1. -- 方法一
  2. select
  3.       C.name as customer_name,
  4.       O.customer_id,
  5.       O.order_id,
  6.       O.order_date
  7. from
  8.       102_customers C 
  9. inner join
  10.       102_Orders O 
  11. on
  12.       C.customer_id = O.customer_id
  13. where 
  14.      (O.customer_id,O.order_date) in (select
  15.                                             o1.customer_id,
  16.                                             o1.order_date
  17.                                       from 
  18.                                             102_Orders o1 
  19.                                       inner join
  20.                                             102_Orders o2
  21.                                       on 
  22.                                             o1.customer_id = o2.customer_id 
  23.                                             and
  24.                                             o1.order_date <= o2.order_date
  25.                                       group by
  26.                                             o1.customer_id,o1.order_date
  27.                                       having
  28.                                             count(o2.order_date) <= 3)
  29. order by 
  30.       customer_name,
  31.       O.customer_id,
  32.       O.order_date desc;
  34. -- 方法二
  35. select 
  36.      name customer_name, 
  37.      customer_id,
  38.      order_id,
  39.      order_date
  40. from
  41.     (select 
  42.           name ,
  43.           o.customer_id,
  44.           order_id,
  45.           order_date ,
  46.           rank() over(partition by o.customer_id order by order_date desc) rk
  47.      from 
  48.           102_Orders o 
  49.      left join
  50.           102_Customers c
  51.      on
  52.           o.customer_id=c.customer_id )t1
  53. where rk <=3
  54. order by customer_name ,customer_id,order_date desc;

103. 格式化产品名称

写一个 SQL 语句报告：

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

sale_date 格式为 (‘YYYY-MM’)

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

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

  1. +--------------+--------------+----------+
  2. | product_name | sale_date    | total    |
  3. +--------------+--------------+----------+
  4. | lckeychain   | 2000-02      | 2        |
  5. | lcphone      | 2000-01      | 2        | 
  6. | lcphone      | 2000-02      | 1        | 
  7. | matryoshka   | 2000-03      | 1        | 
  8. +--------------+--------------+----------+
  10. In January, 2 LcPhones were sold, please note that the product names are not case sensitive and may contain spaces.
  11. In Februery, 2 LCKeychains and 1 LCPhone were sold.
  12. In March, 1 matryoshka was sold.
  1. Create table If Not Exists 103_Sales (sale_id int, product_name varchar(30), sale_date date);
  2. Truncate table 103_Sales;
  3. insert into 103_Sales (sale_id, product_name, sale_date) values ('1', 'LCPHONE', '2000-01-16');
  4. insert into 103_Sales (sale_id, product_name, sale_date) values ('2', 'LCPhone', '2000-01-17');
  5. insert into 103_Sales (sale_id, product_name, sale_date) values ('3', 'LcPhOnE', '2000-02-18');
  6. insert into 103_Sales (sale_id, product_name, sale_date) values ('4', 'LCKeyCHAiN', '2000-02-19');
  7. insert into 103_Sales (sale_id, product_name, sale_date) values ('5', 'LCKeyChain', '2000-02-28');
  8. insert into 103_Sales (sale_id, product_name, sale_date) values ('6', 'Matryoshka', '2000-03-31');

最终SQL：

  1. select 
  2.      trim(lower(product_name)) as product_name, 
  3.      date_format(sale_date,'%Y-%m') as sale_date,
  4.      count(*) as total 
  5. from 
  6.      103_Sales 
  7. group by
  8.      trim(lower(product_name)),
  9.      date_format(sale_date,'%Y-%m') 
  10. order by
  11.      product_name asc, 
  12.      sale_date asc;

104. 每个商品最近的订单

编写一个SQL查询来查找每个产品的最新订单。返回按product_name升序排序的结果表，如果有并列，则按product_id升序排序。如果仍然存在并列，则按order_id升序对它们排序。

效果展示：

  1. +--------------+------------+----------+------------+
  2. | product_name | product_id | order_id | order_date |
  3. +--------------+------------+----------+------------+
  4. | keyboard     | 1          | 6        | 2020-08-01 |
  5. | keyboard     | 1          | 7        | 2020-08-01 |
  6. | mouse        | 2          | 8        | 2020-08-03 |
  7. | screen       | 3          | 3        | 2020-08-29 |
  8. +--------------+------------+----------+------------+

建表语句：

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

最终SQL：

  1. -- 方法一
  2. select
  3.       p1.product_name,
  4.       o2.product_id,
  5.       o2.order_id,
  6.       o2.order_date
  7. from
  8.       104_Products p1
  9. right join
  10.      (select
  11.            o2.product_id,
  12.            o2.order_date,
  13.            o2.order_id
  14.       from
  15.            104_Orders o2
  16.       join
  17.           (select
  18.                  o.product_id,
  19.                  max(order_date) as order_date,
  20.                  max(order_id) as order_id
  21.            from
  22.                  104_Orders o
  23.            group by
  24.                  product_id
  25.            ) o1
  26.        on
  27.            o2.order_date = o1.order_date 
  28.            and
  29.            o2.product_id = o1.product_id
  30.        ) o2
  31. on 
  32.        p1.product_id = o2.product_id
  33. order by 
  34.        p1.product_name,
  35.        o2.product_id,
  36.        o2.order_id;
  38. -- 方法二
  39. select 
  40.      product_name,
  41.      product_id,
  42.      order_id,
  43.      order_date
  44. from
  45.     (select 
  46.           product_name,
  47.           o.product_id,
  48.           order_id,
  49.           order_date,
  50.           rank() over(partition by o.product_id order by order_date desc) rk
  51.      from
  52.           104_Orders o 
  53.      left join
  54.           104_Products p
  55.      on 
  56.           o.product_id =p.product_id )t1
  57. where
  58.      rk =1
  59. order by 
  60.      product_name,
  61.      product_id,
  62.      order_id;