MySQL 练习<4>

MySQL 练习

大家好呀,我是小笙,今天我来分享一些 Leetcode 上的MySQL的练习

1484. 按日期分组销售产品

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

# 创建表
Create table If Not Exists Activities (
    sell_date date, 
    product varchar(20)
)

# 插入数据
insert into Activities (sell_date, product) values ('2020-05-30', 'Headphone')
insert into Activities (sell_date, product) values ('2020-06-01', 'Pencil')
insert into Activities (sell_date, product) values ('2020-06-02', 'Mask')
insert into Activities (sell_date, product) values ('2020-05-30', 'Basketball')
insert into Activities (sell_date, product) values ('2020-06-01', 'Bible')
insert into Activities (sell_date, product) values ('2020-06-02', 'Mask')
insert into Activities (sell_date, product) values ('2020-05-30', 'T-Shirt')

示例:

输入:
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),只需返回该物品名。

代码实现

select
    sell_date,
    count(distinct product) num_sold,
    GROUP_CONCAT(distinct product) products
from
    activities
group by 
	sell_date
order by 
	sell_date;

1965. 丢失信息的雇员

写出一个查询语句,找到所有 丢失信息 的雇员id。当满足下面一个条件时,就被认为是雇员的信息丢失:

  • 雇员的 姓名 丢失了,或者
  • 雇员的 薪水信息 丢失了,或者

返回这些雇员的id employee_id从小到大排序

Create table If Not Exists Employees (
    employee_id int, 
    name varchar(30)
)

Create table If Not Exists Salaries (
    employee_id int, 
    salary int
)

Truncate table Employees
insert into Employees (employee_id, name) values ('2', 'Crew')
insert into Employees (employee_id, name) values ('4', 'Haven')
insert into Employees (employee_id, name) values ('5', 'Kristian')

Truncate table Salaries
insert into Salaries (employee_id, salary) values ('5', '76071')
insert into Salaries (employee_id, salary) values ('1', '22517')
insert into Salaries (employee_id, salary) values ('4', '63539')

示例 :

输入:
Employees table:
+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+
输出:
+-------------+
| employee_id |
+-------------+
| 1           |
| 2           |
+-------------+
解释:
雇员1,2,4,5 都工作在这个公司。
1号雇员的姓名丢失了。
2号雇员的薪水信息丢失了。

代码实现

select 
    employee_id 
from (
    select employee_id from employees
    union all 
    select employee_id from salaries
) as t
group by 
    employee_id
having 
    count(employee_id) = 1
order by 
    employee_id;

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

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

Create table If Not Exists Products (
    product_id int,
    store1 int, 
    store2 int, 
    store3 int
)

Truncate table Products
insert into Products (product_id, store1, store2, store3) values ('0', '95', '100', '105')
insert into Products (product_id, store1, store2, store3) values ('1', '70', 'None', '80')

示例 :

输入:
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无法买到

代码实现

# 行转列,列转行常用:
# CASE WHEN
# UNION ALL 不会对结果去重,效率比 UNION 更高。 如果结果集中存在重复数据建议使用 UNION
select 
    product_id,'store1' as store,store1 as price -- 'store1'指的是标题 store1指的是值
from
    Products
where
    store1 is not null
union All
select 
    product_id,'store2' as store,store2 as price 
from
    Products
where
    store2 is not null
union All
select 
    product_id,'store3' as store,store3 as price 
from
    Products
where
    store3 is not null

608. 树节点

树中每个节点属于以下三种类型之一:

  • 叶子:如果这个节点没有任何孩子节点
  • 根:如果这个节点是整棵树的根,即没有父节点
  • 内部节点:如果这个节点既不是叶子节点也不是根节点

写一个查询语句,输出所有节点的编号和节点的类型,并将结果按照节点编号排序

Create table If Not Exists Tree (
    id int, 
    p_id int
)

Truncate table Tree
insert into Tree (id, p_id) values ('1', 'None')
insert into Tree (id, p_id) values ('2', '1')
insert into Tree (id, p_id) values ('3', '1')
insert into Tree (id, p_id) values ('4', '2')
insert into Tree (id, p_id) values ('5', '2')

示例:

+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+
------查询结果------
+----+------+
| id | Type |
+----+------+
| 1  | Root |
| 2  | Inner|
| 3  | Leaf |
| 4  | Leaf |
| 5  | Leaf |
+----+------+
**解释**

- 节点 '1' 是根节点,因为它的父节点是 NULL ,同时它有孩子节点 '2' 和 '3' 。

- 节点 '2' 是内部节点,因为它有父节点 '1' ,也有孩子节点 '4' 和 '5' 。

- 节点 '3', '4' 和 '5' 都是叶子节点,因为它们都有父节点同时没有孩子节点。

- 样例中树的形态如下:

			             1
			           /   \
                      2       3
                    /   \
                  4       5
```

注意

#### 使用 UNION

```sql
SELECT
    id, 'Root' AS Type
FROM
    tree
WHERE
    p_id IS NULL

UNION

SELECT
    id, 'Leaf' AS Type
FROM
    tree
WHERE
    id NOT IN (SELECT DISTINCT -- 存在子节点;判断该id是否有作为父节点
            p_id
        FROM
            tree
        WHERE
            p_id IS NOT NULL)
        AND p_id IS NOT NULL -- 存在父节点

UNION

SELECT
    id, 'Inner' AS Type
FROM
    tree
WHERE
    id IN (SELECT DISTINCT  -- 不存在子节点
            p_id
        FROM
            tree
        WHERE
            p_id IS NOT NULL)
        AND p_id IS NOT NULL -- 存在父节点
ORDER BY id;
使用流控制语句 CASE
# 结构
# CASE
#     WHEN 条件语句
#        THEN 执行语句
#     WHEN  条件语句
#       THEN 执行语句
#     ELSE 执行语句
# END 
SELECT
    id AS `Id`,
    CASE
        WHEN tree.id = (SELECT atree.id FROM tree atree WHERE atree.p_id IS NULL)
          THEN 'Root'
        WHEN tree.id IN (SELECT atree.p_id FROM tree atree)
          THEN 'Inner'
        ELSE 'Leaf'
    END AS Type
FROM
    tree
ORDER BY `Id`;
使用 IF 函数
select
    id,if(isNULL(p_id),'Root',if(id in (select temptree.p_id from tree temptree),'Inner','Leaf')) as Type
from
    tree
order by
    id
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

罗念笙

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值