【力扣 | SQL题 | 每日4题】力扣1164，3293，1308，1270

4 mid，四题都比较简单，没什么难度。

1. 力扣1164：指定日期的产品价格

1.1 题目：

产品数据表: Products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+
(product_id, change_date) 是此表的主键（具有唯一值的列组合）。
这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。

编写一个解决方案，找出在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 。

以 任意顺序 返回结果表。

结果格式如下例所示。

示例 1:

输入：
Products 表:
+------------+-----------+-------------+
| product_id | new_price | change_date |
+------------+-----------+-------------+
| 1          | 20        | 2019-08-14  |
| 2          | 50        | 2019-08-14  |
| 1          | 30        | 2019-08-15  |
| 1          | 35        | 2019-08-16  |
| 2          | 65        | 2019-08-17  |
| 3          | 20        | 2019-08-18  |
+------------+-----------+-------------+
输出：
+------------+-------+
| product_id | price |
+------------+-------+
| 2          | 50    |
| 1          | 35    |
| 3          | 10    |
+------------+-------+

1.2 思路：

看注释。

1.3 题解：

with tep1 as (
    -- 先将2019-08-16之前的数据过滤掉
    select *
    from Products
    where change_date  <= '2019-08-16'
), tep2 as (
    -- 然后给product_id开窗给出排名
    select product_id, new_price ,rank() over (partition by product_id order by change_date desc) ranks
    from tep1
), tep3 as (
    -- 查询到所有的产品
    select distinct product_id
    from Products 
), tep4 as (
    -- 查询到每个产品的最新修改的价格
    select product_id, new_price
    from tep2
    where ranks = 1
)
-- left join保证所有产品都可以查询到
select t1.product_id , ifNull(new_price, 10) price 
from tep3 t1
left join tep4 t2
on t1.product_id = t2.product_id

2. 力扣3293：计算产品最终价格

2.1 题目：

表：Products

+------------+---------+ 
| Column Name| Type    | 
+------------+---------+ 
| product_id | int     | 
| category   | varchar |
| price      | decimal |
+------------+---------+
product_id 是这张表的唯一主键。
每一行包含产品的 ID，分类以及价格。

表：Discounts

+------------+---------+ 
| Column Name| Type    | 
+------------+---------+ 
| category   | varchar |
| discount   | int     |
+------------+---------+
category 是这张表的主键。
每一行包含有一个产品分类和该分类的折扣百分比（值的范围从 0 到 100）。

编写一个解决方案来找到每个产品使用 分类折扣 后的 最终价格。如果一个产品分类 没有关联的折扣，它的价格保持 不变。

返回结果表以 product_id 升序 排序。

结果格式如下所示。

 

示例：

输入：

Products 表：

<span style="color:var(--text-secondary)"><span style="background-color:#f0f0f0"><span style="color:var(--text-secondary)">+------------+-------------+-------+
| product_id | category    | price |
+------------+-------------+-------+
| 1          | Electronics | 1000  |
| 2          | Clothing    | 50    |
| 3          | Electronics | 1200  | 
| 4          | Home        | 500   |
+------------+-------------+-------+
  </span></span></span>

Discounts 表：

<span style="color:var(--text-secondary)"><span style="background-color:#f0f0f0"><span style="color:var(--text-secondary)">+------------+----------+
| category   | discount |
+------------+----------+
| Electronics| 10       |
| Clothing   | 20       |
+------------+----------+
  </span></span></span>

输出：

<span style="color:var(--text-secondary)"><span style="background-color:#f0f0f0"><span style="color:var(--text-secondary)">+------------+------------+-------------+
| product_id | final_price| category    |
+------------+------------+-------------+
| 1          | 900        | Electronics |
| 2          | 40         | Clothing    |
| 3          | 1080       | Electronics |
| 4          | 500        | Home        |
+------------+------------+-------------+
  </span></span></span>

解释：

• 对于产品 1，它属于电器分类，有 10% 的折扣，所以最终价格为 1000 - (10% of 1000) = 900。
• 对于产品 2，它属于衣物分类，有 20% 的折扣，所以最终价格为 50 - (20% of 50) = 40。
• 对于产品 3，它属于电器分类，有 10% 的折扣，所以最终价格为 1200 - (10% of 1200) = 1080。
• 对于产品 4，它属于家具分类，没有可用的折扣，所以最终价格仍是 500。

结果表以 product_id 升序排序。

2.2 思路：

简单的左外连接。

2.3 题解：

-- 左外连接保证可以查询到所有的产品的最终价格
-- 如果产品没有相关联的折扣，则discount为null，ifNull函数就会返回price
with tep as (
    select product_id , ifNull(
        price*(100-discount)/100
    , price) final_price
    ,t1.category
    from Products t1
    left join Discounts t2
    on t1.category = t2.category
)
select *
from tep
order by product_id

3. 力扣1308：不同性别每日分数总计

3.1 题目：

表: Scores

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| player_name   | varchar |
| gender        | varchar |
| day           | date    |
| score_points  | int     |
+---------------+---------+
(gender, day)是该表的主键(具有唯一值的列的组合)
一场比赛是在女队和男队之间举行的
该表的每一行表示一个名叫 (player_name) 性别为 (gender) 的参赛者在某一天获得了 (score_points) 的分数
如果参赛者是女性，那么 gender 列为 'F'，如果参赛者是男性，那么 gender 列为 'M'

