--通用函数
1、userenv('language') 查看当前客户端使用的字符集
SELECT USERENV('LANGUAGE') FROM DUAL;
2、GREATEST(参1,参2,参3。。。。) 多个参数中的最大值
3、least(参1,参2,。。。。) 多个参数中的最小值
注：参数的类型必须一致
如果参数中有null 那么最后的结果就是空(数值比较null最大)
SELECT GREATEST('A','B','C'),GREATEST(1,2,3,4),
GREATEST(DATE'2008-1-1',DATE'2012-12-20',SYSDATE),
GREATEST(1,22,44,NULL) FROM DUAL;
SELECT LEAST('A','B','C'),LEAST(1,2,3,4),
LEAST(DATE'2008-1-1',DATE'2012-12-20',SYSDATE),
LEAST(1,22,44,NULL) FROM DUAL;
4、coalesce(参1,参2,参3。。。) 多个参数中第一个不为空的值
注：参数的类型必须一致
SELECT COALESCE(NULL,1,2,3), COALESCE(NULL,NULL,'A','B','C'),
COALESCE(NULL,DATE'2020-1-23',SYSDATE) FROM DUAL;
--查询每个人的姓名和月薪（月薪=工资+提成）
SELECT ENAME,SAL,COMM,SAL+COALESCE(COMM,0) 月薪 FROM EMP;
5、nvl(参1,参2) 参1为空 返回参2 否则返回参1
注：参数类型必须是一致的
SELECT ENAME,SAL,COMM,SAL+NVL(COMM,0) FROM EMP;
--查询员工姓名和经理编号 如果经理编号为空 返回大哥
SELECT ENAME,MGR,NVL(TO_CHAR(MGR),'大哥') FROM EMP;
6、nvl2(参1,参2,参3) 参1为空返回参3 否则返回参2
--如果经理编号为空 返回老大 否则返回小弟
SELECT EMPNO,MGR,NVL2(MGR,'小弟','老大') FROM EMP;
--如果comm列为空或者为0 返回无奖金 否则返回有奖金
SELECT ENAME,COMM,NVL2(REPLACE(COMM,'0'),'有奖金','无奖金') 奖金 FROM EMP;
7、distinct 去重
注：多列去重时 只有完全一致才会去重
SELECT DISTINCT DEPTNO FROM EMP;
SELECT DISTINCT DEPTNO,JOB FROM EMP;
8、CASE WHEN
(一) CASE 列 WHEN 列中值1 THEN 值1
WHEN 列中值2 THEN 值2
WHEN 3 THEN 值3
............
[ELSE 值n]
END
--如果部门是10 显示十部门 20 显示20部门 30 显示三十部门
SELECT DEPTNO,CASE DEPTNO WHEN 10 THEN '十部门'
WHEN 20 THEN '二十部门'
ELSE '三十部门'
END 中文部门
FROM EMP;
--查询员工表中的职位和对应的中文含义
SELECT JOB,CASE JOB WHEN 'CLERK' THEN '职员'
WHEN 'SALESMAN' THEN '销售'
WHEN 'MANAGER' THEN '经理'
WHEN 'ANALYST' THEN '分析员'
ELSE '懂事长'
END 中文职位
FROM EMP;
(二) CASE WHEN 条件1 THEN 值1
WHEN 条件2 THEN 值2
WHEN 条件3 THEN 值3
[ELSE 值n]
end
--如果部门是10 显示十部门 20 显示20部门 30 显示三十部门
SELECT DEPTNO,CASE WHEN DEPTNO=10 THEN '十部门'
WHEN DEPTNO=20 THEN '二十部门'
ELSE '三十部门'
END 中文部门
FROM EMP;
----查询员工表中的职位和对应的中文含义
/*如果工资大于3000 返回一级 大于2000 返回二级 大于1000 返回三级 否则 返回穷鬼 */
SELECT SAL,CASE WHEN SAL>3000 THEN '一级'
WHEN SAL>2000 THEN '二级'
WHEN SAL>1000 THEN '三级'
ELSE '穷鬼'
end 等级
FROM EMP;
/如果入职日期在1981年之前 返回部门编号 在1981~1982年 返回入职的年份如果在1982年之后 返回入职的月份/
SELECT HIREDATE,CASE WHEN TO_CHAR(HIREDATE,'YYYY')
WHEN TO_CHAR(HIREDATE,'YYYY') IN(1981,1982) THEN
TO_CHAR(HIREDATE,'YYYY')
ELSE TO_CHAR(HIREDATE,'MM')
END AAAAA
FROM EMP;
9、DECODE(列,列中值1,值1,列中值2,值2,列中值3,值3。。。。[值N])
注：等价于case when的第一种语法
SELECT DEPTNO,DECODE(DEPTNO,10,'十部门',20,'二十部门','三十部门') 中文部门
FROM EMP;
--员工表中职位和中文职位
SELECT JOB,DECODE(JOB,'CLERK','职员','SALESMAN','销售','MANAGER','经理',
'ANALYST','分析员','董事长') 中文职位
FROM EMP;
10、nullif(参1,参2) 判断两个参数是否相等 相等返回空 不等返回参数1
两个参数类型必须一致 参数1不能为空
sql中有除法运算时 为了避免除数为0报错 可以使用这个函数
NULLIF(除数,0)
--查询工资是提成的多少倍
SELECT ENAME,SAL,COMM,SAL/NULLIF(COMM,0) 倍数 FROM EMP;
--聚合函数（分组函数）
注：空值不会参与计算
1、min()
2、max()
3、avg() --只能是数值
4、sum() --只能是数值
5、count()
SELECT MIN(SAL),MAX(SAL),AVG(SAL),SUM(SAL),COUNT(COMM),COUNT(SAL)
FROM EMP; --把整张表看成一个小组
SELECT COUNT(1),COUNT(*),COUNT(COMM)
FROM EMP;
COUNT(1) COUNT() COUNT(列)的区别？count(列) 不会统计列中的空值count（1） count() 统计小组中有多少条数据 速度：count(主键/索引列)>count(*)>count(常数)>count(列)
--查询最大和最小的员工姓名 最大最小的入职日期 平均工资 有奖金的人数
SELECT MAX(ENAME),MIN(ENAME),MAX(HIREDATE),MIN(HIREDATE),
AVG(SAL),COUNT(COMM)
FROM EMP;
分组 GROUP BY 列
--查询每个部门的人数和平均工资
SELECT DEPTNO,COUNT(1),AVG(SAL) FROM EMP
GROUP BY DEPTNO;
--查询每种职位的人数 平均工资 最高工资 总工资
SELECT JOB,COUNT(1),AVG(SAL),MAX(SAL),SUM(SAL) FROM EMP
GROUP BY JOB;
--查询每个经理的下属人数 有奖金的人数 最低工资
SELECT COUNT(1),COUNT(COMM),MIN(SAL) FROM EMP
GROUP BY MGR;
--查询每个部门每种职位最早 最晚的入职日期
SELECT DEPTNO,JOB,MIN(HIREDATE),MAX(HIREDATE) FROM EMP
GROUP BY DEPTNO,JOB;
分组之后的筛选 having
--查询每个职位的人数 只显示人数大于3的
SELECT JOB,COUNT(1) FROM EMP
GROUP BY JOB
HAVING COUNT(1)>3;
--查询部门和部门的最高工资 只显示最高工资大于2900的部门
SELECT DEPTNO,MAX(SAL) FROM EMP
GROUP BY DEPTNO
HAVING MAX(SAL)>2900;
--查询最早的入职日期在1981-6-1之前的职位
SELECT JOB,MIN(HIREDATE) FROM EMP
GROUP BY JOB
HAVING MIN(HIREDATE)<DATE'1981-6-1';
--查询每个经理的下属的平均工资 只显示平均工资在1000~2000的经理
SELECT MGR,AVG(SAL) FROM EMP
GROUP BY MGR
HAVING AVG(SAL) BETWEEN 1000 AND 2000;
排序 ORDER BY 列 ASC|DESC
注：order by后面可以加多个列 当前面的列值相等时按照后面的列排序
null last 将null 值放在最后
null first 将null值放在前面
SELECT * FROM EMP
ORDER BY SAL,ENAME DESC;
SELECT * FROM EMP
ORDER BY 6 DESC;
--数字表示select后面的第几个列
--查询每个部门的平均工资 按照部门升序
SELECT DEPTNO,AVG(SAL) FROM EMP
GROUP BY DEPTNO
ORDER BY 1;
where 分组前的筛选->分组->having 分组后的筛选
--查询每个部门中名字包含A的员工人数 只显示人数小于3的
SELECT DEPTNO,COUNT(1) FROM EMP
WHERE ENAME LIKE'%A%'
GROUP BY DEPTNO
HAVING COUNT(1)<3;
--统计员工表 每个职位上半年入职的人数 只显示人数小于3的
SELECT JOB,COUNT(1) FROM EMP
WHERE TO_CHAR(HIREDATE,'MM')<7
GROUP BY JOB
HAVING COUNT(1)<3;
--查询工资小于3000的员工每年入职的平均工资 显示平均工资在1000~3000 按入职年
--份升序
SELECT TO_CHAR(HIREDATE,'YYYY'),AVG(SAL) FROM EMP
WHERE SAL<3000
GROUP BY TO_CHAR(HIREDATE,'YYYY')
HAVING AVG(SAL) BETWEEN 1000 AND 3000
ORDER BY 1;
6、wm_concat(列) 分组连接字符串
只能使用逗号分隔 不能排序
--查询每个部门的员工姓名
SELECT DEPTNO,WM_CONCAT(ENAME) FROM EMP
GROUP BY DEPTNO;
7、listagg(列,'分隔符')within group(order by 列) 分组连接字符串
可以执行分隔符 还可以执行排序的列
SELECT DEPTNO,LISTAGG(ENAME,'/')WITHIN GROUP(ORDER BY SAL DESC)
FROM EMP GROUP BY DEPTNO;
NULL的小结：
null 不参与统计
空不占存储 空不等于0 不等于空格 也不完全等价于 '' --空字符串
对一行值进行计算 单行函数/四则运算 空和任何值最后的结果都是空
对一列值值进行计算 聚合函数/分析函数 空不参与运算
可以使用NVL/NVL2/COALESCE对空进行处理
--执行顺序
SELECT 5
FROM 1
WHERE 2
GROUP BY 3
HAVING 4
ORDER BY 6
WHERE和HAVING的区别？
1、where是对分组前的数据做筛选
having是对分组后的数据做筛选
2、where后面不能加聚合函数
having后面可以加聚合函数
--分析函数（开窗函数）
分析函数和聚合函数的区别？
聚合函数一个组只能看到一个聚合后的结果
分析函数既可以看到聚合后的结果 还能看到组内的详细信息
语法：
函数名([列])over([partition by 列])
1、min()
2、max()
3、avg() --数值
4、sum() --数值
5、count()
--查询每个部门的最低工资
SELECT DEPTNO,MIN(SAL)
FROM EMP
GROUP BY DEPTNO;
SELECT E.*,MIN(SAL)OVER(PARTITION BY DEPTNO) AAAA
FROM EMP E;
--查询每个职位的平均工资 全表有提成的人数
SELECT ENAME,SAL,JOB,AVG(SAL)OVER(PARTITION BY JOB) 职位平均工资,
COUNT(COMM)OVER() 全表有提成人数
FROM EMP;
--查询每个人的姓名 部门编号 经理编号 部门最低工资 经理下属人数 全表最高工资
SELECT ENAME,DEPTNO,MGR,MIN(SAL)OVER(PARTITION BY DEPTNO) 部门最低,
COUNT(1)OVER(PARTITION BY MGR) 经理下属数,
MAX(SAL)OVER() 全表最高
FROM EMP;
--查询全表除10部门外 多少人有提成
SELECT COUNT(COMM)OVER()
FROM EMP
WHERE DEPTNO!=10;
--如果要求累计值 只要在over中加上order by即可
-按值累计
--求平均工资 工资总和
SELECT SAL,AVG(SAL)OVER(ORDER BY SAL) 平均工资,
SUM(SAL)OVER(ORDER BY SAL) 工资和
FROM EMP;
-按行累计 在以上的基础上加上
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
SELECT SAL,AVG(SAL)OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 平均工资,
SUM(SAL)OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 工资和
FROM EMP;
--按照部门分组按照名字排序 查询名字 部门 累计总工资 累计平均工资
SELECT ENAME,DEPTNO,SAL,SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) 累计总,
AVG(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) 累计平均
FROM EMP
ORDER BY ENAME;
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 5 5 8
8、DENSE_RANK()OVER([PARTITION BY] ORDER BY) --必须排序
并列不跳级 1 2 2 3 3 4 5
--查询员工姓名 工资 工资的排名
SELECT ENAME,SAL,ROW_NUMBER()OVER(ORDER BY SAL DESC) 排名A,
rank()over(order by sal desc) 排名B,
DENSE_RANK()OVER(ORDER BY SAL DESC) 排名C
FROM EMP;
--查询姓名 部门 工资 以及工资在每个部门内的排名
SELECT ENAME,DEPTNO,SAL,ROW_NUMBER()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) 排名
FROM EMP;
9、LEAD(列[,数[,值]])over([PARTITION BY 列]ORDER BY 列) --必须排序
向上拉
10、LAG(列[,数[,值]])over([PARTITION BY 列]ORDER BY 列) --必须排序
向下拉
数：拉几行 不写默认拉1行
值：上啦或下拉之后要填充的内容 必须和列中类型一致 默认是空
--查询每个人的工资和下一个人的工资
SELECT ENAME,SAL,LEAD(SAL)OVER(ORDER BY SAL) 下个人的工资,
LEAD(SAL,2,0)OVER(ORDER BY SAL) 下下个人工资
FROM EMP;
--按照工资降序 查询每个人的工资比上一个人的工资少多少
SELECT ENAME,SAL,LAG(SAL)OVER(ORDER BY SAL DESC)上个人的工资,
LAG(SAL)OVER(ORDER BY SAL DESC)-SAL 少多少
FROM EMP;
11、WM_CONCAT(列)OVER() 分组连接字符串
12、LISTAGG(列,'分割符')WITHIN GROUP(ORDER BY 列)OVER() 分组连接字符串
SELECT ENAME,DEPTNO,WM_CONCAT(ENAME)OVER(PARTITION BY DEPTNO)
FROM EMP;
SELECT ENAME,DEPTNO,LISTAGG(ENAME,'/')WITHIN GROUP(ORDER BY SAL)OVER(PARTITION BY DEPTNO)
FROM EMP;
--查询每个人的姓名 职位 以及每种职位对应的人名
SELECT ENAME,JOB,WM_CONCAT(ENAME)OVER(PARTITION BY JOB)
FROM EMP;
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
