MYSQL 寒假自学 2022 十二（八）

最新推荐文章于 2024-11-13 22:17:00 发布

鹤纸千丶

最新推荐文章于 2024-11-13 22:17:00 发布

阅读量168

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_51415695/article/details/123012750

版权

本文深入探讨了SQL中的窗口函数，包括如何使用SUM()函数进行分组内累计求和，通过ORDER BY子句控制计算范围。从无边界到有边界，从当前行到后续行的各种情况逐一解析，展示了窗口函数在数据聚合和分析中的灵活性。

摘要由CSDN通过智能技术生成

use mydb4;
select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname order by hiredate) as pv1
from employee;


select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname ) as pv1
from employee;# 将order by 删去，全部求和

select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname order by hiredate) as pv1
from employee;

-- 控制相加的范围
select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname order by hiredate rows between unbounded preceding and current row) as pv1
from employee; #从分组后的开始行加到当前行

select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname order by hiredate rows between 3 preceding and current row) as pv1
from employee; #从分组后,当前行的前三行一直加到当前行

select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname order by hiredate rows between 3 preceding and 1 following) as pv1
from employee; #从分组后,当前行的前三行一直加到当前行的后一行

select 
 dname,
 ename,
 hiredate,
 salary,
 sum(salary) over (partition by dname order by hiredate rows between current row and  unbounded following) as pv1
from employee; #从当前行一直加到最后一行