回顾
查询┌简单查询┌语法 select 列1,列2 from 表
│ └别名 select 列1 [as] 列别名,列2 from 表 表别名
└限定查询┌语法 select 列1,列2 from 表 where 条件
└操作符┌比较符 > < >= <= = != <>
├逻辑符 and or not
├between 小值 and 大值
├in (值1,值2,...)
├算数符 +-*/
├空 is null is not null
├连接符 ||
└like '匹配的内容'函数┌系统函数┌单行函数┌数值函数┌abs(数) 绝对值
│ │ │ ├mod(数1,数2) 取余
│ │ │ ├ceil(数) 向上取整
│ │ │ ├floor(数) 向下取整
│ │ │ ├round(数1[,数2]) 四舍五入
│ │ │ ├trunc(数1[,数2]) 截断--不进位
│ │ │ ├sign(数) 判断正负零
│ │ │ └power(数1,数2) 次方和开方
│ │ ├字符函数┌upper(str) 大写
│ │ │ ├lower(str) 小写
│ │ │ ├initcap(str) 首字母大写
│ │ │ ├length(str) 字符长度
│ │ │ ├lengthb(str) 字节长度
│ │ │ ├trim(str) 去两端空格
│ │ │ ├ltrim(str1[,str2]) 去左端字符
│ │ │ ├rtrim(str1[,str2]) 去右端字符
│ │ │ ├lpad(str1,数,str2) 左填充
│ │ │ ├rpad(str1,数,str2) 右填充
│ │ │ ├instr(str1,str2[,数1[,数2]]) 查找字符串位置
│ │ │ ├substr(str,数1[,数2]) 截取字符串
│ │ │ ├replace(str1,str2[,str3]) 整体替换
│ │ │ ├translate(str1,str2,str3) 逐一替换
│ │ │ ├concat(str1,str2) 合并字符串
│ │ │ ├to_single_byte(str) 半宽
│ │ │ └to_multi_byte(str) 全宽
│ │ ├日期函数┌sysdate 当前系统时间
│ │ │ ├
│ │ ├转换函数┌ascii(str) 字符→阿斯克码
│ │ │ ├chr(数) 阿斯克码→字符
│ │ │ ├to_number(str) 纯数字字符→数值
│ │ │ ├to_date(str,'格式') 字符按照格式→日期
│ │ │ └to_char┌to_char(参) 参→字符
│ │ │ ├to_char(日期,'格式') 提取日期中的元素
│ │ │ └to_char(数,'格式') 数→格式化数字
│ │ └通用函数┌userenv('language') 客户端字符集
│ │ ├greatest(参1,参2..) 多个参数中的最大值
│ │ ├least(参1,参2..) 多个参数中的最小值
│ │ ├coalesce(参1,参2..) 多个参数中第一个不为空的值
│ │ ├nvl(参1,参2) 参1为空返回参2,否则返回参1
│ │ ├nvl2(参1,参2,参3) 参1为空返回参3,否则返回参2
│ │ ├distinct 去重
│ │ ├nullif(参1,参2) 参1参2相同返回空,否则返回参1
│ │ ├case when ┌case 列 when 列中值1 then 值1.. [else 值n] end
│ │ │ └case when 条件1 then 值1 .. [else 值n] end
│ │ └decode(列,列中值1,值1,列中值2,值2...[,值n])
│ ├聚合函数┌min(列) 最小值
│ │ ├max(列) 最大值
│ │ ├sum(列) 求和
│ │ ├avg(列) 平均值
│ │ ├count(列) 计数
│ │ └[wmsys].wm_concat(列) 分组连接字符串
│ │ └listagg(列,'分隔符')within group(order by 列)分组连接字符串
│ └分析函数
└自定义函数
--分析函数 开窗函数
分析函数和聚合函数的区别是聚合函数一个组只能看到一个聚合的记录,而
分析函数除了可以看到聚合的记录,还可以看到组内明细的记录
--分析函数的语法 开窗子句
分析函数(列)over(partition by 列 order by 列)
分析函数:
min
max
avg
sum
count
partition by 分组 相当于 group by
--求每个部门的平均工资,同时需要看到每个人的工资
select deptno,sal,avg(sal)over(partition by deptno) A
from emp--查询每个部门的最低工资,每种职位的平均工资
select e.*,
min(sal)over(partition by deptno) 最低工资,
avg(sal)over(partition by job) 平均工资
from emp e --求全表的最高工资,部门最低工资,经理下属人数
select e.*,max(sal)over() 全表最高工资,
min(sal)over(partition by deptno) 部门最低工资,
count(1)over(partition by mgr) 经理下属人数
from emp e--查询SMITH的员工姓名,部门编号,职位
select ename,deptno,job
from emp
where ename='SMITH'--部门最高工资,职位工资总和,部门最早入职日期
select max(sal)over(partition by deptno),
sum(sal)over(partition by job),
min(hiredate)over(partition by deptno)
from emp
--order by代表累计
select sal,sum(sal)over(order by sal asc)
from emp --按行累计求和,在order by的最后加上:rows between unbounded preceding and current row
select sal,sum(sal)over(order by sal asc rows between unbounded preceding and current row) A
from emp--按部门分组,按名字排序降序,查询名字,部门
--累计总工资,按行累计平均工资
select ename,deptno,sal,sum(sal)over(partition by deptno order by ename desc) A,
avg(sal)over(partition by deptno order by ename desc rows between unbounded preceding and current row) B
from emp--排列 必须排序
6.row_number()over([partition by 列] order by 列)
不重复,不跳级 1,2,3,4,5 --计数
7.rank()over([partition by 列] order by 列)
重复,跳一级 1,2,2,4,5 --排名
8.dense_rank()over([partition by 列] order by 列)
重复,不跳级 1,2,2,3,4,5 --颁奖--查询员工姓名,工资,工资排名
select ename,sal,rank()over(order by sal desc) 工资排名
from emp--查询员工姓名,工资,部门,部门内的工资排名
select ename,sal,deptno,rank()over(partition by deptno order by sal desc)
from emp --查询每个部门工资排名第一的人的员工信息 [拓展]
select e.*,rank()over(partition by deptno order by sal desc) 工资排名
from emp e
where 工资排名=1select *
from (select e.*,rank()over(partition by deptno order by sal desc) 工资排名
from emp e)
where 工资排名=1 9.lead(列[,数[,值]])over([partition by] order by) 向上提
10.lag(列[,数[,值]])over([partition by] order by) 向下拉 select sal,lead(sal)over(order by sal desc) 向上提,
lag(sal)over(order by sal desc) 向下拉
from emp select sal,lead(sal,2,250)over(order by sal desc) 向上提,
lag(sal,2,250)over(order by sal desc) 向下拉
from emp --按照工资降序排序
--查询每个人的工资,他下一个人的工资
--查询每个人的工资比他下一个人的工资多多少
--查询每个人的工资是他上一个人的工资的百分之多少(四舍五入保留两位小数)
select sal,lead(sal)over(order by sal desc) 下一个人的工资,
sal-lead(sal)over(order by sal desc) 多多少,
ltrim(round(sal/lag(sal)over(order by sal desc)*100,2)||'%','%') 百分比
from emp
11.first_value(列)over() 第一个值 可以排序
12.last_value(列)over() 最后一个值 如果排序,结果始终是自己 select sal,first_value(sal)over(partition by deptno order by sal desc)
from emp select sal,last_value(sal)over(order by sal desc)
from emp 13.[wmsys.]wm_concat(列)over([partition by 列]) 分组连接字符串,以逗号作为分隔符
14.listagg(列,'分隔符')within group(order by 列)over([partition by 列]) 分组连接字符串select e.*,wm_concat(ename)over(partition by deptno)
from emp e select e.*,listagg(ename,'/')within group (order by ename desc) over(partition by deptno) A
from emp e
--查询员工编号,员工姓名,部门编号,每个部门的"员工编号-员工姓名"
select empno,ename,deptno,wm_concat(empno||'-'||ename)over(partition by deptno)
from emp select empno,ename,deptno,listagg(empno||'-'||ename,'/')within group (order by ename desc) over(partition by deptno) A
from emp小结
分析函数┌分析函数(列)over(partition by 列 order by 列)
├按行累计求和:rows between unbounded preceding and current row
├row_number()over(order by 列) 计数 不重复不跳级
├rank()over(order by 列) 排名 重复跳一级
├dense_rank()over(order by 列) 颁奖 重复不跳级
├lead(列[,数[,值]])over(order by 列) 向上提
├lag(列[,数[,值]])over(order by 列) 向下拉
├first_value(列)over() 第一个值 可以排序
├last_value(列)over() 最后一个值 不可以排序
├wm_concat(列)over() 分组连接字符串
└listagg(列,'分隔符')within group (order by 列)over([partition by 列]) 分组连接字符串本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
