mysql 窗口函数 -JZTXT

本文引用CSDN博客:

来源:bilibili视频和CSDN

https://blog.csdn.net/mjh1667002013/article/details/128140257

一.什么是窗口函数

基本含义:

窗口限定一个范围，它可以理解为满足某些条件的记录集合，窗口函数也就是在窗口范围内执行的函数。

基本语法：

窗口函数有over关键字，指定函数执行的范围，可分为三部分：分组子句(partition by) 排序子句(order by) 窗口子句(rows)

create table SQL_S(
cid varchar(4),
sname varchar(4),
score INT)
insert into SQL_S values('001','张三',78),('001','李四',82),('002','小明',90),('001','王五',67)
,('002','小红',85),('002','小刚',62);
select *,SUM(score) over(partition by cid) as '班级总分' from SQL_S;

对比GROUP by:

select cid,SUM(score) as '班级总分' from SQL_S group by cid;

2023-09-04

1.创建表:员工表

create table employee2
(
`eid` int not null auto_increment comment '员工id' primary key,
`ename` varchar(20) not null comment '员工名称',
`dname` varchar(50) not null comment '部门名称',
`hiredate` date not null comment '入职日期',
`salary` double null comment '薪资'
) comment '员工表';

2.插入数据:

insert into employee2 (`ename`, `dname`, `hiredate`, `salary`) values ('傅嘉熙', '开发部', '2022-08-20 12:00:04', 9000);
insert into employee2 (`ename`, `dname`, `hiredate`, `salary`) values ('武晟睿', '开发部', '2022-06-12 13:54:12', 9500);
insert into employee2 (`ename`, `dname`, `hiredate`, `salary`) values ('孙弘文', '开发部', '2022-10-16 08:27:06', 9400);
insert into employee2 (`ename`, `dname`, `hiredate`, `salary`) values ('潘乐驹', '开发部', '2022-04-22 03:56:11', 9500);
insert into employee2 (`ename`, `dname`, `hiredate`, `salary`) values ('潘昊焱', '人事部', '2022-02-24 03:40:02', 5000);
insert into employee2 (`ename`, `dname`, `hiredate`, `salary`) values ('沈涛', '人事部', '2022-12-14 09:16:37', 6000);
insert into employee2 (`ename`, `dname`, `hiredate`, `salary`) values ('江峻熙', '人事部', '2022-05-12 01:17:48', 5000);
insert into employee2 (`ename`, `dname`, `hiredate`, `salary`) values ('陆远航', '人事部', '2022-04-14 03:35:57', 5500);
insert into employee2 (`ename`, `dname`, `hiredate`, `salary`) values ('姜煜祺', '销售部', '2022-03-23 03:21:05', 6000);
insert into employee2 (`ename`, `dname`, `hiredate`, `salary`) values ('邹明', '销售部', '2022-11-23 23:10:06', 6800);
insert into employee2 (`ename`, `dname`, `hiredate`, `salary`) values ('董擎苍', '销售部', '2022-02-12 07:54:32', 6500);
insert into employee2 (`ename`, `dname`, `hiredate`, `salary`) values ('钟俊驰', '销售部', '2022-04-10 12:17:06', 6000);

3.使用聚合函数SUm()

select dname,sum(salary) sum from employee2 group by dname;

效果:

窗口