oracle函数

计算工资的平均,中位数,最小值,最大值

SELECT AVG(sal) AS mean_sal,
MEDIAN(sal) AS media_sal,
MIN(sal) AS min_sal,
MAX(sal) AS max_sal
FROM emp;
在这里插入图片描述

查看每个部门的内容,分类汇总

SELECT deptno,
AVG(sal) AS mean_sal,
MEDIAN(sal) AS media_sal,
MIN(sal) AS min_sal,
MAX(sal) AS max_sal
FROM emp
GROUP by deptno;
在这里插入图片描述

显示原始内容,对比的看

SELECT deptno,ename,sal,
AVG(sal) OVER () AS mean_sal,
MEDIAN(sal) OVER () AS media_sal,
MIN(sal) OVER () AS min_sal,
MAX(sal) OVER () AS max_sal
FROM emp;
在这里插入图片描述

显示每个部门的平均薪水以及每个部门的员工数据

SELECT deptno,ename,sal,
AVG(sal) OVER (PARTITION BY deptno) AS mean_sal,
MEDIAN(sal) OVER (PARTITION BY deptno) AS media_sal,
MIN(sal) OVER (PARTITION BY deptno) AS min_sal,
MAX(sal) OVER (PARTITION BY deptno) AS max_sal
FROM emp;
在这里插入图片描述

使用计算

SELECT deptno,ename,sal,
MIN(sal) OVER (PARTITION BY deptno) AS min_sal,
sal-MIN(sal) OVER (PARTITION BY deptno) AS sal_diff
FROM emp;
在这里插入图片描述
FIRST_VALUE:返回的第一个结果

返回第一条工资

SELECT empno,ename,job,sal,
FIRST_VALUE(sal) OVER () AS FIRST_sal
FROM emp;
在这里插入图片描述

进行排列

SELECT empno,ename,job,sal,
FIRST_VALUE(sal) OVER (ORDER BY sal) AS FIRST_sal
FROM emp;
在这里插入图片描述

进行计算

SELECT empno,ename,job,sal,
FIRST_VALUE(sal) OVER (ORDER BY sal) AS lowest_sal,
sal-FIRST_VALUE(sal) OVER (ORDER BY sal) AS sal_diff
FROM emp;
在这里插入图片描述

每个部门的最低工资

在这里插入图片描述## 显示部门最高和最低工资
SELECT empno,deptno,sal,
FIRST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal ) AS zuidi,
FIRST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal DESC) AS zuigao
FROM emp;
在这里插入图片描述
LAST_VALUE:回的最后一个结果
SELECT empno,deptno,sal,
LAST_VALUE(sal) OVER () AS zuihou
FROM emp;
在这里插入图片描述
SELECT empno,deptno,sal,
LAST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal ) AS zuidi
FROM emp;
在这里插入图片描述
指定边界
SELECT empno,deptno,sal,
LAST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS zuidi
FROM emp;
在这里插入图片描述
查询最高和最低工资
SELECT empno,deptno,sal,
FIRST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal) AS zidi,
LAST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS zuidi
FROM emp;
在这里插入图片描述
1:偏移量 表示结果集的前一行,没有前一行显示成0
SELECT empno,deptno,sal,
LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev
FROM emp;
它去除第一行,显示成设置的0
在这里插入图片描述
用于计算
SELECT empno,deptno,sal,
LAG(sal, 1,0) OVER (ORDER BY sal) AS sal_prev,
sal-LAG(sal, 1,0) OVER (ORDER BY sal) AS sal_diff
FROM emp;
在这里插入图片描述
SELECT empno,deptno,sal,
LAG(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal) AS sal_prev
FROM emp;
在这里插入图片描述
LEAD:函数
它是把0补下面
SELECT empno,deptno,sal,
LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
FROM emp;
在这里插入图片描述

RANK函数

进行大小排列
SELECT empno,deptno,sal,
RANK() OVER ( ORDER BY sal) AS myrank
FROM emp;
在这里插入图片描述
SELECT empno,deptno,sal,
DENSE_RANK()OVER( ORDER BY sal)as myrank from
emp;
在这里插入图片描述
SELECT empno,deptno,sal,
RANK()OVER(PARTITION BY deptno ORDER BY sal)as myrank from
emp;
在这里插入图片描述

LISTAGG函数

SELECT deptno, LISTAGG(ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno
ORDER BY deptno;
在这里插入图片描述
插入两条重名的人
INSERT INTO emp VALUES (7940,‘MILLER’,‘CLERK’,7782,to_date(‘23-1-1982’,‘dd-mm-yyyy’),1300,NULL,10);
在这里插入图片描述
取消重名的人
在这里插入图片描述
增加DISTINCT关键字
SELECT deptno, LISTAGG(DISTINCT ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno
ORDER BY deptno;
在这里插入图片描述
19C以前的解决方法,我是记不下来
SELECT e2.deptno, LISTAGG(e2.ename, ‘,’) WITHIN GROUP (ORDER BY e2.ename) AS employees
FROM (SELECT DISTINCT e.deptno, e.ename
FROM emp e) e2
GROUP BY e2.deptno
ORDER BY e2.deptno;
在这里插入图片描述
SELECT e2.deptno, LISTAGG(e2.ename, ‘,’) WITHIN GROUP (ORDER BY e2.ename) AS employees
FROM (SELECT e.*,
ROW_NUMBER() OVER (PARTITION BY e.deptno, e.ename ORDER BY e.empno) AS myrank
FROM emp e) e2
WHERE e2.myrank = 1
GROUP BY e2.deptno
ORDER BY e2.deptno;
在这里插入图片描述

©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页