Oracle重复记录锁等信息处理方法.doc

Oracle:行转列函数,查询重复记录函数,过滤表中重复记录函数1 行转列函数: 1. selectwm_concat(username)fromusersselect wm_concat(username) from users2 查询重复记录函数 Sql代码 1. selectt.*from(selectt.*,rownumrnfromUSERStwheret.usernamein(selectusernamefromUSERSgroupbyusernamehavingcount(*)1)tselect t.* from(select t.*,rownum rn from USERS t where t.username in (select username from USERS group by username having count(*) 1) t3 过滤表中重复记录函数 有一张User表,姓名字段重复 A Sql代码 1. selectt.*from(selectt.*,rownumrnfromUSERStwheret.usernamenotin(selectusernamefromUSERSgroupbyusernamehavingcount(*)1)t 2. unionall3. selectt.*from(selectt.*,rownumrnfromUSERStwheret.usernamein(selectusernamefromUSERSgroupbyusernamehavingcount(*)1)twheret.rn=1select t.* from(select t.*,rownum rn from USERS t where t.username not in (select username from USERS group by username having count(*) 1)tunion allselect t.* from(select t.*,rownum rn from USERS t where t.username in (select username from USERS group by username having count(*) 1) t where t.rn=1B 1. select*from(selectt.*,row_number()over(partitionbyusernameorderbyusername)rnfromUSERSt)ttwherett.rn=1;1. 多where，少having where用来过滤行，having用来过滤组 2. 多union all，少union union删除了重复的行，因此花费了一些时间 3. 多Exists，少in Exists只检查存在性，性能比in强很多，有些朋友不会用Exists，就举个例子 例，想要得到有电话号码的人的基本信息，table2有冗余信息 select * from table1;-(id,name,age) select * from table2;-(id,phone) in： select * from table1 t1 where t1.id in (select t2.id from table2 t2 where t1.id=t2.id); Exists： select * from table1 t1 where Exists (select 1 from table2 t2 where t1.id=t2.id); 4. 使用绑定变量 Oracle数据库软件会缓存已经执行的sql语句，复用该语句可以减少执行时间。复用是有条件的，sql语句必须相同 问：怎样算不同？ 答：随便什么不同都算不同，不管什么空格啊，大小写什么的，都是不同的想要复用语句，建议使用PreparedStatement将语句写成如下形式： insert into XXX(pk_id,column1) values(?,?); update XXX set column1=? where pk_id=?; delete from XXX where pk_id=?; select pk_id,column1 from XXX where pk_id=?; 5.少用* 很多朋友很喜欢用*，比如：select * from XXX; 一般来说，并不需要所有的数据，只需要一些，有的仅仅需要1个2个， 拿5W的数据量，10个属性来测试: (这里的时间指的是PL/SQL Developer显示所有数据的时间)使用select * from XXX;平均需要20秒，使用select column1,column2 from XXX;平均需要12秒 (我的机子不是很好。) 对于开发来说，这一条是个灾难，知道是一回事，做就是另一回事了 6.分页sql 一般的分页sql如下所示： sql1:select * from (select t.*,rownum rn from XXX t)where rn0 and rn 10; sql2:select * from (select t.*,rownum rn from XXX t where rownum 0; 乍看一下没什么区别，实际上区别很大.125万条数据测试，sql1平均需要1.25秒(咋这么准呢？ ) sql2平均需要. 0.07秒 原因在于，子查询中，sql2排除了10以外的所有数据 当然了，如果查询最后10条，那效率是一样的 第7条本来不准备解释的，也有这么多质疑的，说明追寻真理的朋友还是相当多的。 我认为数据库当掉确实很有可能，我没想到是我的问题。 以上各条只针对sql如何优化，至于开发效率低，可读性等等，并不在考虑范围内 （补充内容2）第6条，如果有分页需要排序，必须再包一层，结果为 select * from (select t.*, rownum rn from (select * from XXX order by value desc) t where rownum 0; （补充内容3）第3条，用in的朋友注意了，当参数超过1000个，数据库就挂了。（oracle 10g数据库） 5. 1、关于group by表内容：6. 2005-05-09 胜 2005-05-09 胜 2005-05-09 负 2005-05-09 负 2005-05-10 胜 2005-05-10 负 2005-05-10 负 如果要生成下列结果, 该如何写sql语句? 胜 负 2005-05-09 2 2 2005-05-10 1 2答案：7. 1) select rq,sum(case when shengfu=胜 then 1 else 0 end) as胜,sum(case when shengfu=负 then 1 else 0 end) as负from tab3 group by rq2) select N.rq,N. 胜,M. 负 from (select rq,count(*) 胜 from tab3 where shengfu=胜group by rq)N inner join(select rq,count(*) 负from tab3 where shengfu=负group by rq)M on N.rq=M.rq 3) select a.rq,a. 胜 as胜,b.负 as 负from (select rq,count(shengfu) 胜from tab3 where shengfu=胜 group by rq) a,(select rq,count(shengfu) 负from tab3 where shengfu=负 group by rq) b where a.rq=b.rq;1.关于group by表内容：2005-05-09 胜2005-05-09 胜2005-05-09 负2005-05-09 负2005-05-10 胜2005-05-10 负2005-05-10 负如果要生成下列结果, 该如何写sql语句? 胜 负2005-05-09 2 22005-05-10 1 2-1) select rq,sum(case when shengfu=胜 then 1 else 0 end) as胜,sum(case when shengfu=负 then 1 else 0 end) as负from tab3 group by rq2) select N.rq,N. 胜,M. 负 from (select rq,count(*) 胜 from tab3 where shengfu=胜group by rq)N inner join(select rq,count(*) 负from tab3 where shengfu=负group by rq)M on N.rq=M.rq 3) select a.rq,a. 胜 as胜,b.负 as 负from (select rq,count(shengfu) 胜from tab3 where shengfu=胜 group by rq) a,(select rq,count(shengfu) 负from tab3 where shengfu=负 group by rq) b where a.rq=b.rq;2.表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。-select (case when ab then a else b end),(case when bc then b else c end) from tab43.一个日期判断的sql语句请取出tab5表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)-select * from tab5 t where to_char(t.SendTime,yyyy-mm-dd)=to_char(sysdate,yyyy-mm-dd)4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来（并写出您的思路）： 大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。 显示格式： 语文 数学 英语 及格 优秀 不及格-select (case when语文=80 then 优秀 when语文60 then 及格 else 不及格 end) as 语文,(case when 数学=80 then 优秀 when数学60 then 及格 else 不及格 end) as数学,(case when英语=80 then 优秀 when英语60 then 及格 else 不及格 end) as 英语from tab55.请用一个sql语句得出结果 从table1,table2中取出如table3所列格式数据 table1月份mon 部门dep 业绩yj-一月份 01 10一月份 02 10一月份 03 5二月份 02 8二月份 04 9三月份 03 8table2部门dep 部门名称depname- 01 国内业务一部 02 国内业务二部 03 国内业务三部 04 国际业务部table3 （result）部门dep 一月份 二月份 三月份- 01 10 null null 02 10 8 null 03 5 null 8 04 null 9 null-1)select t.depname,(select yj from tab6 where mon=一月份 and dep=t.dep) 一月份,(select yj from tab6 where mon=二月份 and dep=t.dep) 二月份,(select yj from tab6 where mon=三月份 and dep=t.dep) 三月份from tab7 t-2)求总销售额select sum(case when t1.mon=一月份 then t1.yj else 0 end) 一月份,sum(case when t1.mon=二月份 then t1.yj else 0 end) 二月份,sum(case when t1.mon=三月份 then t1.yj else 0 end) 三月份from tab7 t,tab6 t1 where t.dep=t1.dep6.一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。-select id,count(*) from tab8 group by id having count(*)1 select * from (select tab8,count(id) as num from tab8 group by id) t where t.num17.用一条SQL语句 查询出每门课都大于80分的学生姓名 8. name kecheng fenshu 张三 语文 81张三 数学 75李四 语文 76李四 数学 90王五 语文 81王五 数学 100王五 英语 90a): select distinct name from tab9 where name not in (select distinct name from tab9 where fengshu=80)b): select * from tab9 t7 where not in (select from (select * from (select t1.kecheng from tab9 t1 group by t1.kecheng),(select from tab9 t2 group by ) t4,(select * from tab9) t5 where = and t4.kecheng = t5.kecheng and t5.fengshu 80)9. 一个叫department的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进行比赛，用一条sql语句显示所有可能的比赛组合.10. select t.bh|vs|t1.bh from tab10 t,tab10 t1 where t.bht1.bh这个是分主客场的11. select t.bh|vs|t1.bh from tab10 t,tab10 t1 where t.bht1.bh and t.bht1.bh这个是不分的12. 9.怎么把这样一个表儿yearmonth amount1991 1 1.11991 2 1.21991 3 1.31991 4 1.41992 1 2.11992 2 2.21992 3 2.31992 4 2.4查成这样一个结果year m1m2m3m41991 1.1 1.2 1.3 1.41992 2.1 2.2 2.3 2.4 a):select t.year,(select a.amout from tab11 a where a.month=1 and a.year=t.year) m1, (select b.amout from tab11 b where b.month=2 and b.year=t.year) m2,(select c.amout from tab11 c where c.month=3 and c.year=t.year) m3,(select d.amout from tab11 d where d.month=4 and d.year=t.year) m4from tab11 t group by t.year13. 拷贝表(拷贝数据,源表名：a 目标表名：b) SQL: insert into b(a, b, c) select d,e,f from b; 14. createtabletestasselect*fromdept;-从已知表复制数据和结构 15. createtabletestasselect*fromdeptwhere1=2;-从已知表复制结构但不包括数据11.显示文章、提交人和最后回复时间 select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 12.日程安排提前五分钟提醒 13.两张关联表，删除主表中已经在副表中没有的信息 delete from fubiao a where a.fid not in(select id from zhubiao)14.有两个表tab12和tab13，均有key和value两个字段，如果tab13的key在tab12中也有，就把tab13的value换为tab12中对应的value16. update tab13 set value=(select value from tab12 where tab12.key=tab13.key)17. 15.原表: courseid coursename score - 1 java 70 2 oracle 90 3 xml 40 4 jsp 30 5 servlet 80 - 为了便于阅读,查询此表后的结果显式如下(及格分数为60): courseid coursename score mark - 1 java 70 pass 2 oracle 90 pass 3 xml 40 fail 4 jsp 30 fail 5 servlet 80 pass - select t.courseid,t.coursename,t.score,(case when score60 then pass else fail end) mark from tab14 t18. 16.表15a1 a2 1 a 1 b 2 x 2 y 2 z 用select能选成以下结果吗？ 1 ab 2 xyz 17.题为有两个表, t1, t2,Table t1:SELLER | NON_SELLER- - A B A C A D B A B C B D C A C B C D D A D B D CTable t2:SELLER | BAL- - A 100 B 200 C 300 D 400要求用SELECT 语句列出如下结果:-如A的SUM(BAL)为B,C,D的和,B的SUM(BAL)为A,C,D的和.且用的方法不要增加数据库负担,如用临时表等19. select * from (select t. *,row_number() over(partition by username order by username) rn from USERS t) tt where tt.rn=1;Oracle SQL精妙SQL语句讲解-行列转换 行转列1. DROPTABLEt_change_lc; 2. CREATETABLEt_change_lc(card_codeVARCHAR2(3),qNUMBER,balNUMBER); 3. 4. INSERTINTOt_change_lc 5. SELECT001card_code,ROWNUMq,trunc(dbms_random.VALUE*100)balFROMdualCONNECTBYROWNUM=4 6. UNION 7. SELECT002card_code,ROWNUMq,trunc(dbms_random.VALUE*100)balFROMdualCONNECTBYROWNUM=4; 8. 9. SELECT*FROMt_change_lc; 10. 11. SELECTa.card_code, 12. SUM(decode(a.q,1,a.bal,0)q1, 13. SUM(decode(a.q,2,a.bal,0)q2, 14. SUM(decode(a.q,3,a.bal,0)q3, 15. SUM(decode(a.q,4,a.bal,0)q4 16. FROMt_change_lca 17. GROUPBYa.card_code 18. ORDERBY1;DROP TABLE t_change_lc;CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);INSERT INTO t_change_lc SELECT 001 card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM = 4UNION SELECT 002 card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM = 4;SELECT * FROM t_change_lc;SELECT a.card_code,SUM(decode(a.q, 1, a.bal, 0) q1,SUM(decode(a.q, 2, a.bal, 0) q2,SUM(decode(a.q, 3, a.bal, 0) q3,SUM(decode(a.q, 4, a.bal, 0) q4FROM t_change_lc aGROUP BY a.card_codeORDER BY 1;-行列转换 列转行 Sql代码 1. DROPTABLEt_change_cl; 2. CREATETABLEt_change_clAS 3. SELECTa.card_code, 4. SUM(decode(a.q,1,a.bal,0)q1, 5. SUM(decode(a.q,2,a.bal,0)q2, 6. SUM(decode(a.q,3,a.bal,0)q3, 7. SUM(decode(a.q,4,a.bal,0)q4 8. FROMt_change_lca 9. GROUPBYa.card_code 10. ORDERBY1; 11. 12. SELECT*FROMt_change_cl; 13. 14. SELECTt.card_code, 15. t.rnq, 16. decode(t.rn,1,t.q1,2,t.q2,3,t.q3,4,t.q4)bal 17. FROM(SELECTa.*,b.rn 18. FROMt_change_cla, 19. (SELECTROWNUMrnFROMdualCONNECTBYROWNUM=4)b)t 20. ORDERBY1,2;DROP TABLE t_change_cl;CREATE TABLE t_change_cl AS SELECT a.card_code,SUM(decode(a.q, 1, a.bal, 0) q1,SUM(decode(a.q, 2, a.bal, 0) q2,SUM(decode(a.q, 3, a.bal, 0) q3,SUM(decode(a.q, 4, a.bal, 0) q4FROM t_change_lc aGROUP BY a.card_codeORDER BY 1;SELECT * FROM t_change_cl;SELECT t.card_code,t.rn q,decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) balFROM (SELECT a.*, b.rnFROM t_change_cl a,(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM = 4) b) tORDER BY 1, 2;-行列转换 行转列 合并 Sql代码 1. DROPTABLEt_change_lc_comma; 2. CREATETABLEt_change_lc_commaASSELECTcard_code,quarter_|qASqFROMt_change_lc; 3. 4. SELECT*FROMt_change_lc_comma; 5. 6. SELECTt1.card_code,substr(MAX(sys_connect_by_path(t1.q,;),2)q 7. FROM(SELECTa.card_code, 8. a.q, 9. row_number()over(PARTITIONBYa.card_codeORDERBYa.q)rn 10. FROMt_change_lc_commaa)t1 11. STARTWITHt1.rn=1 12. CONNECTBYt1.card_code=PRIORt1.card_code 13. ANDt1.rn-1=PRIORt1.rn 14. GROUPBYt1.card_code;DROP TABLE t_change_lc_comma;CREATE TABLE t_change_lc_comma AS SELECT card_code,quarter_|q AS q FROM t_change_lc; SELECT * FROM t_change_lc_comma;SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ;), 2) qFROM (SELECT a.card_code,a.q,row_number() over(PARTITION BY a.card_code ORDER BY a.q) rnFROM t_change_lc_comma a) t1START WITH t1.rn = 1CONNECT BY t1.card_code = PRIOR t1.card_codeAND t1.rn - 1 = PRIOR t1.rnGROUP BY t1.card_code;-行列转换 列转行 分割 Sql代码 1. DROPTABLEt_change_cl_comma; 2. CREATETABLEt_change_cl_commaAS3. SELECTt1.card_code,substr(MAX(sys_connect_by_path(t1.q,;),2)q 4. FROM(SELECTa.card_code, a.q, row_number()over(PARTITIONBYa.card_codeORDERBYa.q)rn FROM5. t_change_lc_commaa)t1 6. STARTWITHt1.rn=1 7. CONNECTBYt1.card_code=PRIORt1.card_code 8. ANDt1.rn-1=PRIORt1.rn 9. GROUPBYt1.card_code; 10. SELECT*FROMt_change_cl_comma; 11. 12. SELECTt.card_code, 13. substr(t.q, 14. instr(;|t.q,;,1,rn), 15. instr(t.q|;,;,1,rn)-instr(;|t.q,;,1,rn)q 16. FROM(SELECTa.card_code,a.q,b.rn 17. FROMt_change_cl_commaa, 18. (SELECTROWNUMrnFROMdualCONNECTBYROWNUM0)t 20. ORDERBY1,2;DROP TABLE t_change_cl_comma;CREATE TABLE t_change_cl_comma ASSELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ;), 2) qFROM (SELECT a.card_code,a.q,row_number() over(PARTITION BY a.card_code ORDER BY a.q) rnFROM t_change_lc_comma a) t1START WITH t1.rn = 1CONNECT BY t1.card_code = PRIOR t1.card_codeAND t1.rn - 1 = PRIOR t1.rnGROUP BY t1.card_code;SELECT * FROM t_change_cl_comma;SELECT t.card_code,substr(t.q,instr(; | t.q, ;, 1, rn),instr(t.q | ;, ;, 1, rn) - instr(; | t.q, ;, 1, rn) qFROM (SELECT a.card_code, a.q, b.rnFROM t_change_cl_comma a,(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM 0) tORDER BY 1, 2;- 实现一条记录根据条件多表插入 Sql代码1. DROPTABLEt_ia_src; 2. CREATETABLEt_ia_srcASSELECTa|ROWNUMc1,b|ROWNUMc2FROMdualCONNECTBYROWNUM=5; 3. DROPTABLEt_ia_dest_1; 4. CREATETABLEt_ia_dest_1(flagVARCHAR2(10),cVARCHAR2(10); 5. DROPTABLEt_ia_dest_2; 6. CREATETABLEt_ia_dest_2(flagVARCHAR2(10),cVARCHAR2(10); 7. DROPTABLEt_ia_dest_3; 8. CREATETABLEt_ia_dest_3(flagVARCHAR2(10),cVARCHAR2(10); 9. 10. SELECT*FROMt_ia_src; 11. SELECT*FROMt_ia_dest_1; 12. SELECT*FROMt_ia_dest_2; 13. SELECT*FROMt_ia_dest_3; 14. 15. INSERTALL16. WHEN(c1IN(a1,a3)THEN 17. INTOt_ia_dest_1(flag,c)VALUES(flag1,c2) 18. WHEN(c1IN(a2,a4)THEN 19. INTOt_ia_dest_2(flag,c)VALUES(flag2,c2) 20. ELSE21. INTOt_ia_dest_3(flag,c)VALUES(flag1|flag2,c1|c2) 22. SELECTc1,c2,f1flag1,f2flag2FROMt_ia_src;DROP TABLE t_ia_src;CREATE TABLE t_ia_src AS SELECT a|ROWNUM c1, b|ROWNUM c2 FROM dual CONNECT BY ROWNUM=5;DROP TABLE t_ia_dest_1;CREATE TABLE t_ia_dest_1(flag VARCHAR2(10) , c VARCHAR2(10);DROP TABLE t_ia_dest_2;CREATE TABLE t_ia_dest_2(flag VARCHAR2(10) , c VARCHAR2(10);DROP TABLE t_ia_dest_3;CREATE TABLE t_ia_dest_3(flag VARCHAR2(10) , c VARCHAR2(10);SELECT * FROM t_ia_src; SELECT * FROM t_ia_dest_1;SELECT * FROM t_ia_dest_2;SELECT * FROM t_ia_dest_3;INSERT ALLWHEN (c1 IN (a1,a3) THEN INTO t_ia_dest_1(flag,c) VALUES(flag1,c2)WHEN (c1 IN (a2,a4) THEN INTO t_ia_dest_2(flag,c) VALUES(flag2,c2)ELSEINTO t_ia_dest_3(flag,c) VALUES(flag1|flag2,c1|c2)SELECT c1,c2, f1 flag1, f2 flag2 FROM t_ia_src;- 如果存在就更新，不存在就插入用一个语句实现 Sql代码 1. DROPTABLEt_mg; 2. CREATETABLEt_mg(codeVARCHAR2(10),NAMEVARCHAR2(10); 3. 4. SELECT*FROMt_mg; 5. 6. MERGEINTOt_mga 7. USING(SELECTthecodecode,thenameNAMEFROMdual)b 8. ON(a.code=b.code) 9. WHENMATCHEDTHEN10. UPDATESETa.NAME=b.NAME11. WHENNOTMATCHEDTHEN12. INSERT(code,NAME)VALUES(b.code,b.NAME);DROP TABLE t_mg;CREATE TABLE t_mg(code VARCHAR2(10), NAME VARCHAR2(10);SELECT * FROM t_mg;MERGE INTO t_mg aUSING (SELECT the code code, the name NAME FROM dual) bON (a.code = b.code)WHEN MATCHED THENUPDATE SET a.NAME = b.NAMEWHEN NOT MATCHED THENINSERT (code, NAME) VALUES (b.code, b.NAME);- 抽取/删除重复记