oracle常用

1：找出公司里收入最高的前三名员工：^\tJR8
©http://www.java125.cn　　(CgIsc
SQL> select rownum, last_name, salaryB.o
2 from (select last_name, salary"T+Rh
3 from s_empY1VYj
4 order by salary desc)Zb#
5 where rownum<=3;/h
©http://www.java125.cn　　A3&/
ROWNUM LAST_NAME SALARYEA(
---------- ------------------------- ----------M/i
1 Velasquez 4750nx!j2*
2 Ropeburn 2945-US-
3 Nguyen 2897.5xOK
©http://www.java125.cn　　|U
©http://www.java125.cn　　VMY$\
注意：请大家分析一下一下语句为什么不对：9Ga,
©http://www.java125.cn　　)
SQL> select rownum, last_name, salary^u|>;i
2 from s_emp2%
3 where rownum<=3t}$
4 order by salary desc;s
©http://www.java125.cn　　]
ROWNUM LAST_NAME SALARYp
---------- ------------------------- ----------|Nr!
1 Velasquez 4750"
3 Nagayama 2660N#1m~
2 Ngao 2000;
©http://www.java125.cn　　%YH+K
©http://www.java125.cn　　to&Uz?
2： 找出表中的某一行或某几行的数据：mhaT
©http://www.java125.cn　　0S+Y
（1）：找出表中第三行数据：UB
用以下方法是不行的，因为rownum后面至可以用<或<=号,不可以用=,>号和其它的比较符号。!yM]&
©http://www.java125.cn　　<JARz
SQL> select * from s_empJ:vs
2 where rownum=3;p
©http://www.java125.cn　　B+
no rows selectedy,h}H}
©http://www.java125.cn　　"<F#
SQL> select * from s_empAXCc
2 where rownum between 3 and 5;xMxX5
©http://www.java125.cn　　)5/3Pk
no rows selecteds^`
©http://www.java125.cn　　!
正确的方法如下：<
©http://www.java125.cn　　pda
SQL> l=e
1 select last_name, salary1N%S
2 from (select rownum a, b.*Gl5k;^
3 from s_emp b)E
4* where a=3HAZvS
SQL> /P
©http://www.java125.cn　　$$&
LAST_NAME SALARYm@
------------------------- ----------SY+~sc
Nagayama 2660.E
©http://www.java125.cn　　U;^s
（2）：找出第三行到第五行之间的数据：:
SQL> ldbQ7f
1 select last_name, salaryct`opn
2 from (select rownum a, b.*J=(=`x
3 from s_emp b)'
4* where a between 3 and 5 ?"9
SQL> /:
©http://www.java125.cn　　vB
LAST_NAME SALARYWV4%|
------------------------- ----------J=
Nagayama 2660h
Quick-To-See 2755!F]]
Ropeburn 2945%|.".8
©http://www.java125.cn　　iLq]X
3：找出那些工资高于他们所在部门的平均工资的员工。(x>2
©http://www.java125.cn　　~aJf
（1）：第一种方法："Fo
SQL> select last_name, dept_id, salary:ry/t
2 from s_emp a[
3 where salary>(select avg(salary)K
4 from s_empf>y
5 where dept_id=a.dept_id);m!0]
©http://www.java125.cn　　&A
LAST_NAME DEPT_ID SALARYIK[{6
------------------------- ---------- ----------&az[<
Velasquez 50 4750hn.Q
Urguhart 41 2280M
Menchu 42 2375x9Xd
Biri 43 2090N
Catchpole 44 2470h<
Havel 45 2483.3]R_H
Nguyen 34 2897.5Rj*
Maduro 41 2660.+x,rO
Nozaki 42 2280Fhit?F
Schwartz 45 2090D+
©http://www.java125.cn　　H-~6
10 rows selected.\
©http://www.java125.cn　　0&
（2）：第二种方法：/4i.q[
SQL> lM@{mcG
1 select a.last_name, a.salary, a.dept_id, b.avgsal)7w
2 from s_emp a, (select dept_id, avg(salary) avgsalG&7r
3 from s_empyi_
4 group by dept_id) b9I
5 where a.dept_id=b.dept_idAD
6* and a.salary>b.avgsal[9P2P+
SQL> /]c
©http://www.java125.cn　　d18
LAST_NAME SALARY DEPT_ID AVGSALGnJ,a*
------------------------- ---------- ---------- ----------y}
Velasquez 4750 50 3847.58Ir<F
Urguhart 2280 41 2181.5k\t/iO
Menchu 2375 42 2055.16667-f`
Biri 2090 43 1710i"m
Catchpole 2470 44 1995FRv
Havel 2483.3 45 2069.1P![=R
Nguyen 2897.5 34 2204"A
Maduro 2660 41 2181.51pu}
Nozaki 2280 42 2055.16667*:
Schwartz 2090 45 2069.1v)o2FE
©http://www.java125.cn　　f'0
10 rows selected..\:
©http://www.java125.cn　　87O0
4：找出那些工资高于他们所在部门的manager的工资的员工。X
©http://www.java125.cn　　MaC
SQL> lb-x`5F
1 select id, last_name, salary, manager_id%W
2 from s_emp aX Pn
3 where salary>(select salarywXQ`C
4 from s_empXz4^
5* where id=a.manager_id)qU
SQL> /g!
©http://www.java125.cn　　EkSt>1
ID LAST_NAME SALARY MANAGER_ID9B[T!
---------- ------------------------- ---------- ---------- <
6 Urguhart 2280 2n O
7 Menchu 2375 2(8.!62
8 Biri 2090 2g`Z'8
9 Catchpole 2470 2Fz
10 Havel 2483.3 2>7MC
12 Giljum 2831 3cH2
13 Sedeghi 2878.5 3kF,d&
14 Nguyen 2897.5 3fKS5{"
15 Dumas 2755 3mCUT
16 Maduro 2660 6(/Oru)
©http://www.java125.cn　　%
10 rows selected.o5<Yj
©http://www.java125.cn　　.m
©http://www.java125.cn　　9H8
找出部门工资排名第二，三的员工8
©http://www.java125.cn　　rm8\*+
1 select name,salary,deptno from (D9d
2 select concat(last_name,first_name) name,salary,department_id deptno,w2fy
3 rank() over (partition by department_id order by salary desc) rnkh'W+
4* from employees) where rnk=2 or rnk=3l!"@
SQL> /.
©http://www.java125.cn　　zdf`?
NAME©http://www.java125.cn　　=
--------------------------------------------------------------------------------z]OY
SALARY DEPTNO,A[
---------- ----------I:
FayPat9M
6000 20il
©http://www.java125.cn　　-
KhooAlexander;}=D\$
3100 30sgi_'0
©http://www.java125.cn　　f20;
BaidaShelliFk
2900 309+'y
©http://www.java125.cn　　#*:<.
©http://www.java125.cn　　JBZH$(
NAME©http://www.java125.cn　　;j?8
--------------------------------------------------------------------------------TT
SALARY DEPTNOWd
---------- ----------Z
WeissMatthew^Y
8000 50i#S
©http://www.java125.cn　　cy_F
KauflingPayam?{jb
7900 50;FuL@
©http://www.java125.cn　　1>
ErnstBrucexe[AM1
6000 60MS_
©http://www.java125.cn　　4
©http://www.java125.cn　　'
NAME©http://www.java125.cn　　{mv/l&
--------------------------------------------------------------------------------^
SALARY DEPTNO~<th7w
---------- -----------;Hqq
AustinDavid;
4800 60[htf
©http://www.java125.cn　　w{D
PataballaVallish)
4800 60WZx(
©http://www.java125.cn　　!hb$
PartnersKaren*GM5v=
13500 80`:>LA
©http://www.java125.cn　　TVD>o
©http://www.java125.cn　　U (?
NAME©http://www.java125.cn　　76%
--------------------------------------------------------------------------------K9
SALARY DEPTNOjJ5,
---------- ----------{L$e
ErrazurizAlbertoBPd@
12000 80@_)
©http://www.java125.cn　　+U@%
KochharNeena%=
17000 907=!
©http://www.java125.cn　　WS4\
De HaanLexJm
17000 90*
©http://www.java125.cn　　m
©http://www.java125.cn　　-
NAME©http://www.java125.cn　　8kJ
--------------------------------------------------------------------------------17C
SALARY DEPTNO9='=!
---------- ----------an
FavietDaniel"
9000 100E{7Av
©http://www.java125.cn　　,\`J=$
ChenJohn%4!J4J
8200 100+Xb
©http://www.java125.cn　　6G{7
GietzWilliam(Z]r
8300 11033]
©http://www.java125.cn　　>=c(
©http://www.java125.cn　　:ru
15 rows selected.&|{
©http://www.java125.cn　　o
SQL>h
©http://www.java125.cn　　O
找出部门工资排名第二，三的员工a%
©http://www.java125.cn　　hz
1 select name,salary,deptno from (igD/)
2 select concat(last_name,first_name) name,salary,department_id deptno,9
3 rank() over (partition by department_id order by salary desc) rnkc`QN
4* from employees) where rnk=2 or rnk=3A[A
SQL> /HCAU
©http://www.java125.cn　　v
NAME©http://www.java125.cn　　7l2
--------------------------------------------------------------------------------:lAOP
SALARY DEPTNOy
---------- ----------aY
FayPatn@B_l
6000 20t
©http://www.java125.cn　　];OTJ
KhooAlexander9jP<~J
3100 30PYXF.@
©http://www.java125.cn　　F#
BaidaShellin
2900 30X
©http://www.java125.cn　　\Gro<
©http://www.java125.cn　　&4
NAME©http://www.java125.cn　　jmWoJ
--------------------------------------------------------------------------------z
SALARY DEPTNOU
---------- ----------x^f
WeissMatthew$X`[YM
8000 50i
©http://www.java125.cn　　]m?
KauflingPayam0oP
7900 50?D<g,2
©http://www.java125.cn　　/ \t
ErnstBruce.s
6000 60/Qx?U$
©http://www.java125.cn　　)X
©http://www.java125.cn　　'
NAME©http://www.java125.cn　　CkO3
--------------------------------------------------------------------------------wl}:Hh
SALARY DEPTNOC+G
---------- ----------'B
AustinDavid(u
4800 60NIHX
©http://www.java125.cn　　`K
PataballaVallih0Y:
4800 60Y
©http://www.java125.cn　　4-r
PartnersKaren)58
13500 80Ve5@
©http://www.java125.cn　　7K
©http://www.java125.cn　　h2[Bp
NAME©http://www.java125.cn　　S*
-------------------------------------------------------------------------------- ~TBW
SALARY DEPTNO3
---------- ----------~
ErrazurizAlberto?5_
12000 806_
©http://www.java125.cn　　v}@FmE
KochharNeenaI!?2WZ
17000 90W
©http://www.java125.cn　　CNN
De HaanLexyvWR3h
17000 905<l
©http://www.java125.cn　　$p_y2u
©http://www.java125.cn　　}+('_.
NAME©http://www.java125.cn　　w~2#M
--------------------------------------------------------------------------------7A-sI
SALARY DEPTNOD:"-
---------- ----------eE
FavietDanielpy/@lK
9000 100i*{1]
©http://www.java125.cn　　3",@n
ChenJohnFt?
8200 100?XY:M]
©http://www.java125.cn　　{
GietzWilliamgvsb
8300 110a1D
©http://www.java125.cn　　Hu~&T
©http://www.java125.cn　　2
15 rows selected.-h*DI
©http://www.java125.cn　　6W-2v
SQL>f;QD
©http://www.java125.cn　　x
又是一道面试题:q9#
©http://www.java125.cn　　"&P@
原表:©http://www.java125.cn　　RB
©http://www.java125.cn　　 g
id proid pronameC.
1 1 M©http://www.java125.cn　　$l?6 {
1 2 F©http://www.java125.cn　　rCqS
2 1 N©http://www.java125.cn　　{q
2 2 G©http://www.java125.cn　　v
3 1 B©http://www.java125.cn　　^p
3 2 A©http://www.java125.cn　　;-*l
查询后的表:IC?w~"
©http://www.java125.cn　　mqW
id pro1 pro2!#m_k/
1 M F©http://www.java125.cn　　}[;lR
2 N G©http://www.java125.cn　　}p0R
3 B A©http://www.java125.cn　　x/_ek
写出查询语句 ,c
©http://www.java125.cn　　"
又是一道面试题:\s
©http://www.java125.cn　　PKQP]
原表:©http://www.java125.cn　　QPZ
©http://www.java125.cn　　S/zVKy
id proid pronameM*V|
1 1 M©http://www.java125.cn　　"R
1 2 F©http://www.java125.cn　　,
2 1 N©http://www.java125.cn　　tFkS~
2 2 G©http://www.java125.cn　　J:zU n
3 1 B©http://www.java125.cn　　^cE!
3 2 A©http://www.java125.cn　　g
查询后的表:LdL@.d
©http://www.java125.cn　　Vnol
id pro1 pro29OC]
1 M F©http://www.java125.cn　　D.waa
2 N G©http://www.java125.cn　　\sOh\
3 B A©http://www.java125.cn　　&:(
写出查询语句 H
©http://www.java125.cn　　Z2?Uh
又是一道面试题:p.P
©http://www.java125.cn　　Ro(V
原表:©http://www.java125.cn　　XXUc
©http://www.java125.cn　　^Cyx
id proid proname~_?q['
1 1 M©http://www.java125.cn　　4)hg
1 2 F©http://www.java125.cn　　KBRrK
2 1 N©http://www.java125.cn　　$Qeeeb
2 2 G©http://www.java125.cn　　rIBy
3 1 B©http://www.java125.cn　　!v=(J
3 2 A©http://www.java125.cn　　j+NbN
查询后的表:s0Gb
©http://www.java125.cn　　)Z
id pro1 pro29E.JF
1 M F©http://www.java125.cn　　zt+!#u
2 N G©http://www.java125.cn　　y6)[(
3 B A©http://www.java125.cn　　%Ps'V
写出查询语句 w
©http://www.java125.cn　　69|}
又是一道面试题: 1wH
©http://www.java125.cn　　D
原表: z&
©http://www.java125.cn　　^
id proid proname .\Cg|
1 1 M ~W
1 2 F ;uk
2 1 N /@&B
2 2 G U[.r)k
3 1 B > &
3 2 A (*
查询后的表: K
©http://www.java125.cn　　u
id pro1 pro2 %@a^
1 M F -_E
2 N G _?Pt4g
3 B A md-
写出查询语句 k[z`F
©http://www.java125.cn　　'f+P;0
解决方案可有以下三种作参考：mA
©http://www.java125.cn　　T:x
©http://www.java125.cn　　tr`,9S
1：使用pl/sql代码实现，但要求你组合后的长度不能超出oracle varchar2长度的限制。 <wB"n
下面是一个例子 |D
create or replace type strings_table is table of varchar2(20);\]+
/©http://www.java125.cn　　I
create or replace function merge (pv in strings_table) return varchar2%dR
is©http://www.java125.cn　　K@|;z~
ls varchar2(4000);f#dhK-
begin©http://www.java125.cn　　NA_2
for i in 1..pv.count loop-<
ls := ls || pv(i);l;s6
end loop;>yZ
return ls;hR!'F
end;Q"
/©http://www.java125.cn　　}j'-[
create table t (id number,name varchar2(10));4n
insert into t values(1,'Joan');PeQnD@
insert into t values(1,'Jack');hMEtal
insert into t values(1,'Tom');Ni)
insert into t values(2,'Rose');.
insert into t values(2,'Jenny');\'N
©http://www.java125.cn　　uk&$4
column names format a80;a<
select t0.id,merge(cast(multiset(select name from t where t.id = t0.id) as strings_table)) names 1i4
from (select distinct id from t) t0;)&
©http://www.java125.cn　　k5<5h
drop type strings_table;q#?
drop function merge;>T
drop table t;#
©http://www.java125.cn　　5\F}G
©http://www.java125.cn　　"\ n
2：用sql： nr~>
©http://www.java125.cn　　i:v
Well if you have a thoretical maximum, which I would assume you would given the legibility of listing hundreds of employees in the way you describe then yes. But the SQL needs to use the LAG function for each employee, hence a hundred emps a hundred LAGs, so kind of bulky. \OfJmF
©http://www.java125.cn　　!F&
This example uses a max of 6, and would need more cut n pasting to do more than that. fzY
©http://www.java125.cn　　wr
©http://www.java125.cn　　U-
SQL> select deptno, dname, emps ;"xD@]
2 from ( '
3 select d.deptno, d.dname, rtrim(e.ename ||', '|| I/U
4 lead(e.ename,1) over (partition by d.deptno Z;
5 order by e.ename) ||', '|| a
6 lead(e.ename,2) over (partition by d.deptno 2Luw
7 order by e.ename) ||', '|| K
8 lead(e.ename,3) over (partition by d.deptno 1fJ-K?
9 order by e.ename) ||', '|| AWL<
10 lead(e.ename,4) over (partition by d.deptno Y
11 order by e.ename) ||', '|| @7k1
12 lead(e.ename,5) over (partition by d.deptno j
13 order by e.ename),', ') emps, 97xb
14 row_number () over (partition by d.deptno Uw
15 order by e.ename) x Zr8DO
16 from emp e, dept d ZY`E
17 where d.deptno = e.deptno !K;@
18 ) ©http://www.java125.cn　　9
19 where x = 1 59
20 / ©http://www.java125.cn　　x'4
©http://www.java125.cn　　u9~7
DEPTNO DNAME EMPS d
------- ----------- ------------------------------------------ SZCh<p
10 ACCOUNTING CLARK, KING, MILLER K9u
20 RESEARCH ADAMS, FORD, JONES, ROONEY, SCOTT, SMITH 'xy'u&
30 SALES ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD8
©http://www.java125.cn　　"
©http://www.java125.cn　　}:':
3：先用pl/sql创建一个函数（create function get_a2）; 'sLZ
create or replace function get_a2( tmp_a1 number) 5{
return varchar2 >7
is ©http://www.java125.cn　　OB:M
Col_a2 varchar2(4000); n
begin C8&
Col_a2:=''; 2
for cur in (select a2 from unite_a where a1=tmp_a1) TDeg7
loop ©http://www.java125.cn　　\v:F
Col_a2=Col_a2||cur.a2; =Da7
end loop; *9w3
return Col_a2; Ya]
end get_a2; #:&5
©http://www.java125.cn　　rT]
select distinct a1 ,get_a2(a1) from unite_a <[TuN
1 ABC Q9
2 EFG