最近有 一些面试中的问题,总结起来看,一是关于怎样找出和去除重复数据,这在另一个帖子利已有详细介绍。二是关于找出某一列里最大或最小的前几个,或是大于或小于某一个值(最大值或平均值)的数据。针对这种情况,再此做一个介绍。
1:找出公司里收入最高的前三名员工:
SQL> select rownum, last_name, salary
2 from (select last_name, salary
3 from s_emp
4 order by salary desc)
5 where rownum<=3;
ROWNUM LAST_NAME SALARY ---------- ------------------------- ---------- 1 Velasquez 47508 G 2 Ropeburn 2945l6C.G 3 Nguyen 2897.5 QUVoW ©达内科技论坛 -- 达内科技论坛 =9F}j\ ©达内科技论坛 -- 达内科技论坛 J‘ 注意:请大家分析一下一下语句为什么不对:dOb{RO ©达内科技论坛 -- 达内科技论坛 bt7]c0 SQL> select rownum, last_name, salary 2 from s_empg:m4> 3 where rownum<=3.ny 4 order by salary desc;tUJ ©达内科技论坛 -- 达内科技论坛 pqGE\[ ROWNUM LAST_NAME SALARY!7. ---------- ------------------------- ----------Y 1 Velasquez 4750YHJ#V 3 Nagayama 2660%p8~ 2 Ngao 2000Bb; ©达内科技论坛 -- 达内科技论坛 @ ©达内科技论坛 -- 达内科技论坛 g#|E 2: 找出表中的某一行或某几行的数据:k9 ©达内科技论坛 -- 达内科技论坛 mM (1):找出表中第三行数据:P 用以下方法是不行的,因为rownum后面至可以用<或<=号,不可以用=,>号和其它的比较符号。+h">^ ©达内科技论坛 -- 达内科技论坛 Vr$$]h SQL> select * from s_emp{ 2 where rownum=3;O_{ ©达内科技论坛 -- 达内科技论坛 /;B no rows selectedp( ©达内科技论坛 -- 达内科技论坛 1"B SQL> select * from s_emp">7 2 where rownum between 3 and 5;Dky!m ©达内科技论坛 -- 达内科技论坛 qS no rows selectedm^)0 ©达内科技论坛 -- 达内科技论坛 H_& 正确的方法如下:,~w[KF ©达内科技论坛 -- 达内科技论坛 z7@>Me SQL> l/( 1 select last_name, salary(?\ 2 from (select rownum a, b.*ZVwI! 3 from s_emp b)N 4* where a=3DI SQL> /Cu ©达内科技论坛 -- 达内科技论坛 `t LAST_NAME SALARYd$#> ------------------------- ----------,/N?UK Nagayama 2660w ©达内科技论坛 -- 达内科技论坛 .$CX (2):找出第三行到第五行之间的数据:L SQL> l]M]~H 1 select last_name, salaryH 2 from (select rownum a, b.*CD5w+ 3 from s_emp b)om2 4* where a between 3 and 5,fQ&,= SQL> /R4SS ©达内科技论坛 -- 达内科技论坛 O?D7) LAST_NAME SALARY5‘ ------------------------- ----------6yd Nagayama 2660@L Quick-To-See 2755> Ropeburn 2945Ni‘=r ©达内科技论坛 -- 达内科技论坛 <Lh_VZ 3:找出那些工资高于他们所在部门的平均工资的员工。I‘R*u ©达内科技论坛 -- 达内科技论坛 ;7Z (1):第一种方法:5acm6S SQL> select last_name, dept_id, salary( 2 from s_emp ag1}+7 3 where salary>(select avg(salary)S-( 4 from s_emp2ksQsY 5 where dept_id=a.dept_id);PZRM ©达内科技论坛 -- 达内科技论坛 WFa=Fa LAST_NAME DEPT_ID SALARY_f7 ------------------------- ---------- ----------3x-v0 Velasquez 50 4750r6h*8 Urguhart 41 2280yECF* Menchu 42 2375TLoS/‘ Biri 43 2090^xJ Catchpole 44 2470ZS, Havel 45 2483.3;R`$_ Nguyen 34 2897.5pK\=Q$ Maduro 41 2660?OZ_(: Nozaki 42 2280wR Schwartz 45 2090` ©达内科技论坛 -- 达内科技论坛 AtMr 10 rows selected.loa ©达内科技论坛 -- 达内科技论坛 q8a{ (2):第二种方法:5AH1 SQL> l=\]n 1 select a.last_name, a.salary, a.dept_id, b.avgsal!NZW2d 2 from s_emp a, (select dept_id, avg(salary) avgsal7P<2H; 3 from s_emp)(o? 4 group by dept_id) b(a5 5 where a.dept_id=b.dept_id-6 6* and a.salary>b.avgsalZ SQL> /‘^NJ ©达内科技论坛 -- 达内科技论坛 7s/ LAST_NAME SALARY DEPT_ID AVGSALCX@ ------------------------- ---------- ---------- ----------j Velasquez 4750 50 3847.5}@7 Urguhart 2280 41 2181.5_m Menchu 2375 42 2055.16667ks5 Biri 2090 43 1710I/Q Catchpole 2470 44 1995k Havel 2483.3 45 2069.1,H Nguyen 2897.5 34 2204nB Maduro 2660 41 2181.5<& Nozaki 2280 42 2055.1666788W Schwartz 2090 45 2069.1"L$Aht ©达内科技论坛 -- 达内科技论坛 @3`tD~ 10 rows selected.p3Q#b ©达内科技论坛 -- 达内科技论坛 pV[@QT 4:找出那些工资高于他们所在部门的manager的工资的员工。Y^;‘ ©达内科技论坛 -- 达内科技论坛 5z SQL> lg!O`?~ 1 select id, last_name, salary, manager_ida 2 from s_emp ae8wG 3 where salary>(select salaryb)-r 4 from s_empflzfJG 5* where id=a.manager_id)|/t)yO SQL> /{Nwt ©达内科技论坛 -- 达内科技论坛 *^0LJ ID LAST_NAME SALARY MANAGER_IDCQV ---------- ------------------------- ---------- ----------=Il 6 Urguhart 2280 2If@ 7 Menchu 2375 2j:QMG 8 Biri 2090 2wjkW.! 9 Catchpole 2470 23] 10 Havel 2483.3 2f&&MPS 12 Giljum 2831 38NT<! 13 Sedeghi 2878.5 3_ 14 Nguyen 2897.5 3b@ 15 Dumas 2755 3${ 16 Maduro 2660 6Kg ©达内科技论坛 -- 达内科技论坛 Zu 10 rows selected.Uk2fN
第一题:有两个表分别如下:=Z< 表A(varchar(32) NAME,int GRADE)*{ 数据::WyN$ ZHANGSHAN 80 R LISI 60N WANGWU 84# ©达内科技论坛 -- 达内科技论坛 m]P 表B(varchar(32) NAME,int AGE)[! 数据:Hi)Y:7 ZHANGSHAN 26 =( LISI 24@ WANGWU 261> WUTIAN 26~.yj4 ©达内科技论坛 -- 达内科技论坛 X@~~O (1)写SQL语句得到如下查询结果:>g1\oj NAME GRADE AGE C ZHANGSHAN 80 26 q5IV LISI 60 24sw WANGWU 84 262c. l WUTIAN NULL 26Z] 疑问:这里的没有成绩的那个人的记录怎么得到呢?C#~,Q ©达内科技论坛 -- 达内科技论坛 ` (2)写SQl语句根据名字(NAME)相同按年龄(AGE)分组得到不同年龄的人的平均成绩,并写出结果。f${4\n 疑问:按照名字相同,WUTIAN这个人没有成绩该不该把他统计在内呢?8B< ©达内科技论坛 -- 达内科技论坛 t%# (3)还有一问具体数据记不清了,比上两问更怪。y#1\^m
|
|
|
|
[这个贴子最后由SunOne在 2003/12/30 04:11pm 编辑]m ©达内科技论坛 -- 达内科技论坛 ;( 第二题:有一个数据库表dept中有如下数据:3!|- id_no id_namewe# 1000 S1L&@U 1001 S2.$]K 1002 S31? 1003 S4<q(SlO 1000 S5p8 1000 S6gZ~&>= 1001 S7/ 表中有id_no重复,如id_no为1000的有3条记录,如id_no为1001的有2条记录,z-XS 现在要按照id_no给表建索引,需要删除id_no重复了的那些记录,但不能删掉所有拥有该id_no的记录,必需保留一条拥有该id_no的记录(如id_no为1000的只剩下一条记录)7 ©达内科技论坛 -- 达内科技论坛 ( (1)请写出SQl语句(或SQL语句组),查询所有id_no重复的记录。bxt ©达内科技论坛 -- 达内科技论坛 qwZ239 (2)请写出SQl语句实现题目要求的结果。
|
1:©达内科技论坛 -- 达内科技论坛 SWT SQL> create table ahCu 2 (name varchar2(32),t 3 grade int);fHYm Table created.j| SQL> insert into a_ty5.u 2 values(‘&a‘,&b);Vk-~\y Enter value for a: zhangshantS Enter value for b: 80hYTG& old 2: values(‘&a‘,&b)7]bgW new 2: values(‘zhangshan‘,80)#sQukk ©达内科技论坛 -- 达内科技论坛 )dD>M: 1 row created.5 ©达内科技论坛 -- 达内科技论坛 >ic5 SQL> /Nb># Enter value for a: lisi|FS|3 Enter value for b: 60[7s old 2: values(‘&a‘,&b)6 new 2: values(‘lisi‘,60)^s-a ©达内科技论坛 -- 达内科技论坛 hR-Bni 1 row created.3 ©达内科技论坛 -- 达内科技论坛 Cc] SQL> /iX@ Enter value for a: wangwu9J Enter value for b: 84rR` old 2: values(‘&a‘,&b)I z4Bn new 2: values(‘wangwu‘,84)u ©达内科技论坛 -- 达内科技论坛 Cwdw,M 1 row created.‘&F4H& ©达内科技论坛 -- 达内科技论坛 zU SQL> commit;pS#~f ©达内科技论坛 -- 达内科技论坛 R" Commit complete.I@b} ©达内科技论坛 -- 达内科技论坛 X#{! 9 SQL> create table b_t%;R 2 (name varchar2(32),71 3 age int);x;SQ^ ©达内科技论坛 -- 达内科技论坛 Q+z Table created.!M ©达内科技论坛 -- 达内科技论坛 L| SQL> insert into b_tJ 2 values(‘&a‘,&b);UK|‘?] Enter value for a: zhangshanZA+X: Enter value for b: 26:x`.M( old 2: values(‘&a‘,&b)wbi[ new 2: values(‘zhangshan‘,26)M=A ©达内科技论坛 -- 达内科技论坛 # 1 row created.6f4 ©达内科技论坛 -- 达内科技论坛 *h5 SQL> /#u Enter value for a: lisi18J/- Enter value for b: 24fA* old 2: values(‘&a‘,&b)8MqSY" new 2: values(‘lisi‘,24)Myt6W ©达内科技论坛 -- 达内科技论坛 d" 1 row created.;2NtAv ©达内科技论坛 -- 达内科技论坛 UPy:Z; SQL> /"[u Enter value for a: wangwu{|Q3 Enter value for b: 267Max old 2: values(‘&a‘,&b)5+ new 2: values(‘wangwu‘,26)9tn+{ ©达内科技论坛 -- 达内科技论坛 1oT; 1 row created.Yg ©达内科技论坛 -- 达内科技论坛 j3{M SQL> /?G#i Enter value for a: wutianz&a Enter value for b: 26g old 2: values(‘&a‘,&b)3 new 2: values(‘wutian‘,26)lqP ©达内科技论坛 -- 达内科技论坛 H 1 row created.XqM ©达内科技论坛 -- 达内科技论坛 |t SQL> commit;*x!#v ©达内科技论坛 -- 达内科技论坛 gfh. Commit complete.m>:QD ©达内科技论坛 -- 达内科技论坛 Gv*h SQL>}rDu# SQL> col grade null ‘null‘3"K SQL> l71srGP 1 select b.name, a.grade, b.age5)F< 2 from a_t a,b_t b-!$YRs 3* where a.name(+)=b.name\H SQL> /q9[ ©达内科技论坛 -- 达内科技论坛 Ia- NAME GRADE AGE-n&M -------------------------------- ---------- ----------BL lisi 60 24W wangwu 84 26477E wutian null 26[a?EA zhangshan 80 26h~ ©达内科技论坛 -- 达内科技论坛 ?!; SQL>-#D ©达内科技论坛 -- 达内科技论坛 ?kMGSC 2:©达内科技论坛 -- 达内科技论坛 45r&‘a SQL> lL 1 select name, avg(grade), age(sJ7rh 2 from (select b.name name, a.grade grade, b.age ageNUx3>; 3 from a_t a,b_t bSJx 4 where a.name(+)=b.name),5 5* group by age, nameZU SQL> /E ©达内科技论坛 -- 达内科技论坛 Ewhj NAME AVG(GRADE) AGE&H -------------------------------- ---------- ----------g]<pc& lisi 60 243 wangwu 84 26!ZOnC wutian 26Nn zhangshan 80 26f ©达内科技论坛 -- 达内科技论坛 uThcM\ SWUTIAN这个人没有成绩不把他统计在内q) 3:©达内科技论坛 -- 达内科技论坛 i-: 1、当我们想要为一个表创建唯一索引时,如果该表有重复的记录,则无法创建成功。 (QJS 方法原理: )KQ 1、Oracle中,每一条记录都有一个rowid,rowid在整个数据库中是唯一的, Gk rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。 yhjK ©达内科技论坛 -- 达内科技论坛 `, 2、在重复的记录中,可能所有列的内容都相同,但rowid不会相同,所以只要确定出重复记录中 V>)P 那些具有最大rowid的就可以了,其余全部删除。 lH ©达内科技论坛 -- 达内科技论坛 k7 3、以下语句用到了3项技巧:rowid、子查询、别名。 XNR* ©达内科技论坛 -- 达内科技论坛 ba 实现方法: /e SQL> create table a ( 33 2 bm char(4), --编码 <}cU@4 3 mc varchar2(20) --名称 =jY;Z 4 ) ©达内科技论坛 -- 达内科技论坛 g!KM 5 / ©达内科技论坛 -- 达内科技论坛 {:e[ ©达内科技论坛 -- 达内科技论坛 e`e 表已建立. 1?,. ©达内科技论坛 -- 达内科技论坛 8=Dl9; SQL> insert into a values(‘1111‘,‘1111‘); R23g SQL> insert into a values(‘1112‘,‘1111‘); # SQL> insert into a values(‘1113‘,‘1111‘); i| SQL> insert into a values(‘1114‘,‘1111‘); q>V+L ©达内科技论坛 -- 达内科技论坛 }J SQL> insert into a select * from a; +i2|@0 ©达内科技论坛 -- 达内科技论坛 *T 插入4个记录. lIG\^X ©达内科技论坛 -- 达内科技论坛 T SQL> commit; EnemB SQL> select rowid,bm,mc from a; O7 ©达内科技论坛 -- 达内科技论坛 e ROWID BM MC :!|j8m ------------------ ---- ------- ‘9%EK 000000D5.0000.0002 1111 1111 h9$-)L 000000D5.0001.0002 1112 1111 dd 000000D5.0002.0002 1113 1111 OXC 000000D5.0003.0002 1114 1111 K7K 000000D5.0004.0002 1111 1111 #O 000000D5.0005.0002 1112 1111 Hk 000000D5.0006.0002 1113 1111 < 000000D5.0007.0002 1114 1111 iAs Y ©达内科技论坛 -- 达内科技论坛 u 查询到8记录. {9& ©达内科技论坛 -- 达内科技论坛 || ©达内科技论坛 -- 达内科技论坛 x 查出重复记录 j-H&D SQL> select rowid,bm,mc from a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc); $8& X^ ©达内科技论坛 -- 达内科技论坛 V‘ ROWID BM MC w@ ------------------ ---- -------------------- Z> 000000D5.0000.0002 1111 1111 DAu8a 000000D5.0001.0002 1112 1111 sRxcx 000000D5.0002.0002 1113 1111 ;+<J 000000D5.0003.0002 1114 1111 WbQN ©达内科技论坛 -- 达内科技论坛 ?| -5 删除重复记录 $@ SQL> delete from a a where a.rowid!=(select max(rowid) from a b where a.bm=b.bm and a.mc=b.mc); I‘=!c ©达内科技论坛 -- 达内科技论坛 a(S}C 删除4个记录. 8 ©达内科技论坛 -- 达内科技论坛 #2B SQL> select rowid,bm,mc from a; >i/ ©达内科技论坛 -- 达内科技论坛 Wj~cn ROWID BM MC dij ------------------ ---- -------------------- uWg 000000D5.0004.0002 1111 1111 HO/0hp 000000D5.0005.0002 1112 1111 mfc 000000D5.0006.0002 1113 1111 T$S5aA 000000D5.0007.0002 1114 1111q
|
=====================================================
ProductID SALE_YEAR SALES productID 2001 2002 2003L5WI3 001 2001 10 001 10" 002 2001 15 002 15< 003 2002 12 003 12 10AT 003 2003 10`h%@{7 写一个SQL或者PL/SQL完成左边的表变成右边的表。=
SQL> CREATE TABLE TEST(PRODUCT_ID NUMBER ,SALE_YEAR VARCHAR2(20),SALES NUMBER) ;Rxcs ©达内科技论坛 -- 达内科技论坛 R Table created.HQ/ ©达内科技论坛 -- 达内科技论坛 RQ SQL> INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘);v Enter value for 1: 001l7g[ Enter value for 2: 2001(!Zj Enter value for 3: 10AXREC old 1: INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘)S\_ new 1: INSERT INTO TEST valueS(‘001‘,‘2001‘,‘10‘)A{]KYG ©达内科技论坛 -- 达内科技论坛 6,De% 1 row created.73. ©达内科技论坛 -- 达内科技论坛 ]LHQ SQL> INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘);d|z& Enter value for 1: 002w3 Enter value for 2: 2001Y Enter value for 3: 15i old 1: INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘)Fj new 1: INSERT INTO TEST valueS(‘002‘,‘2001‘,‘15‘)^ZawQJ ©达内科技论坛 -- 达内科技论坛 : 1 row created.: ©达内科技论坛 -- 达内科技论坛 k0=O7 SQL> INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘);N Enter value for 1: 003e Enter value for 2: 2002g-P Enter value for 3: 12G^RV old 1: INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘)jp/I0 new 1: INSERT INTO TEST valueS(‘003‘,‘2002‘,‘12‘)Y ©达内科技论坛 -- 达内科技论坛 ) 1 row created.[1UqH ©达内科技论坛 -- 达内科技论坛 lKrk SQL> INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘);<(n Enter value for 1: 003OI Enter value for 2: 2003y< Enter value for 3: 10l )@d old 1: INSERT INTO TEST valueS(‘&1‘,‘&2‘,‘&3‘)S`v>1 new 1: INSERT INTO TEST valueS(‘003‘,‘2003‘,‘10‘)Bjax ©达内科技论坛 -- 达内科技论坛 .dvn 1 row created.kov/, ©达内科技论坛 -- 达内科技论坛 /j SQL> COMMIT;:[Po1 ©达内科技论坛 -- 达内科技论坛 Yor{B Commit complete.`XH`h0 ©达内科技论坛 -- 达内科技论坛 -2#en$ SQL> SELECT * FROM TEST;-C ©达内科技论坛 -- 达内科技论坛 [ PRODUCT_ID SALE_YEAR SALESyk ---------- -------------------- ---------- 1 2001 10U 2 2001 15;o 3 2002 12rN\ou 3 2003 10eI%/ ©达内科技论坛 -- 达内科技论坛 T0[WI" SQL> SELECT PRODUCT_ID,:B 2 DECODE(SALE_YEAR,2001,SUM(SALES)) "2001",B\ 3 DECODE(SALE_YEAR,2002,SUM(SALES)) "2002",cp79 4 DECODE(SALE_YEAR,2003,SUM(SALES)) "2003"C 5 FROM TEST,‘Yd[Z 6 GROUP BY PRODUCT_ID,SALE_YEA;<rjT GROUP BY PRODUCT_ID,SALE_YEAd *Ri ERROR at line 6:y ORA-00904: "SALE_YEA": invalid identifierr". ©达内科技论坛 -- 达内科技论坛 !0!% ©达内科技论坛 -- 达内科技论坛 ~1 SQL> SELECT PRODUCT_ID,Ss 2 SUM(DECODE(SALE_YEAR,2001,SALES)) "2001",JQ+XNx 3 SUM(DECODE(SALE_YEAR,2002,SALES)) "2002",{Sp‘c 4 SUM(DECODE(SALE_YEAR,2003,SALES)) "2003"C 5 FROM TEST[ 6 6 GROUP BY PRODUCT_ID;2:6R1x ©达内科技论坛 -- 达内科技论坛 ` PRODUCT_ID 2001 2002 20031 ---------- ---------- ---------- ----------Tq 1 10r)<D 2 158^o 3 12 10NY?
分页模板:
SELECT B.* FROM78 (SELECT A.*,ROWNUM ROW_NUM FROMj (SELECT * FROM [表名] WHERE [选择条件]% ORDER BY [排序方式] ) A where rownum <= [页号*每页记录数] )B©
WHERE ROW_NUM >= [(页号-1)*每页记录数+1]
|