编写解决方案统计每种性别在每一天的总分。

返回按 gender 和 day 对查询结果 升序排序 的结果。

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

示例 1：

输入：
Scores表:
+-------------+--------+------------+--------------+
| player_name | gender | day        | score_points |
+-------------+--------+------------+--------------+
| Aron        | F      | 2020-01-01 | 17           |
| Alice       | F      | 2020-01-07 | 23           |
| Bajrang     | M      | 2020-01-07 | 7            |
| Khali       | M      | 2019-12-25 | 11           |
| Slaman      | M      | 2019-12-30 | 13           |
| Joe         | M      | 2019-12-31 | 3            |
| Jose        | M      | 2019-12-18 | 2            |
| Priya       | F      | 2019-12-31 | 23           |
| Priyanka    | F      | 2019-12-30 | 17           |
+-------------+--------+------------+--------------+
输出：
+--------+------------+-------+
| gender | day        | total |
+--------+------------+-------+
| F      | 2019-12-30 | 17    |
| F      | 2019-12-31 | 40    |
| F      | 2020-01-01 | 57    |
| F      | 2020-01-07 | 80    |
| M      | 2019-12-18 | 2     |
| M      | 2019-12-25 | 13    |
| M      | 2019-12-30 | 26    |
| M      | 2019-12-31 | 29    |
| M      | 2020-01-07 | 36    |
+--------+------------+-------+
解释：
女性队伍:
第一天是 2019-12-30，Priyanka 获得 17 分，队伍的总分是 17 分
第二天是 2019-12-31, Priya 获得 23 分，队伍的总分是 40 分
第三天是 2020-01-01, Aron 获得 17 分，队伍的总分是 57 分
第四天是 2020-01-07, Alice 获得 23 分，队伍的总分是 80 分

男性队伍：
第一天是 2019-12-18, Jose 获得 2 分，队伍的总分是 2 分
第二天是 2019-12-25, Khali 获得 11 分，队伍的总分是 13 分
第三天是 2019-12-30, Slaman 获得 13 分，队伍的总分是 26 分
第四天是 2019-12-31, Joe 获得 3 分，队伍的总分是 29 分
第五天是 2020-01-07, Bajrang 获得 7 分，队伍的总分是 36 分

3.2 思路：

使用窗口函数sum计算每天队伍总分，然后给gender优先级排序即可。

3.3 题解：

with tep1 as (
    -- 利用窗口函数计算每天的队伍总分
    select gender, day, sum(score_points) over (partition by gender order by day) total 
    from Scores
), tep2 as (
    -- 然后赋给gender优先级
    select case gender when 'F' then 1
    when 'M' then 2
    end gender, day, total
    from tep1
), tep3 as (
    select *
    from tep2
    order by gender, day
)
-- 将gender改回去
select case gender when 1 then 'F'
when 2 then 'M'
end gender, day, total
from tep3

4. 力扣1270：向公司CEO汇报工作的所有人

4.1 题目：

员工表：Employees

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| employee_id   | int     |
| employee_name | varchar |
| manager_id    | int     |
+---------------+---------+
employee_id 是这个表具有唯一值的列。
这个表中每一行中，employee_id 表示职工的 ID，employee_name 表示职工的名字，manager_id 表示该职工汇报工作的直线经理。
这个公司 CEO 是 employee_id = 1 的人。

编写解决方案，找出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id 。

由于公司规模较小，经理之间的间接关系 不超过 3 个经理 。

可以以 任何顺序 返回无重复项的结果。

返回结果示例如下。

示例 1：

输入：
Employees table:
+-------------+---------------+------------+
| employee_id | employee_name | manager_id |
+-------------+---------------+------------+
| 1           | Boss          | 1          |
| 3           | Alice         | 3          |
| 2           | Bob           | 1          |
| 4           | Daniel        | 2          |
| 7           | Luis          | 4          |
| 8           | Jhon          | 3          |
| 9           | Angela        | 8          |
| 77          | Robert        | 1          |
+-------------+---------------+------------+
输出：
+-------------+
| employee_id |
+-------------+
| 2           |
| 77          |
| 4           |
| 7           |
+-------------+
解释：
公司 CEO 的 employee_id 是 1.
employee_id 是 2 和 77 的职员直接汇报给公司 CEO。
employee_id 是 4 的职员间接汇报给公司 CEO 4 --> 2 --> 1 。
employee_id 是 7 的职员间接汇报给公司 CEO 7 --> 4 --> 2 --> 1 。
employee_id 是 3, 8 ，9 的职员不会直接或间接的汇报给公司 CEO。

4.2 思路：

三张表查询结果即为，直接和间接向领导汇报工作的所有人。

4.3 题解：

-- 先查询到直接汇报工作的人的id
with tep1 as (
    select employee_id
    from Employees
    where manager_id = 1 and employee_id <> 1
), tep2 as (
    -- 再查询到间接汇报工作的人的id
    select employee_id 
    from Employees
    where manager_id in (
        select * from tep1
    )
), tep3 as (
    -- 再查询到间接汇报工作的人的id
    select employee_id 
    from Employees
    where manager_id in (
        select * from tep2
    )
)
-- 然后将这三种情况union起来
select * from tep1
union all
select * from tep2
union all
select * from tep3