关于数据库:LeetCode-mysql-刷题一计算特殊奖金-买下所有产品的客户

理论开发中 sql 的高级用法并不罕用，特地是在做数据库迁徙时，高级用法几乎是噩梦

只满足于简略的查问，而后用代码实现相干逻辑，又感觉本人的 sql 能力太弱

通过 leetcode 中数据库相干的练习题，刷下题目，减少下本人的 sql 能力

leetcode 只提供了 MySQL 和 Oracle 两种数据库，我是用 MySQL 刷题的

上面两条题目：

• 第一题 [简略]，次要考查 MySQL 的根本用法：比方正则应用，条件判断，如果判断偶数
• 第二题 [中等]，次要考查 MySQL 的高级用法：比方将一张表的统计后果去和另一张表的数据做匹配

题目一

题目链接：计算非凡奖金

编写解决方案，计算每个雇员的奖金。如果一个雇员的 id 是 奇数 并且他的名字不是以 ‘M’ 结尾，那么他的奖金是他工资的 100%，否则奖金为 0。

返回的后果依照 employee_id 排序。

Create table If Not Exists Employees (employee_id int, name varchar(30), salary int);
Truncate table Employees;
insert into Employees (employee_id, name, salary) values ('2', 'Meir', '3000');
insert into Employees (employee_id, name, salary) values ('3', 'Michael', '3800');
insert into Employees (employee_id, name, salary) values ('7', 'Addilyn', '7400');
insert into Employees (employee_id, name, salary) values ('8', 'Juan', '6100');
insert into Employees (employee_id, name, salary) values ('9', 'Kannon', '7700');

 输出：Employees 表:
+-------------+---------+--------+
| employee_id | name    | salary |
+-------------+---------+--------+
| 2           | Meir    | 3000   |
| 3           | Michael | 3800   |
| 7           | Addilyn | 7400   |
| 8           | Juan    | 6100   |
| 9           | Kannon  | 7700   |
+-------------+---------+--------+
输入：+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 2           | 0     |
| 3           | 0     |
| 7           | 7400  |
| 8           | 0     |
| 9           | 7700  |
+-------------+-------+
解释：因为雇员 id 是偶数，所以雇员 id 是 2 和 8 的两个雇员失去的奖金是 0。雇员 id 为 3 的因为他的名字以 'M' 结尾，所以，奖金是 0。其余的雇员失去了百分之百的奖金。

解析

本题考查了三个知识点：

1. 判断 name 中首字母是 M 的办法有 LIKE 和 REGEXP 两种：

   • 应用 LIKE，用左匹配：M%

   • 应用 REGEXP，正则匹配有很多种，正则的写法有很多种，就不一一列举了

     • ^M：以 M 结尾
     • ^M.*：以 M 结尾，前面跟任意字符
     • ^[^M]：以非 M 结尾
   • 还有一个跟 REGEXP 相似的 RLIKE，也是匹配正则
2. 匹配偶数办法有很多，能够看这题 620. 乏味的电影，用了 6 种办法判断奇数

3. 条件判断如果满足输入 bonus 否者输入 0，有两种办法：

   • IF：IF (condition, true, false)
   • CASE：CASE WHEN condition THEN true ELSE false END

把握了下面的办法，你就能够写出 24 种 SQL 语句了，上面是其中一种

SELECT
    employee_id,
    IF (employee_id % 2 != 0 AND name NOT LIKE 'M%', salary, 0) bonus
FROM
    Employees
ORDER BY employee_id;

题目二

题目链接：买下所有产品的客户

编写解决方案，报告 Customer 表中购买了 Product 表中所有产品的客户的 id。

返回后果表 无程序要求。

返回后果格局如下所示。

Create table If Not Exists Customer (customer_id int, product_key int);
Create table Product (product_key int);
Truncate table Customer;
insert into Customer (customer_id, product_key) values ('1', '5');
insert into Customer (customer_id, product_key) values ('2', '6');
insert into Customer (customer_id, product_key) values ('3', '5');
insert into Customer (customer_id, product_key) values ('3', '6');
insert into Customer (customer_id, product_key) values ('1', '6');
Truncate table Product;
insert into Product (product_key) values ('5');
insert into Product (product_key) values ('6');

Customer 表：+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1           | 5           |
| 2           | 6           |
| 3           | 5           |
| 3           | 6           |
| 1           | 6           |
+-------------+-------------+
该表可能蕴含反复的行。customer_id 不为 NULL。product_key 是 Product 表的外键 (reference 列)。Product 表：+-------------+
| product_key |
+-------------+
| 5           |
| 6           |
+-------------+
输入：+-------------+
| customer_id |
+-------------+
| 1           |
| 3           |
+-------------+
product_key 是这张表的主键（具备惟一值的列）。解释：购买了所有产品（5 和 6）的客户的 id 是 1 和 3。

本题考查了 2 个知识点：

1. 如何查问出一个用户购买了多少件商品
2. 如何关联两张表中的数据

解析

办法一

1. 全副有多少件商品：

   • SELECT COUNT(*) FROM product;

2. 每个用户购买了哪些商品：

   • SELECT customer_id, group_concat(product_key) AS product_key FROM Customer GROUP BY customer_id;

3. 分组之后通过 having 过滤掉购买商品数量不等于全副商品数量的用户：

   • 第一个 count 须要应用 distinct 去重，可能会呈现同一个用户买了多件商品
   • 第二个 count 不须要去重，因为商品不会反复
     COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product);

SELECT
  customer_id
FROM
  Customer
GROUP BY
  customer_id
HAVING
  count(DISTINCT product_key) = (SELECT count(*) FROM Product );

办法二

1. 先通过 group by coustomer_id 分组

2. 分组之后，能够应用 group_concat 函数将商品编号拼接成字符串

   • group_concat(DISTINCT product_key ORDER BY product_key)：将 Customer 表中每个用户购买的商品编号拼接成字符串
   • SELECT group_concat(product_key ORDER BY product_key) FROM Product：将 Product 表中的商品编号拼接成字符串
3. 而后比拟两个字符串是否相等

SELECT
  customer_id
FROM
  Customer
GROUP BY
  customer_id
HAVING
  group_concat(DISTINCT product_key ORDER BY product_key) = (SELECT group_concat( product_key ORDER BY product_key) FROM Product )

更多题目

leetcode 刷题