力扣上的SQL复健记录5

题1.

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| employee_id | int      |
| name        | varchar  |
| reports_to  | int      |
| age         | int      |
+-------------+----------+
employee_id 是这个表中具有不同值的列。
该表包含员工以及需要听取他们汇报的上级经理的 ID 的信息。 有些员工不需要向任何人汇报（reports_to 为空）。

对于此问题，我们将至少有一个其他员工需要向他汇报的员工，视为一个经理。

编写一个解决方案来返回需要听取汇报的所有经理的 ID、名称、直接向该经理汇报的员工人数，以及这些员工的平均年龄，其中该平均年龄需要四舍五入到最接近的整数。

返回的结果集需要按照 employee_id 进行排序。

记录1：

这题再次帮助巩固了having和where的区别，这是我第一次的答案：

select    
    a.employee_id,
    a.name,
    count(b.employee_id) as reports_count,
    round(avg(b.age),0) as average_age
from
    Employees a left join Employees b on a.employee_id=b.reports_to
group by a.employee_id
having b.employee_id is not null
order by a.employee_id

然而，having 子句通常用于对聚合函数的结果进行过滤，而不是用于过滤非聚合条件，因为having发生在聚合之后,换句话说，having中的筛选条件需要在select中出现。而此处的筛选条件是非聚合条件，因此应该使用where来进行筛选，修改如下:

select    
    a.employee_id,
    a.name,
    count(b.employee_id) as reports_count,
    round(avg(b.age),0) as average_age
from
    Employees a left join Employees b on a.employee_id=b.reports_to
where b.employee_id is not null
group by a.employee_id
order by a.employee_id 

题2.

编写一个解决方案，找出在 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    |
+------------+-------+

记录2.

最初想用聚合函数，但筛选条件始终不方便表达，不是在where中出现聚合函数max，就是having中需要使用select中未定义的变量，亦或者是筛选最大日期时，price不能随group匹配，于是改为使用窗口函数对每个product_id依据日期排序：

select
    product_id,price
from(
    select
        product_id,
        price,
        row_number() over (partition by product_id order by date desc) as cn
    from(
        select
            product_id,
            case
            when change_date>"2019-08-16" then 10
            else new_price
            end as price,
            case
            when change_date>"2019-08-16" then null
            else change_date
            end as date
        from
            Products
    ) a
)b
where cn=1

此处复习一下窗口函数：SQL开窗函数（窗口函数）详解_sql开窗函数详解-CSDN博客