首页 > 数据库 >SQL-高级查询和连接-1789. 员工的直属部门

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

时间:2023-12-02 09:11:06浏览次数:56  
标签:直属 1789 窗口 函数 OVER 查询 SQL employee id

注意事项:

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

  • 员工加入多个部门,则选择对应的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): 要执行的窗口函数,例如 SUMAVGROW_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'

 

标签:直属,1789,窗口,函数,OVER,查询,SQL,employee,id
From: https://www.cnblogs.com/lbwBH/p/17871226.html

相关文章

  • SQL 算术运算符:加法、减法、乘法、除法和取模的用法
    SQLServer中的存储过程什么是存储过程?存储过程是一段预先编写好的SQL代码,可以保存在数据库中以供反复使用。它允许将一系列SQL语句组合成一个逻辑单元,并为其分配一个名称,以便在需要时调用执行。存储过程可以接受参数,使其更加灵活和通用。存储过程语法创建存储过程的语法如......
  • 攻防世界 supersqli
    打开页面,发现有GET请求传递的SQL,依次尝试"1'--","1'#",从报错可知,#成功注释。构造payload:1';showdatabases;#,成功,判断存在堆叠注入。尝试SELECT,发现查询语句都被过滤了,思考绕过。对select*from`1919810114514`;进行16进制编码,获得0x73656C656374202A206......
  • SQL 算术运算符:加法、减法、乘法、除法和取模的用法
    SQLServer中的存储过程什么是存储过程?存储过程是一段预先编写好的SQL代码,可以保存在数据库中以供反复使用。它允许将一系列SQL语句组合成一个逻辑单元,并为其分配一个名称,以便在需要时调用执行。存储过程可以接受参数,使其更加灵活和通用。存储过程语法创建存储过程的语法......
  • 七天.NET 8操作SQLite入门到实战 - 第五天引入SQLite-net ORM并封装常用方法(SQLiteHel
    前言上一章节我们搭建好了EasySQLite的前后端框架,今天我们的主要任务是在后端框架中引入SQLite-netORM并封装常用方法(SQLiteHelper)。七天.NET8操作SQLite入门到实战详细教程第一天SQLite简介第二天在Windows上配置SQLite环境第三天SQLite快速入门第四天EasySQLite......
  • Navicat登陆Mysql8.0报“caching_sha_password”错误
    Navicat登陆Mysql8.0报“caching_sha_password”错误​​官方说明:​https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html解决方案:1.使用本地mysql命令行登录;2.修改登录验证方式--修改登录验证方式ALTERUSER'root'@'localhost'IDENTIF......
  • Navicat登陆Mysql8.0报“caching_sha_password”错误
    Navicat登陆Mysql8.0报“caching_sha_password”错误​​官方说明:​https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html解决方案:1.使用本地mysql命令行登录;2.修改登录验证方式--修改登录验证方式ALTERUSER'root'@'localhost'IDENTIF......
  • Mysql时间减30分钟
    在MySQL中,我们可以使用DATE_SUB函数来进行时间减操作。下面是一个使用DATE_SUB函数将时间减去30分钟的示例:SELECTDATE_SUB(NOW(),INTERVAL30MINUTE);在上面的代码中,我们使用NOW()函数获取当前时间,然后用INTERVAL子句指定要减去的时间,这里是30分钟。查询结果将返......
  • Navicat登陆Mysql8.0报“caching_sha_password”错误
    Navicat登陆Mysql8.0报“caching_sha_password”错误​​官方说明:​https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html解决方案:1.使用本地mysql命令行登录;2.修改登录验证方式--修改登录验证方式ALTERUSER'root'@'localhost'IDENTIF......
  • Flask实践-使用pymysql时解决SQL注入问题
    最近在尝试使用flask编写一个网站防篡改监测平台,开始只注意功能,未注意注入问题,开始的SQL执行是拼接的方式,导致SQL注入: 用报错注入:修改为参数化查询: ......
  • DBeaver连接PostgreSQL后只有默认数据库“postgres”不显示其他数据库的问题解决办法
    我们在使用DBeaver连接PostgreSQL后,发现数据库中只有“postgres”默认数据库,不显示我们自己创建的数据库。1、......