526互联

SQL-高级查询和连接-1789. 员工的直属部门

发布时间 2023-12-02 09:06:28作者: Offer多多

注意事项：

找出员工的直属部门，可能有两种情况：

  • 员工加入多个部门，则选择对应的primary_flag是‘Y’的
  • 员工只有一个部门，对应的primary_flag是‘N’

返回结果没有顺序要求

解题思路：

首先来看一下我的错误方法：

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

乍一看是不是没有问题？但是执行出现如下错误：

在sql中，having字句中不能出现单独的且group by中没出现的列，而是需要使用聚合函数或表达式。或者，可以出现单独的在group by中出现的列。例如下边：

select employee_id, department_id
from Employee
group by employee_id
having employee_id = 1

这样是可以运行的，但是结果错误。

那么我们应该怎么做呢？

引入UNION这个概念，它是sql中用于合并两个或者多个select查询结果集的操作符。它的基本规则是，将两个查询的结果集合并，去除重复的行。基本使用语法如下：

SELECT column1, column2, ...
FROM table1
WHERE condition
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition;

注意：

1. UNION默认去除重复的行，如果想保留重复的行，可以使用UNION ALL。

2. UNION要求两个查询的列数和数据类型必须一致，否则会导致错误。如果想合并不同列的查询结果，可以使用NULL或添加虚拟列来匹配列数。

例如：

SELECT employee_id, employee_name FROM employees
UNION
SELECT manager_id, manager_name FROM managers;

这里的UNIOIN合并了两个查询的结果集。

所以我们可以使用UNION来合并两种情况的查询结果：

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

第二种思路：

第二种思路是使用窗口函数，窗口函数是SQL中一种强大的工具，用于在查询结果的窗口或分组内执行计算。窗口函数通常与‘OVER’子句一起使用，允许在查询结果的某个特定窗口上进行聚合、排序和排名等操作，而无需改变查询的基本结构。语法如下：

SELECT
  column1,
  column2,
  window_function(column3) OVER (PARTITION BY partition_column ORDER BY order_column ROWS BETWEEN N PRECEDING AND M FOLLOWING) AS window_result
FROM
  your_table;

关键点解释：

  • window_function(column3): 要执行的窗口函数，例如 SUM、AVG、ROW_NUMBER 等。
  • OVER: 表示窗口函数的开始。
  • PARTITION BY partition_column: 指定窗口的分区方式，即将结果集按照某个列进行分组。
  • ORDER BY order_column: 指定窗口内数据的排序方式。
  • ROWS BETWEEN N PRECEDING AND M FOLLOWING: 定义窗口的范围，例如取前 N 行到后 M 行。

一些常见的窗口函数包括：

  1. 聚合函数： SUM(column) OVER (...)、AVG(column) OVER (...) 等。
  2. 排名函数： RANK() OVER (...)、DENSE_RANK() OVER (...) 等。
  3. 行数函数： ROW_NUMBER() OVER (...)。
  4. 首尾函数： FIRST_VALUE(column) OVER (...)、LAST_VALUE(column) OVER (...)。

参考博客：https://www.cnblogs.com/Uni-Hoang/p/17411313.html 的讲解

窗口函数允许用户在不显式分组查询的情况下对结果集进行分组和聚合计算。

窗口函数的特别之处在于，它将结果集合中的每一行看作一个单独的计算对象，而不是将结果集合划分为分组并计算每个分组的聚合值。这使得窗口函数能够可以在不影响查询结果的情况下为结果集中的每一行计算类似的排名、行号、百分比和移动聚合函数等值。

窗口函数的语法如下：

<窗口函数> OVER ([PARTITION BY <分组列> [, <分组列>...]]
                     [ORDER BY <排序列> [ASC | DESC] [, <排序列> [ASC | DESC]]...]
                     [<rows or range clause>])

其中：

  • <窗口函数> : 定义要在窗口中计算的聚合函数或其它分析函数，如COUNT、RANK、SUM等。
  • OVER : 窗口函数的核心关键字。
  • PARTITION BY : 定义要用来分组的一组列名。
  • ORDER BY : 定义用来排序的一组列名。
  • <rows or range clause> : 定义窗口的行集合。默认为 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ，表示窗口包括从窗口开始到当前行的所有行。
SELECT department, employee_name, salary, 
  AVG(salary) OVER (PARTITION BY department) average_salary, 
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) salary_rank
FROM employee;

这个例子说明了如何使用窗口函数计算每个部门的平均工资，并根据平均工资排名。PERTITION BY定义了用于分组计算平均工资的列名，OVER后面依次定义了需要计算的列名和相应的窗口函数。AVG(salary) OVER (PARTITION BY department) 的意思是对于每个部门，计算 salary 列的平均值，而 RANK() OVER (PARTITION BY department ORDER BY salary DESC) 的意思是计算每个部门中 salary 列的排名。SQL窗口函数的语法和用法比较复杂，但它极大地拓展了SQL查询的能力，使得更多复杂的查询可以得以实现。

更多使用：

窗口函数的使用场景有很多，例如统计某个时间段内的每天销售量排名、计算每个部门的平均工资并与其他部门进行比较、计算每个产品上个月和本月的销售额变化等。

以下是一个使用窗口函数的查询示例和结果，该查询用于计算某个时间段内每天的销售量排名。

假设有一张包含销售信息的表sales，包括销售日期、销售量和产品ID等字段。

现在需要查询2021年1月1日至1月10日期间每天的销售量排名，包括每天的日期和排名信息。

SELECT sale_date, sale_volume, ROW_NUMBER() OVER(PARTITION BY sale_date ORDER BY sale_volume DESC) as sales_rank
FROM sales
WHERE sale_date BETWEEN '2021-01-01' AND '2021-01-10'

该查询将sales表根据销售日期进行分组，计算每个日期的销售量，并使用ROW_NUMBER()函数为每个日期的销售量进行排名。

最终代码为：

SELECT employee_id, department_id
FROM(SELECT employee_id, department_id, primary_flag, COUNT(*) OVER (PARTITION BY employee_id) count
FROM Employee) sub
WHERE count = 1 OR primary_flag = 'Y'