虾想网络定制,网站优化主要工作有那些内容,做文学网站用什么域名,绵阳网站建设 小程序目录 一、over()开窗函数
二、无参over()的使用
三、over(partition by 列名)
四、over(order by 列名 asc/desc)
五、over(partition by 列名 order by 列名 asc|desc)
六、练习#xff08;笔试#xff09; 一、over()开窗函数 拓展:数据库的版本 oracle:8i 9i 10g …目录 一、over()开窗函数
二、无参over()的使用
三、over(partition by 列名)
四、over(order by 列名 asc/desc)
五、over(partition by 列名 order by 列名 asc|desc)
六、练习笔试 一、over()开窗函数 拓展:数据库的版本 oracle:8i 9i 10g 11g 12c 18c 19c mysql:5.x 8.0 (没有7.x、6.x) -- 一、开窗函数 over() --- over函数的使用在select的子句中使用语法 计算函数部分() over(partition by 列名 order by 列名 asc|desc) group by 其中 1、计算函数部分聚合函数、排名函数、平移函数等 只能是一个函数 2、partition by 分组,不是必选项 3、order by排序,不是必选项 4、over()函数有4种使用方法即:参数二选一不带参数全带参数 */ 二、无参over()的使用
-- 一、开窗函数over()的第一种用法over()
-- 1、计算函数部分() over()对整个表格进行计算
-- 例题一查询emp表中的最高工资、最低工资、平均工资
select max(sal),min(sal),avg(sal) from emp
-- 注意1、聚合函数在一个结果集中计算 返回值是一样
-- 2、over() 新生成的一个列 返回的行数是原来表的行数
select
empno,sal,
max(sal) over() maxsal,
min(sal) over() minsal,
avg(sal) over() avgsal
from emp;-- 例题二、找出emp表中工资最高的员工信息----方法1:子查询---a.找出最高的工资select max(sal) from emp; ---b.where条件子查询 单行值子查询select * from emp where sal (select max(sal) from emp) ;----方法2:开窗函数from 子查询---a.开窗函数select emp.*,max(sal) over() maxsal from emp;---b.from 子查询select * from
(select emp.*,max(sal) over() maxsal from emp)where sal maxsal;---with as语句with a as (select emp1.*,max(sal) over() maxsal from emp1)select * from a where sal maxsal;----方法3:开窗from 条件子查询---a.开窗select emp1.*,max(sal) over() maxsal,sal - max(sal) over() c from emp1;---b.from 条件子查询select * from (select emp1.*,max(sal) over() maxsal,sal - max(sal) over() c from emp1)
where c 0;---with as 语句with a as (select emp1.*,max(sal) over() maxsal,sal - max(sal) over() c from emp1)select * from a where c 0;-- 练习三、计算emp表中每个员工你的工资和最高的比值是多少
select emp.*,max(sal) over(),sal/max(sal) over() from emp;
-- 练习四、计算emp表中每个员工的工资和最高工资的差值是多少
select emp.*,max(sal) over(),sal-max(sal) over() from emp;
三、over(partition by 列名)
-- 使用方式二带单个参数、over(partition by 列名)
/*partition by 与 group by的区别group by会将结果集按照字段进行聚合结果集会缩减在统计部门人数平均工资等会用到partition by 会对结果集按照指定字段分层排列结果集不会缩减如将公司全部人
*/-- 在emp表中查询每个部门工资最高的员工信息
-- 1、找出每个部门的最高工资
-- group by 的写法
-- 1.1 对部门分组同时要保持一致性
select deptno,max(sal)
from emp group by deptno
;
-- 1.2 多列子查询
select * from emp
where(deptno,sal) in(select deptno,max(sal)from emp group by deptno
)
;-- 使用开窗函数 over()
-- 1.1 over()
select emp.*,
max(sal) over(partition by deptno) maxsal
from emp;
-- 查询到的数据并不会缩减每个员工的信息都会多出一列当前部门的最高工资-- 1.2 加上条件和from查询
select * from(select emp.*,max(sal) over(partition by deptno) maxsalfrom emp
)where sal maxsal
;-- with as写法
with a as(select emp.*,max(sal) over(partition by deptno) maxsal from emp)
select * from a where sal maxsal;-- 2、在emp表中计算每个人在部门工资总和中所占的比例
-- 2.1 over()开窗
select emp.*,
sum(sal) over(partition by deptno) sumsal
from emp
;
-- 2.2 over开窗form子查询
select * from(select emp.*,sum(sal) over(partition by deptno) sumsal from emp
);-- 2.3 求每个人在工资总和中的比例
select empno,ename,sal,sumsal,sal/sumsal
from(select emp.*,sum(sal) over(partition by deptno) sumsal from emp
);-- 2.4 四舍五入round(值,保留小数位)
select empno,ename,sal,sumsal,round(sal/sumsal,2)
from(select emp.*,sum(sal) over(partition by deptno) sumsal from emp
);四、over(order by 列名 asc/desc) 对整个表对排序的列进行依次的累计运算并列的名次和数据 会当成一个整体进行计算一次性计算 -- row_number()根据某个列按照顺序进行排序 1、2、3、4 -- rank()根据某个列按照顺序进行排序如果值相同会出现并列的名次会跳过占用的名次1、2、2、4 -- dense_rank()根据某个列按照顺序进行排序如果值相同会出现并列的名次不会跳过名次1、2、2、3 --rownum 取行号函数系统关键字只能 不能 从1开始可以 -- 1、对row_number列以sal排序
select emp.*,row_number() over(order by sal) from emp;-- 2、对 rank()列以sal排序
select emp.*, rank() over(order by sal) from emp;-- 3、对dense_rank()列以sal排序
select emp.*,dense_rank() over(order by sal) from emp;-- 4、rownum 以sal升序排序这个需要用到子查询
select a.*,rownum r from
(select * from emp order by sal) a;-- 5、练习在成绩表中查询c001课程成绩的前6~10名-- 5.1、查询到coo1的成绩排序
select * from sc_a01;
select sc_a01.*,row_number() over(partition by cno order by score desc)
from sc_a01
where cno c001
;-- 5.2 合并子查询
select * from (select sc_a01.*,row_number() over(partition by cno order by score desc) rfrom sc_a01where cno c001
)where r between 6 and 10;
五、over(partition by 列名 order by 列名 asc|desc) /* over(partition by 列名 order by 列名 asc|desc) 在每个分组中,对排序的列进行依次的累计运算,并列的名次和数据,会当成一个整体进行计算 */ -- 1、在emp表中找出每个部门的最高工资对应的员工信息
select * from (-- 子查询select emp.*,max(sal) over(partition by deptno order by sal desc) maxsalfrom emp
)where sal maxsal;-- 2、在emp表中找出每个部门的员工的工资和该部门最高工资的差值
select emp.*,max(sal) over(partition by deptno order by sal desc) maxsal,
sal-max(sal) over(partition by deptno order by sal desc) cha from emp;-- 3、在成绩表中计算每门课程前 1-10名的信息
-- 3.1 对成绩做出排序
select a.*,row_number() over(partition by cno order by score desc) r
from sc_a01 a
;-- 3.2 合并子查询
select * from(select a.*,row_number() over(partition by cno order by score desc) r from sc_a01 a
)where r between 1 and 10;-- 4、在成绩表中计算每门课程前1-10名的总分
-- 4.1 已查询到每门课程的前10名
select * from(select a.*,row_number() over(partition by cno order by score desc) r from sc_a01 a
)where r between 1 and 10;-- 4.2 再此基础上加上分组计算
select cno,sum(score) from(select a.*,row_number() over(partition by cno order by score desc) r from sc_a01 a
)
where r between 1 and 10
group by cno
;select * from emp;
-- 5、在emp表中计算每个部门前六名的工资总和
select deptno,sum(sal) from(select e.*,row_number() over(partition by deptno order by sal desc) rfrom emp e
)
where r 60
group by deptno
;
六、练习笔试 /*二、练习题-- case when 条件判断casewhen 条件判断1 then 条件为真when 条件判断2 then 条件为真...else 所有条件都为假的时候endelse 可以省略可以生成一个或多个列*/
-- 1、建表填入数据
create table info(id number,name varchar(20)
)
select * from info;
insert into info values(1,/);
insert into info values(2,A);
insert into info values(3,B);
insert into info values(4,C);
insert into info values(5,/);
insert into info values(6,D);
insert into info values(7,E);
insert into info values(8,/);
insert into info values(9,F);
insert into info values(10,C);
insert into info values(11,H);-- 方法一、
-- 1.1、筛选出不包含/的数据
select * from info where name /;-- 1.2、使用case when语句
select id,name,casewhen id between 2 and 4 then 1when id between 6 and 7 then 2when id between 9 and 11 then 3end group_id
from info where name /;-- 方法二、
-- 2.1 单个结果的查询
select id,name,1 group_id from info where id between 2 and 4;
select id,name,2 group_id from info where id between 6 and 7;
select id,name,3 group_id from info where id between 9 and 11;-- 2.2 拼接三个查询结果使用 union all
select id,name,1 group_id from info where id between 2 and 4
union all
select id,name,2 group_id from info where id between 6 and 7
union all
select id,name,3 group_id from info where id between 9 and 11;-- 方法三、开窗
-- 3.1 筛选没有 / 的
select id,name from info where name /;-- 3.2 对id排序
select id,name,row_number() over(order by id) r from info where name /;--- 3.3完善--用id-row_number 刚好就可以满足到题目条件再以group_id分组
select
id,name,row_number() over(order by id) r,id-row_number() over(order by id) group_id
from info
where name /
;
