资源描述：
《oracle系列教程三oracle复杂sql语句》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、oracle复杂SQL语句一、sql语句的执行顺序1)、常见的select、from、where的顺序：1,from2,where3,select2)、完整的select、from、where、groupby、having、orderby的顺序：1,from2,where3,groupby4,having5,select6,orderby二、selectcasewhen的的使用1)、语法：casewhen条件1thenaction1when条件2thenaction2when条件3thenaction3...elseactionnend[case]eg、selectcasewhensu
2、bstr(to_char(SYSDATE,'yyyymmdd'),5,2)='01'then'一月份'whensubstr(to_char(SYSDATE,'yyyymmdd'),5,2)='02'then'二月份'whensubstr(to_char(SYSDATE,'yyyymmdd'),5,2)='03'then'三月份'whensubstr(to_char(SYSDATE,'yyyymmdd'),5,2)='04'then'四月份'else'other'endcasefromdual;2)、语法：语法：caseselectorwhenvalue1thenaction1whenv
3、alue2thenaction2whenvalue3thenaction3...elseactionnend[case]eg、SELECTCASEsubstr(to_char(SYSDATE,'yyyymmdd'),5,2)WHEN'01'THEN'一月份'WHEN'02'THEN'二月份'WHEN'03'THEN'三月份'WHEN'04'THEN'四月份'ELSE'other'ENDCASEFROMDUAL;3)、案例createtable成绩(snonumber,kmvarchar2(10),scorenumber,gradechar(6));insertinto成绩values(
4、1,'语文',65,null);insertinto成绩values(2,'数学',76,null);insertinto成绩values(3,'英语',86,null);insertinto成绩values(4,'语文',94,null);i、SELECTSNO,CASEWHENSCORE>=90THEN'优秀'WHENSCORE>=80THEN'良好'WHENSCORE>=70THEN'中等'WHENSCORE>=60THEN'及格'ELSE'不及格'ENDGRADE–GRADE别名，END后面不能包含CASE关键字，否则报错FROM成绩查询结果如下：ii、把每个学生的grade列
5、，用相应的等级来更新。--UPDATE成绩setgrade=(SELECTgradeFROM(xxxxxx)awhere成绩.sno=a.sno);update成绩setgrade=(selectgradeFROM(SELECTSNO,CASEWHENSCORE>=90THEN'优秀'WHENSCORE>=80THEN'良好'WHENSCORE>=70THEN'中等'WHENSCORE>=60THEN'及格'ELSE'不及格'ENDGRADEFROM成绩)awhere成绩.sno=a.sno);三、复杂更新语句的使用表T1里有a,b,c...N个字段，表T2里有a,b,c三个字段，然后
6、想在T1中"c"与表T2中"c"相同的情况下从表T2中将a,b覆盖表T1中的a,b怎么做？i、createtableT1(aint,bint,cint,dint,eint);createtableT2(aint,bint,cint);insertintoT1values(1,2,3,4,5);insertintoT1values(10,20,3,4,5);insertintoT1values(10,20,4,40,50);insertintoT2values(-1,-1,3);insertintoT2values(-2,-2,4);ii、UPDATET1SETA=(SELECTAFRO
7、MT2WHERET1.C=T2.C),B=(SELECTBFROMT2WHERET1.C=T2.C)WHERET1.CIN(SELECTCFROMT2);四、DECODE函数的使用i、selectdecode(x,1,'xis1',2,'xis2','others')fromdual当x等于1时，则返回‘xis1’。当x等于2时，则返回‘xis2’。否则，返回‘others’。eg、createtablestudent(idnumber,nam
