Oracle 之高级查询

最新推荐文章于 2022-10-28 16:08:04 发布

宁漂打工仔

最新推荐文章于 2022-10-28 16:08:04 发布

阅读量246

点赞数

分类专栏： # Oracle

本文链接：https://blog.csdn.net/SpringHASh/article/details/106621827

版权

Oracle 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1、常用的分组函数：AVG,SUM,MIN,MAX,COUNT,WM_CONCAT

AVG:平均值,SUM:最大值,MIN:最大值,MAX:最小值,COUNT:计算,WM_CONCAT:行转列

select avg(sal),sum(sal) from emp;
select max(sal),min(sal) from emp;
select count(empno) from emp;
select count(distinct depno) from emp;

set linesize 200
col 部门中员工的编号 for a60
select deptno 部门号,wm_concat(ename) 部门中员工的编号 from emp group by deptno;

2、分组函数和空值：

select sum(comm)|count(*) avg_comm,sum(comm)/count(comm) avg_comm,avg(comm) avg_comm from emp;

分组函数会自动过滤掉空值，所以执行结果不一样。oracle中使用NVL函数使分组函数无法忽略空值。

NVL(value,def_value):当value为空时，返回def_value。

select count(comm),count(nvl(comm,0)) from emp;

3、group by子句：

select a,b,c,组函数(X) from table group by a,b,c;
先按a进行分组，a相同的看b，b相同的看c，如果都相同，则为一组。

注意：在select列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中;包含在GROUP BY子句中的列不必包含在SELECT列表中。

select count(id) num from T_MONITOR_ONLINE_ROOM where in_room = 1 
GROUP BY IN_ROOM_DATE
HAVING count(id) > 45

显示部门的平均工资：部门号，平均工资
select deptno,avg(sal) from emp group by deptno;
按部门不同的职位，统计员工的工资总额
select deptno,job,sum(sal) from emp group by deptno,job order by deptno;

非法使用组函数：
select deptno,count(ename) from emp;
错误：所有包含于select列表中，而未包含于组函数中的列都必须包含在GROUP BY子句中。

4、过滤分组——having子句

HAVING group_condition

平均工资大于2000的部门：
select deptno,sum(sal) from emp group by deptno having sum(sal)>2000;

同样都是过滤数据，where和having的区别：不能在where子句中使用组函数，可以在having子句中使用组函数。

select deptno,avg(sal) from emp group by deptno having deptno=10;
select deptno,avg(sal) from emp where deptno=10 group by deptno;

如果过滤条件中没有分组函数时，where与having通用，那么从sql优化的角度来讲，where的效率更高，因为having是先分组再过滤，而where是先过滤再分组，所以，同等条件下，尽量使用where。

5、使用order by子句进行排序

select deptno,avg(sal) from emp group by deptno order by 2 –select表达式的数目

–a命令 append，追加到上一个命令后面。注意必须添加两个及两个以上的空格
a desc

6、嵌套分组函数

求出平均工资的最大值
select max(avg(sal)) from emp group by deptno;

7、group by语句增强–适用于报表

group by rollup(a,b)–先对a,b分组；在对a分组；最后不分组

select deptno,job,sum(sal) from emp group by rollup(deptno,job);
= select deptno,job,sum(sal) from emp group by deptno,job+select deptno,job,sum(sal) from emp group by deptno+select deptno,job,sum(sal) from emp

break on deptno skip 2–相同的部门号只显示一次，不同的部门号空两行

8、SQL/PLUS报表功能

ttitle col 15 ‘我的报表’ col 35 sql.pno
col deptno heading 部门号
col job heading 职位
col sum(sal) heading 工资总额
break on deptno skip 1

将设置保存为.sql格式的文件，把它保存到一个目录下，然后我们可以在sqlplus中把这个文件用get语句加上路径读取进来，然后我们要执行的话就输入一个@然后加上路径，这样格式就设置好了，我们就可以执行sql语句了，执行sql语句后就会显示成我们设置的格式。

多表查询

多个表连接进行查询，数学理论——笛卡尔积。

1、等值连接

连接条件是‘=’号

select e.empno,e.ename,e.sal,d.dname
from emp e,dept d
where e.deptno=d.deptno–等号连接，等值连接

2、不等值连接

连接条件不是‘=’号

select e.empno,e.ename,s.grade
from emp e,salgrade s
where e.sal betweem s.losal and s.hisal–between and 小值在前，大值在后