sql语句关键字over的作用及用法

最新推荐文章于 2023-12-11 18:02:43 发布
最新推荐文章于 2023-12-11 18:02:43 发布
阅读量2k 收藏 1
点赞数 2
分类专栏： sql 数据库 文章标签： 数据库 sql
本文深入探讨了SQL中的OVER子句及其与窗口函数的结合运用，包括两种主要使用方式：一种是与排名开窗函数一起使用，用于按指定字段分组并排序；另一种是与聚合开窗函数一起使用，实现在不分组情况下的聚合运算。通过示例演示了如何在查询中灵活应用OVER子句，以获取更丰富的数据洞察。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

原文链接：点击打开链接

sql语句关键字over的作用及用法  

在应用具体的聚合函数、排名函数前对行集的分区和排序。over子句，用于支持窗口的计算,所以一般与排名开窗函数、聚合开窗函数一起使用。

窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。

以前使用聚合函数必须分组，即便没有group by 子句，也是默认将表中所有的数据分成了1组，来聚合。通过使用over子句可以在不分组的情况下实现聚合运算，在查询结果集中既包含基础行的数据也包含聚合函数的值。（可以把over()子句理解成是“后台运行的数据”，只是为了让聚合函数或者是排名函数“用一下”，并不影响实际显示的数据。在后台提供数据。）

over子句的两种使用方式：

1.over子句与排名开窗函数一起用，语法：over([partition by 列1] order by 列2)。必须有orderby 子句

2.over子句与聚合开窗函数一起用，语法：over([partition by 列1])不能使用order by子句了。【先演示这个】

注：当over()子句与聚合函数一起使用时，也可以直接写count(*)over()不在over()子句中进行任何分区，表示把整个表分为一个区。

第1种使用方式

over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 orderby columnname2）
含义：按columname1指定的字段的值，对查询后的结果集进行分组再组内排序，形成结果集中的一个新列。若SQL语句最后有order by，则把最终的结果集再整体排序。

例如：employees表中，有两个部门的记录：department_id ＝10和20
SQL语句“select department_id ,salary,rank() over(partition by department_id order by salary ) from Employees ”，就是指在部门10中进行薪水的排名，在部门20中进行薪水排名。如果是没有partition by部分，则是在整个公司内进行排名。

 

第2种使用方式

下面的示例对于查询返回的所有行，将 OVER 子句与聚合函数一起使用。 在这个示例中，使用 OVER 子句与使用子查询相比，可以更高效地派生聚合值。

USE AdventureWorks2012;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg"
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count"
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min"
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);
GO

下面是结果集：

SalesOrderID ProductID   OrderQty Total       Avg         Count       Min    Max
------------ ----------- -------- ----------- ----------- ----------- ------ ------
43659        776         1        26          2           12          1      6
43659        777         3        26          2           12          1      6
43659        778         1        26          2           12          1      6
43659        771         1        26          2           12          1      6
43659        772         1        26          2           12          1      6
43659        773         2        26          2           12          1      6
43659        774         1        26          2           12          1      6
43659        714         3        26          2           12          1      6
43659        716         1        26          2           12          1      6
43659        709         6        26          2           12          1      6
43659        712         2        26          2           12          1      6
43659        711         4        26          2           12          1      6
43664        772         1        14          1           8           1      4
43664        775         4        14          1           8           1      4
43664        714         1        14          1           8           1      4
43664        716         1        14          1           8           1      4
43664        777         2        14          1           8           1      4
43664        771         3        14          1           8           1      4
43664        773         1        14          1           8           1      4
43664        778         1        14          1           8           1      4

 


