每日刷力扣SQL题(四)

1045.买下所有的客户

考察点：Having的使用 最后要去掉重复购买的产品id

select customer_id
from Customer 
group by customer_id
Having count( distinct product_key ) = (select count(*) from Product)

1731.每位经理的下属员工数量

考察点 ：表的连接 ，聚合函数 ，函数的使用

select e.reports_to as employee_id , m.name as name , count(e.employee_id) as reports_count, ROUND(AVG(e.age)) as average_age
from Employees e join Employees m on e.reports_to = m.employee_id
where e.reports_to is not NULL
group by e.reports_to 
order by e.reports_to

1789.员工的直属部门

考察 ：使用窗口函数 分组计算数量 作为一张表 再从该表中根据查询条件筛选数据

WITH t AS
(
    select 
        employee_id ,
        department_id,
        primary_flag,
        COUNT(*) OVER (PARTITION BY employee_id) AS count_over
    From Employee
)
SELECT employee_id , department_id
from t
where count_over = 1 or primary_flag = 'Y'

窗口函数介绍

SQL 窗口函数（Window Functions）是一类强大的函数，可以在查询结果的每一行上执行计算，而不需要像聚合函数那样对结果进行分组。窗口函数允许你对结果集中的行进行分析，通常用于生成运行总和、排名、移动平均值等。

<window_function> OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
    [ROWS or RANGE frame_specification]
)

主要组成部分

1. 窗口函数: 常见的窗口函数包括：

   • ROW_NUMBER(): 为结果集中的每一行分配唯一的序号。
   • RANK(): 为结果集中的每一行分配排名，相同的值会有相同的排名。
   • DENSE_RANK(): 类似于 RANK()，但没有空缺排名。
   • SUM(), AVG(), COUNT(): 这些聚合函数可以与窗口函数结合使用。
   • LEAD() 和 LAG(): 用于访问当前行的前一行或后一行的值。

2. PARTITION BY: 用于将结果集划分为多个分区，窗口函数会在每个分区内独立计算。例如，按部门计算薪资总和。

3. ORDER BY: 用于定义行的顺序，这是计算排名或运行总和时必须的。

4. ROWS 或 RANGE: 可选的，用于定义计算的行范围。

5. 示例

   假设有一个员工表 employees，其中包含 department_id 和 salary 列。

   SELECT 
       employee_id,
       salary,
       SUM(salary) OVER (PARTITION BY department_id) AS department_total,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
   FROM 
       employees;
   

示例解析

• SUM(salary) OVER (PARTITION BY department_id) 计算每个部门的总薪资，并将结果添加到每一行。
• RANK() OVER (ORDER BY salary DESC) 计算每个员工的薪资排名。

使用场景

• 报表: 生成带有分组和排名的报表。
• 分析: 计算移动平均、同比增长等。
• 数据洞察: 通过分析数据趋势和模式获得深入见解。

优势

• 灵活性: 可以在不进行分组的情况下进行复杂计算。
• 简化查询: 不需要使用子查询来获取相关数据。

通过窗口函数，可以更加高效和简洁地处理复杂的 SQL 查询。

 子查询解法

# Write your MySQL query statement below
select employee_id, department_id
from Employee
where primary_flag = 'Y' or employee_id in (
    select employee_id
    from Employee
    group by employee_id
    having count(employee_id) = 1
)
 

610.判断三角形

IF判断解法

select * ,
IF(x+y>z and x+z > y and y+z > x,'Yes','No') as triangle
from Triangle

CASE WHEN 解法

select
    x,y,z,
    CASE
        when x+y>z and x+z>y and y+z>x then 'Yes'
        else "No"
    END as 'triangle'
from
    Triangle

180.连续出现的数字

        连续出现的意味着相同数字的 Id 是连着的，由于这题问的是至少连续出现 3 次，我们使用 Logs 并检查是否有 3 个连续的相同数字。

        然后我们从上表中选择任意的 Num 获得想要的答案。同时我们需要添加关键字 DISTINCT ，因为如果一个数字连续出现超过 3 次，会返回重复元素。

SELECT distinct l1.Num as ConsecutiveNums
From 
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num

解法二：

思路：连续的相同Num的数字，但是Id有可能不是连续的，就需要通过对结果集再次编号，使其变成连续的。

1、对原始数字编号，从1开始使用 row_number() over(表达式) 函数，使用Id来排序既

select Id ,Num ,
row_number() over(order by id) as SerialNum
From ContinueNumber

2、使用原始数据另一维度排序，这些num值一样的分组排序，然后对其编号同样使用row_number() over(表达式)，参数：（num分组，id排序）row_number() over(partition by num order by id)

SELECT Id,Num,
ROW_NUMBER() over(partition by Num order by Id) as SerialGroup
FROM ContinueNumber

3、通过上述1和上述2 看一下有什么规律吗？两个列（SerialNum，SerialGroup）对应相减，只要连续，相减得到的值是一样的。不连续相减得到的值也不同。

SELECT Id,Num,
      row_number() over(order by id) -
      row_number() over(partition by Num order by Id) as SerialNumberSubGroup
      FROM ContinueNumber

4、通过上述3，通过列Num和列SerialNumberSubGroup分组，最后拿到Num,就是求得的数据,去重（distinct）指：有可能同一个数字在多处出现三次以上。

SELECT DISTINCT Num FROM (
SELECT Num,COUNT(1) as SerialCount FROM 
(SELECT Id,Num,
row_number() over(order by id) -
ROW_NUMBER() over(partition by Num order by Id) as SerialNumberSubGroup
FROM ContinueNumber) as Sub
GROUP BY Num,SerialNumberSubGroup HAVING COUNT(1) >= 3) as Result