【SQL高频练习带刷】day6：高级查询

题目一：每位经理的下属员工数量

题目要求：

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

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

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

表结构：

 运行结果示例：

思路：

        这道题可以使用自连接查询进行，也就是使用reports_to 和 employee_id作为条件，连接两张表，再进行分组统计即可。

运行代码示例：

select e1.employee_id,e1.name,count(e2.reports_to) reports_count,round(avg(e2.age),0) as average_age
from Employees e1
join Employees e2
on e1.employee_id = e2.reports_to
group by e2.reports_to
order by employee_id

题目二：员工的直属部门

题目要求：

一个员工可以属于多个部门。当一个员工加入超过一个部门的时候，他需要决定哪个部门是他的直属部门。请注意，当员工只加入一个部门的时候，那这个部门将默认为他的直属部门，虽然表记录的值为'N'.

请编写解决方案，查出员工所属的直属部门。

返回结果 没有顺序要求 。

表结构：

 运行结果示例：

思路：

        这道题第一反应就是分情况讨论，使用union连接两部分的查询结果。也就是

select  employee_id , department_id 
from Employee
group by employee_id
having count(department_id) = 1 
union 
select  employee_id , department_id 
from Employee
where primary_flag ='Y'

        看了看题解大多数也是这种解法，但是有一个题解也指出了这种方法存在的问题——MySQL中不允许使用select查询非分组字段department_id，因为MySQL对ONLY_FULL_GROUP_BY的SQL模式比较敏感。如果启用了ONLY_FULL_GROUP_BY模式（大多数时候默认开启），MySQL要求SELECT列表、HAVING条件和ORDER BY子句仅引用GROUP BY子句中的列或聚合函数。所以该作者也提出了一种窗口函数的解法：

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'

作者：没上岸就继续刷呗
链接：https://leetcode.cn/problems/primary-department-for-each-employee/solutions/2702019/qiang-lie-jian-yi-bu-yao-shi-yong-unionc-l86h/
        但是我们也可以直接将department_id字段使用聚合函数处理一下，得到的结果不变，但是MySQL也不会认为存在语法错误。

运行代码示例：

select  employee_id , min(department_id)  AS department_id
from Employee
group by employee_id
having count(department_id) = 1 
union 
select  employee_id , department_id 
from Employee
where primary_flag ='Y'

题目三：判断三角形

题目要求：

对每三个线段报告它们是否可以形成一个三角形。

以 任意顺序 返回结果表。

表结构：

 运行结果示例：

思路：

        emm数学废表示刷题中最难的一题出现了（bushi），特意去查了一下怎么判断三角形.......了解了三角形的判断条件之后就很简单了，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

题目四：连续出现的数字

题目要求：

找出所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

表结构：

 运行结果示例：

思路：

        这道题目是SQL中非常经典的一种类型，也就是“连续登录问题”，我们看给出的示例表表名也可以看出，这道题的现实背景就是查看连续**天登录的用户，反之我们也可以去查询某用户连续登录的天数。这道题比较通用的解法就是使用窗口函数（虽然我的第一反应是连表查询），窗口函数在SQL中使用频率是非常高的，具体的使用可以参考【SQL】一张学生表带你学会开窗函数-CSDN博客这篇博客。

        本题我们使用的窗口函数是lead和lag将数据进行位移。lag的作用是将上方的数据移动到下方，上端出现空位。lead的作用是将下方的数据移动到上方，下端出现空位。

        lag 和lead 有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是 超出记录窗口时的默认值。

        本题中，我们使用窗口函数移动num列，拼接出一张新表，再在新表中查询符合三个数字相等的数字即可。

运行代码示例：

select distinct 
    num ConsecutiveNums
from (  
    select num,lead(num) over(order by id) num1,lead(num,2) over(order by id) num2
    from Logs
) t
where num = num1 and num1 = num2

再附一种使用连接查询的方法：

select distinct 
    t1.num ConsecutiveNums
from Logs t1,Logs t2,Logs t3
where t1.id = t2.id - 1
and t2.id = t3.id - 1
and t1.num = t2.num
and t2.num = t3.num 

补充：最新版测试用例中增加了“id必须连续”这一条件，也就是下图这种情况（累了...），所以需要把id这一列也考虑上。

with t1 as(select id , num , 
                lag(id,1) over(order by id) as id1 ,
                lag(num,1) over(order by id) as lag1 , 
                lag(id,2) over(order by id) as id2, 
                lag(num,2) over(order by id) as lag2
from Logs
order by id)
select distinct num as ConsecutiveNums
from t1
where num = lag1 and lag1 =lag2 and id1 = id-1 and id2 = id-2

最后提供一个连续登录问题的通用解法：

# Write your MySQL query statement below
SELECT DISTINCT t.num as ConsecutiveNums FROM (
        SELECT Num,COUNT(1) FROM 
                                (SELECT Id,Num,
                                row_number() over(order by id) -
                                row_number() over(partition by Num order by Id) as rankSub
                                FROM Logs) Sub
        GROUP BY Sub.Num,Sub.rankSub HAVING COUNT(1) >= 3 #连续N次则替换为N
                                            ) t