SQL语句全攻略：从基础到进阶的编程之旅
邓邓子的博客
07-01 990
本文系统梳理SQL语句的核心知识体系，从基础语法到高级应用层层递进。开篇解析SQL语句的标准写法顺序，深入讲解关联查询（内连接、外连接等）与常用函数操作；继而覆盖数据库与表的创建、增删改查等基础操作，结合一对多、多对多关系场景演示复杂数据关联逻辑。针对性能优化，重点分析索引策略、子查询替代方案等实用技巧；高级部分引入窗口函数、公共表表达式（CTEs）及聚合函数的进阶应用，辅以场景化案例提升理解。无论是数据库新手还是进阶开发者，均可通过本文夯实SQL基础、掌握性能调优方法，实现从语法应用到工程实践的能力提升。
SQL语句全面知识整理与实战指南
qq_45507563的博客
04-24 2259
根据所学内容，对所有SQL语句进行知识性整理，包括但不限于各语句的使用规范、应用规则、易错点、应用实战等
窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE
Ahuuua的博客
10-01 3万+
窗口函数OVER(PARTITION BY)详细用法，PARTITION BY写法，ROWS和RANGE的区别，各种函数共功能说明
SQL中distinct 和 row_number() over() 的区别及用法
12-16
1 前言 在咱们编写 SQL 语句操作数据库中的数据的时候，有可能会遇到一些不太爽的问题，例如对于同一字段拥有相同名称的记录，我们只需要显示一条，但实际上数据库中可能含有多条拥有相同名称的记录，从而在检索的时候，显示多条记录，这就有违咱们的初衷啦！因此，为了避免这种情况的发生，咱们就需要进行“去重”处理啦，那么何为“去重”呢？说白了，就是对同一字段让拥有相同内容的记录只显示一条记录。 那么，如何实现“去重”的功能呢？对此，咱们有两种方式可以实现该功能。 第一种，在编写 select 语句的时候，添加 distinct 关键词； 第二种，在编写 select 语句的时候，调用 row_numbe
row_number() OVER(PARTITION BY)函数介绍
热门推荐
631799的专栏
04-01 12万+
OVER(PARTITION BY)函数介绍 开窗函数                Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。       开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下： 1：over后的写法
SQL窗口函数OVER详细用法，一学就会
扬帆天涯
12-11 5677
SQL窗口函数OVER详细用法，一学就会
SQL over的作用及用法
dichengyan0013的博客
09-05 586
sql over的作用及用法 RANK ( ) OVER ( [query_partition_clause] order_by_clause ) DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause ) 可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序, 其中PARTITION B...
SQL Server 数据库实用SQL语句
12-15
在SQL Server数据库中，了解和熟练运用SQL语句是管理和操作数据的关键。下面将详细解析提供的实用...以上就是SQL Server中涉及的一些实用SQL语句及其背后的原理和用法，掌握这些技巧能更高效地进行数据库操作和管理。
SQL分页语句的三种方案
09-10
此方法的效率通常优于方案一，但仍然需要动态构造SQL语句。 ### 方案二倒序 对于需要按降序排列的数据，我们可以稍微修改上面的方案二，如下所示： ```sql SELECT TOP 页大小 * FROM table1 WHERE ID ( SELECT ...
SQL语句之Over
VincEnt-1998的博客
08-06 1693
over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。 其参数：over（partition by columnname1 order by columnname2） 含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。 例如：employees表中，有两个部门的记录：department...
SQL中over的用法
liuyuehui的专栏
01-15 1万+
在泡坛子的时候中无意中发现了这个函数，才知道oracle分析函数是如此的强大，其中over() 函数的用法又尤为的特别，所以将自己的研究结果记录一下。 　　个人理解：over() 函数 是对 分析函数的一种条件解释，直接点就是 给分析函数加条件吧。 　　在网上看见比较常用的就是 与 sum()、rank() 函数使用。接下来就用分析下两种函数结合over的用法。 　　以下测试使用的orace
Hive中 HQL高级介绍及用法
gym02的博客
12-06 922
SQL关键词执行顺序 from>where条件>group by>having条件>select>order by>limit 注意：一旦slq出现group by，后续的关键词能够操作的字段只有（分组依据字段，组函数处理结果） 常见步骤： 0.各个数据类型的字段访问(array、map、struct) 1. 条件查询：= != >= <= 2. and or between and 3. order by[底层会启动mapreduce进行排序] 4. .
sql 操作：Over
吃肉狐的博客
04-27 1092
一下内容为转载，作为笔记 RANK ( ) OVER ( [query_partition_clause] order_by_clause ) DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause ) 可实现按指定的字段分组排序，对于相同分组字段的结果集进行排序, 其中PARTITION BY 为分组字段，ORDER BY 指...
SQL over关键字(转载)
weixin_30813225的博客
02-16 160
1.over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。其参数：over（partition by columnname1 order by columnname2）含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。例如：employees表中，有两个部门的记录：department_...
数据库over（）函数用法--【SQL Server】
qq_44162236的博客
03-09 2194
开窗函数：over() 将统计出来的数据分布到原表的每一行中 结合聚合函数、排名函数使用 select sc.*,avg(score) over() from sc where sid=1
SQL 窗口函数OVER 关键字简单使用(自己总结)
yoshubom的专栏
02-03 721
SQL 窗口函数OVER 关键字简单使用(自己总结) 环境准备 # 可选，如果本机没装mysql 客户端 brew install mysql-client # 然后导入到PATH 中 echo 'export PATH="/usr/local/opt/mysql-client/bin:$PATH"' >> ~/.bash_profile source ~/.bash_profile ...
SQL关键字大全
最新发布
07-16
<think>嗯，用户需要一份SQL常用关键字的列表和使用说明。从引用内容来看，用户已经查阅过一些资料，但希望获得更系统化的整理。用户可能是数据库初学者或需要速查手册的开发人员。 用户提供的引用很有价值，特别是引用[4]中SQL语句的执行顺序和引用[5]的分析函数部分。需要将这些分散的信息整合成逻辑清晰的分类，同时补充基础关键字如SELECT/FROM等核心内容。 考虑按功能分为六大类：数据查询、数据操作、数据定义、数据控制、事务控制、高级功能。在数据查询部分要强调执行顺序（引用[4]），在高级功能部分重点介绍窗口函数（引用[5]）。对于有歧义的点如AS关键字是否必需，要明确说明（引用[1]）。 特别注意几个技术细节：1) UNION和UNION ALL的区别（引用[2][3]）2) 别名使用规范 3) JOIN的多种类型。示例代码要选择最具代表性的场景，比如窗口函数用排名案例，JOIN用员工部门关联案例。 用户可能后续会追问具体关键字用法，所以最后准备的问题要覆盖语法细节（CASE）、性能影响（EXISTS）、新特性（MERGE）等方向。特别是窗口函数部分，用户引用[5]显示ta已接触过但可能需要深化理解。</think>以下是 SQL 常用关键字列表及其使用说明，按功能分类整理： --- ### **一、 数据查询 (DQL)** 1. **`SELECT`** * **作用**：从数据库表中检索数据。 * **语法**：`SELECT column1, column2, ... FROM table_name;` * **示例**：`SELECT name, salary FROM employees;` 2. **`FROM`** * **作用**：指定要查询数据的表。 * **语法**：`SELECT ... FROM table_name;` * **示例**：`SELECT * FROM orders;` 3. **`WHERE`** * **作用**：过滤记录，指定查询条件。 * **语法**：`SELECT ... FROM ... WHERE condition;` * **示例**：`SELECT * FROM products WHERE price > 100;` 4. **`AS`** * **作用**：为列或表指定别名（可省略，直接用空格分隔）。 * **语法**：`SELECT column_name AS alias_name FROM ...;` 或 `SELECT column_name alias_name FROM ...;` * **示例**：`SELECT name AS employee_name FROM employees;` 或 `SELECT name employee_name FROM employees;` [^1] 5. **`DISTINCT`** * **作用**：返回唯一不同的值（去重）。 * **语法**：`SELECT DISTINCT column1, column2, ... FROM ...;` * **示例**：`SELECT DISTINCT department FROM employees;` 6. **`ORDER BY`** * **作用**：对结果集按指定列排序（默认升序）。 * **语法**：`SELECT ... ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;` * **示例**：`SELECT * FROM customers ORDER BY last_name ASC, first_name DESC;` 7. **`GROUP BY`** * **作用**：将结果集按一列或多列分组（常与聚合函数如 `COUNT`, `SUM`, `AVG` 等一起使用）。 * **语法**：`SELECT ... GROUP BY column1, column2, ...;` * **示例**：`SELECT department, COUNT(*) FROM employees GROUP BY department;` 8. **`HAVING`** * **作用**：对 `GROUP BY` 分组后的结果进行条件过滤（`WHERE` 用于分组前的行过滤）。 * **语法**：`SELECT ... GROUP BY ... HAVING condition;` * **示例**：`SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000;` 9. **`LIMIT` / `FETCH FIRST` / `TOP` (数据库实现相关)** * **作用**：限制返回的记录数。 * **MySQL/PostgreSQL/SQLite 示例**：`SELECT * FROM products LIMIT 10;` * **SQL Server 示例**：`SELECT TOP 10 * FROM products;` * **Oracle 12c+ / ANSI SQL 示例**：`SELECT * FROM products FETCH FIRST 10 ROWS ONLY;` --- ### **二、 数据操作 (DML)** 10. **`INSERT INTO`** * **作用**：向表中插入新记录。 * **语法**：`INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);` * **示例**：`INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');` 11. **`UPDATE`** * **作用**：修改表中现有的记录。 * **语法**：`UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;` * **示例**：`UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';` **（务必使用 `WHERE` 子句避免全表更新！）** 12. **`DELETE`** * **作用**：从表中删除记录。 * **语法**：`DELETE FROM table_name WHERE condition;` * **示例**：`DELETE FROM orders WHERE order_date < '2023-01-01';` **（务必使用 `WHERE` 子句避免清空表！）** --- ### **三、 数据定义 (DDL)** 13. **`CREATE`** * **作用**：创建数据库对象（如表、索引、视图等）。 * **创建表示例**：`CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255));` 14. **`ALTER`** * **作用**：修改现有数据库对象的结构（如添加、删除或修改列）。 * **添加列示例**：`ALTER TABLE employees ADD COLUMN hire_date DATE;` * **修改列示例**：`ALTER TABLE employees ALTER COLUMN salary DECIMAL(10,2);` 15. **`DROP`** * **作用**：删除数据库对象（如表、索引、视图等），**操作不可逆**。 * **删表示例**：`DROP TABLE temp_data;` * **删索引示例**：`DROP INDEX idx_customer_name;` 16. **`TRUNCATE TABLE`** * **作用**：快速删除表中的所有行（比 `DELETE` 快，不记录日志，重置自增ID），**操作不可逆**。 * **语法**：`TRUNCATE TABLE table_name;` * **示例**：`TRUNCATE TABLE audit_log;` --- ### **四、 数据控制 (DCL)** 17. **`GRANT`** * **作用**：授予用户或角色对数据库对象的访问权限。 * **语法**：`GRANT permission_type ON object TO user_or_role;` * **示例**：`GRANT SELECT, INSERT ON employees TO analyst_role;` 18. **`REVOKE`** * **作用**：撤销之前授予用户或角色的权限。 * **语法**：`REVOKE permission_type ON object FROM user_or_role;` * **示例**：`REVOKE DELETE ON customers FROM user_john;` --- ### **五、 事务控制** 19. **`BEGIN TRANSACTION` / `START TRANSACTION`** * **作用**：显式开始一个事务。 * **示例**：`BEGIN TRANSACTION;` 20. **`COMMIT`** * **作用**：提交事务，使所有更改永久生效。 * **示例**：`COMMIT;` 21. **`ROLLBACK`** * **作用**：回滚事务，撤销所有未提交的更改。 * **示例**：`ROLLBACK;` 22. **`SAVEPOINT`** * **作用**：在事务内设置保存点，允许部分回滚。 * **语法**：`SAVEPOINT savepoint_name;` * **回滚到保存点**：`ROLLBACK TO savepoint_name;` --- ### **六、 高级查询与组合** 23. **`JOIN`** * **作用**：基于相关列合并来自两个或多个表的行。 * **主要类型**： * `INNER JOIN`：返回两个表匹配的行。 * `LEFT (OUTER) JOIN`：返回左表所有行，以及右表匹配的行（不匹配则为 `NULL`）。 * `RIGHT (OUTER) JOIN`：返回右表所有行，以及左表匹配的行（不匹配则为 `NULL`）。 * `FULL (OUTER) JOIN`：返回左表和右表所有行，不匹配的部分填充 `NULL`。 * `CROSS JOIN`：返回两个表的笛卡尔积（所有可能的组合）。 * **示例 (INNER JOIN)**： ```sql SELECT orders.order_id, customers.name FROM orders INNER JOIN customers ON orders.customer_id = customers.id; ``` 24. **`UNION`** * **作用**：合并两个或多个 `SELECT` 语句的结果集，**自动去除重复行**。要求列数、列类型和顺序相同。 * **语法**：`SELECT ... UNION SELECT ...;` * **示例**：`SELECT city FROM suppliers UNION SELECT city FROM customers;` [^2] 25. **`UNION ALL`** * **作用**：合并两个或多个 `SELECT` 语句的结果集，**保留所有行，包括重复行**。要求列数、列类型和顺序相同。 * **语法**：`SELECT ... UNION ALL SELECT ...;` * **示例**：`SELECT product_id FROM orders_2023 UNION ALL SELECT product_id FROM orders_2024;` [^3] 26. **`EXISTS`** * **作用**：检查子查询是否返回至少一行记录，返回布尔值（`TRUE`/`FALSE`）。常用于相关子查询。 * **语法**：`WHERE EXISTS (subquery);` * **示例**：`SELECT * FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);` (查找下过订单的客户) 27. **`CASE`** * **作用**：在查询中实现条件逻辑。 * **语法**： ```sql CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE default_result END ``` * **示例**： ```sql SELECT name, CASE WHEN salary < 5000 THEN 'Low' WHEN salary BETWEEN 5000 AND 10000 THEN 'Medium' ELSE 'High' END AS salary_level FROM employees; ``` 28. **窗口函数 (Window Functions)** [^5] * **作用**：对一组相关行（窗口）进行计算，同时保留原始行。 * **语法**：`function_name(...) OVER ([PARTITION BY ...] [ORDER BY ...] [frame_clause])` * **常用函数**： * `ROW_NUMBER()`：为分区内的行分配唯一序号（1,2,3,...）。 * `RANK()`：为分区内的行分配排名（相同值并列，后续排名跳过并列数，如 1,1,3）。 * `DENSE_RANK()`：为分区内的行分配密集排名（相同值并列，后续排名连续，如 1,1,2）。 * `SUM(...) OVER(...)`, `AVG(...) OVER(...)`, `MIN(...) OVER(...)`, `MAX(...) OVER(...)`：计算分区内的累计值、移动平均值等。 * `LEAD(column, offset)` / `LAG(column, offset)`：访问分区内当前行之前/之后指定偏移量的行的值。 * **示例 (ROW_NUMBER)**： ```sql SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees; ``` --- ### **SQL 查询执行顺序 (关键概念)** [^4] 理解 SQL 引擎处理子句的逻辑顺序对编写复杂查询至关重要： 1. **`FROM`** (包括 `JOIN`): 确定数据来源和连接表。 2. **`WHERE`**: 过滤符合条件的行。 3. **`GROUP BY`**: 对过滤后的行进行分组。 4. **`HAVING`**: 过滤分组后的结果。 5. **`SELECT`** (包括计算列、别名、`DISTINCT`): 选择要显示的列并进行计算。 6. **`ORDER BY`**: 对最终结果集排序。 7. **`LIMIT`/`OFFSET`**: 限制返回行数和起始位置。 **示例查询结构**： ```sql SELECT department, AVG(salary) AS avg_sal -- 5. 选择分组后的部门名和计算平均工资，并起别名 FROM employees -- 1. 从员工表取数据 WHERE hire_date > '2020-01-01' -- 2. 筛选入职日期在2020年后的员工 GROUP BY department -- 3. 按部门分组 HAVING AVG(salary) > 6000 -- 4. 筛选平均工资大于6000的部门 ORDER BY avg_sal DESC -- 6. 按平均工资降序排列 LIMIT 5; -- 7. 只返回前5条记录 ``` ---
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值