-
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
提交
取消