-
select count(*) Total, sum(decode(to_char(hiredate,'YYYY'),'1980',1,0)) "1980", sum(decode(to_char(hiredate,'YYYY'),'1981',1,0)) "1981", sum(decode(to_char(hiredate,'YYYY'),'1982',1,0)) "1982", sum(decode(to_char(hiredate,'YYYY'),'1987',1,0)) "1987" from emp;//按部门统计员工人数 =select (select count(*) from emp) Total, (select count(*) from emp where to_char(hiredate,'YYYY')='1980') "1980", (select count(*) from emp where to_char(hiredate,'YYYY')='1981') "1981", (select count(*) from emp where to_char(hiredate,'YYYY')='1982') "1982",(select count(*) from emp where to_char(hiredate,'YYYY')='1987') "1987" from dual;查看全部
-
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);//比较优先 ---------------------------------------------------------- select * from table(dbms_xplan.display); select e.empno,e.ename,e.sal,d.avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno) d where e.deptno=d.deptno and e.sal>d.avgsal;查看全部
-
select rownum,r,empno,ename,sal from (select rownum r,empno,ename,sal from (select rownum,empno,ename,sal from emp order by sal desc) e1 where rownum<=8) e2 where r>=5;查看全部
-
rownum只能使用<,<=,不能使用>,>=查看全部
-
select * from emp where empno not in (select mgr from emp where mgr is not null);查看全部
-
单行子查询只能使用单行操作符；多行子查询只能使用多行操作符 select * from emp where job=(select job from emp where empno=7566) and sal>(select sal from emp where empno=7782); select * from emp where sal=(select min(sal) from emp); select deptno,min(sal) from emp group by deptno having min(sal) >(select min(sal) from emp where deptno=20); select min(sal) from emp where deptno=20; select * from emp where deptno in (select deptno from dept where dname='SALES' or dname='ACCOUNTING');= select e.* from emp e,dept d where e.deptno=d.deptno and (d.dname='SALES' or d.dname='ACCOUNTING'); select * from emp where sal>any(select sal from emp where deptno=30);= select * from emp where sal >(select min(sal) from emp where deptno=30); select * from emp where sal>all(select sal from emp where deptno=30);= select * from emp where sal>(select max(sal) from emp where deptno=30);查看全部
-
一般先执行子查询，再执行主查询；但相关子查询例外 select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal>(select avg(sal) from emp where deptno=e.deptno);查看全部
-
一般不在子查询中，使用排序；但在Top-N分析问题中，必须对子查询排序 行号永远按照默认的顺序生成 行号只能使用<,<=;不能使用>,>=; select rownum,empno,ename,sal from emp; select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum<=3;查看全部
-
主查询和子查询可以不是同一张表 select * from emp where deptno=(select deptno from dept where dname='SALES');= select e.* from emp e,dept d where e.deptno=d.deptno and d.dname='SALES';查看全部
-
from后面的子查询 select * from (select empno,ename,sal,sal*12 annsal from emp);查看全部
-
select deptno,avg(sal) from emp group by deptno having avg(sal) >(select max(sal) from emp where deptno=30); select * from (select empno,ename,sal from emp);查看全部
-
主查询和子查询可以不是同一张表 一般不在子查询中，使用排序；但在Top-N分析问题中，必须对子查询排序 一般先执行子查询，再执行主查询；但相关子查询例外 单行子查询只能使用单行操作符；多行子查询只能使用多行操作符 注意:子查询中是null值问题查看全部
-
不可以使用子查询的位置:group by 强调：from后面的子查询查看全部
-
自连接存在的问题: 1.不适合操作大表 2.本质上是一个层次查询 select level,empno,ename,sal,mgr from emp connect by prior empno=mgr start with mgr is null order by 1; start with empno=7839 connect by 上一层的员工号=老板号查看全部
-
通过别名,将同一张表视为多张表 select e.ename 员工姓名,b.ename 老板姓名 from emp e,emp b where e.mgr=b.empno;查看全部
举报
0/150
提交
取消