【正文】 8001000 10012000 20015000 2 3 6 select * from(select count(*)as “8001000” from emp where sal =800 and sal =1001 and sal =2001 and sal 或顯示成為DEPTNO 8002000 2001500030 5 1 20 2 3 10 1 2 select ,“8002000”,“20015000” from(select deptno,count(*)as “8002000” from emp where sal between 800 and 2000 group by deptno)t join(select deptno,count(*)as “20015000” from emp where sal between 2001 and 5000 group by deptno)t1 on = 。union select 39。相當(dāng)于select * from emp where deptno=10 or deptno=20而下面的語句select * from emp where deptno in(10,20)minus select * from emp where sal 1500。 and deptno = 10。select * from emp where ename like 39。select * from(select ename,sal from emp order by sal desc)t where rownum=5 and rownum所以再套一層select select * from(select t.*,rownum r from(select ename,sal from emp order by sal desc)t)where r=5 and r還有一種排序方式 select * from(select * from emp order by sal desc)where rownum2,取前5行求每個(gè)部門中薪水最高的前兩名雇員1,每個(gè)員工的姓名，部門，工資,按部門和工資(倒序)排列select ename,deptno,sal from emp order by deptno,sal desc2,套一層,加上個(gè)r select ename,deptno,sal,rownum r from(select ename,deptno,sal from emp order by deptno,sal desc)t3,創(chuàng)建試圖create or replace view v1 as select ename,deptno,sal,rownum r from(select ename,deptno,sal from emp order by deptno,sal desc)t觀察一下select * from v1每個(gè)部門中,薪水最高的第一行,并創(chuàng)建試圖 create or replace view v2 as select deptno,min(r)min_r from v1 group by deptno兩個(gè)view跨表連接,大于薪水最高的行數(shù),小于最高的行數(shù)+1,并且部門編號(hào)要匹配 select ename from v1 join v2 on( = and = and 面試題: 比較效率select * from emp where deptno = 10 and ename like 39。每個(gè)部門最高薪水的人員名稱1,首先將每個(gè)部門的最高薪水求出來2,然后把結(jié)果當(dāng)成一張表，再用emp和這張結(jié)果表做連接，以此求得每個(gè)部門最高薪水的人員名稱select deptno,max(sal)from emp where deptno is not null group by deptnoselect ename from emp e join(select deptno,max(sal)max_sal from emp where deptno is not null group by deptno)t on sal = max_sal and = 哪些人的薪水在部門的平均薪水之上1,首先將每個(gè)部門的平均薪水求出來2,然后把結(jié)果當(dāng)成一張表，再用emp和這張結(jié)果表做連接，以此求得哪些人的薪水在部門的平均薪水之上select deptno,avg(sal)avg_sal from emp group by deptnoselect * from emp join(select deptno,avg(sal)avg_sal from emp group by deptno)t on(salavg_sal and =)求部門中(所有人的)平均的薪水等級(jí),形式如：deptno avg_grade10 ,先求每個(gè)人的薪水等級(jí)2,再按照部門分組,求平均數(shù)select deptno,sal,grade from emp join salgrade on sal between losal and hisal select deptno,avg(grade)from(select deptno,sal,grade from emp join salgrade on sal between losal and hisal)t group by deptno使用偽字段:rownum，用來標(biāo)識(shí)每條記錄的行號(hào)，行號(hào)從1開始，每次遞增1select rownum,emp.* from emp。39。%%%39。特殊字符需要轉(zhuǎn)義。求名字中的第二個(gè)字母是A的員工select * from emp where ename like 39。and or not的用法求薪水大于1000或者部門在20這個(gè)部門的員工信息 select * from emp where sal1000 and deptno=20求薪水不是800或者不是1500或者不是3000的員工信息 select * from emp where sal not in(800,1500,3000)也可以這樣來寫select * from emp where sal 800