资源预览内容
第1页 / 共6页
第2页 / 共6页
第3页 / 共6页
第4页 / 共6页
第5页 / 共6页
第6页 / 共6页
亲,该文档总共6页全部预览完了,如果喜欢就下载吧!
资源描述
oracle复杂SQL语句一、sql语句的执行顺序1)、常见的select、from、where的顺序:1, from 2, where 3, select2)、完整的select、from、where、group by、having、order by的顺序:1, from 2, where 3, group by 4,having 5, select 6, order by二、select case when的的使用1)、语法:case when 条件1 then action1 when 条件2 then action2 when 条件3 then action3 . else actionnend caseeg、select case when substr(to_char(SYSDATE, yyyymmdd), 5 ,2) = 01 then 一月份 when substr(to_char(SYSDATE, yyyymmdd), 5 ,2) = 02 then 二月份 when substr(to_char(SYSDATE, yyyymmdd), 5 ,2) = 03 then 三月份 when substr(to_char(SYSDATE, yyyymmdd), 5 ,2) = 04 then 四月份 else other end casefrom dual;2)、语法:语法:case selector when value1 then action1 when value2 then action2 when value3 then action3 . else action nend caseeg、SELECT CASE substr(to_char(SYSDATE, yyyymmdd), 5 ,2) WHEN 01 THEN 一月份 WHEN 02 THEN 二月份 WHEN 03 THEN 三月份 WHEN 04 THEN 四月份 ELSE other END CASEFROM DUAL;3)、案例create table 成绩(sno number, km varchar2(10),score number,grade char(6); insert into 成绩 values(1, 语文, 65,null);insert into 成绩 values(2, 数学, 76,null);insert into 成绩 values(3, 英语, 86,null);insert into 成绩 values(4, 语文, 94,null);i、SELECT SNO, CASE WHEN SCORE = 90 THEN 优秀 WHEN SCORE = 80 THEN 良好 WHEN SCORE = 70 THEN 中等 WHEN SCORE = 60 THEN 及格 ELSE 不及格 END GRADE GRADE别名,END后面不能包含CASE关键字,否则报错 FROM 成绩查询结果如下:ii、把每个学生的grade列,用相应的等级来更新。- UPDATE 成绩 set grade = (SELECT grade FROM(xxxxxx) a where 成绩.sno = a.sno);update 成绩 set grade = ( select grade FROM( SELECT SNO, CASE WHEN SCORE = 90 THEN 优秀 WHEN SCORE = 80 THEN 良好 WHEN SCORE = 70 THEN 中等 WHEN SCORE = 60 THEN 及格 ELSE 不及格 END GRADE FROM 成绩 ) awhere 成绩.sno = a.sno);三、复杂更新语句的使用表T1里有a,b,c.N个字段,表T2里有a,b,c三个字段,然后想在T1中c与表T2中c相同的情况下从表T2中将a,b覆盖表T1中的a,b怎么做?i、create table T1(a int ,b int ,c int ,d int ,e int);create table T2(a int ,b int ,c int );insert into T1 values(1,2,3,4,5);insert into T1 values(10,20,3,4,5);insert into T1 values(10,20,4, 40,50);insert into T2 values( -1, -1 , 3);insert into T2 values( -2, -2, 4);ii、UPDATE T1 SET A = (SELECT A FROM T2 WHERE T1.C = T2.C), B = (SELECT B FROM T2 WHERE T1.C = T2.C)WHERE T1.C IN (SELECT C FROM T2);四、DECODE函数的使用i、select decode(x, 1, x is 1, 2, x is 2, others) from dual 当x等于1时,则返回x is 1。 当x等于2时,则返回x is 2。 否则,返回others。eg、create table student(id number,name varchar2(10),sex char(1);Insert into student values(1, 张, 1);Insert into student values(2, 王, 2);Insert into student values(3, 李, 1);-方法一select name ,decode(sex, 1,男生, 2,女生) 性别FROM student;-方法二 用case实现SELECT NAME, CASE SEX WHEN 1 THEN 男 WHEN 2 THEN 女 END 性别 FROM STUDENT;ii、DECODE取出一行内两列中的较大值create table sales(month char(2),sales_tv number,sales_computer number);Insert into sales values(01, 10, 18);Insert into sales values(02, 28, 20);Insert into sales values(03, 36, 33);-sign(number): 取数字n的符号,大于0返回1,小于0返回-1,等于0返回0 select month, decode(sign(sales_tv-sales_computer), 1, sales_tv, sales_computer) as 较大销售量 from sales;五、Oracle中的行列转换create table 销售(商品名称 varchar2(10), 季度 char(2), 销售额 number);insert into 销售 values(电视机, 01, 100);insert into 销售 values(电视机, 02, 200);insert into 销售 values(电视机, 03, 300);insert into 销售 values(空调, 01, 50);insert into 销售 values(空调, 02, 150);insert into 销售 values(空调, 03, 180);从格式1到格式2:select a.商品名称, sum(decode(a.季度,01, a.销售额 ,0 ) 一季度, sum(decode(a.季度,02, a.销售额 ,0 ) 二季度, sum(decode(a.季度,03, a.销售额 ,0 ) 三季度, sum(decode(a.季度,04, a.销售额 ,0 ) 四季度 from 销售 a group by a.商品名称 order by 1;六、ROWNUM 的使用-创建yggz表create table yggz (bh number(6),gz number);insert into yggz values(1,1000);insert into yggz values(2,1100);insert into yggz values(3,900);insert into yggz values(4,2000);insert into yggz values(5,1500);insert into yggz values(6,3000);insert into yggz values(7,1400);insert into yggz values(8,1200);作用:对查询结果,输出前若干条记录注意:只能与、= 3 AND RN = 5;-方法二select * from yggz where rownum=5minus select * from yggz where rownum=2;ii、按工资由高到底,查找表中,第3高的到第5高的记录,并显示出来。显示结果如下:-方法一SELECT BH, GZ FROM (SELECT A.*, ROWNUM RN FROM (SELECT YGGZ.* FROM YGGZ ORDER BY GZ DESC) A)WHERE RN 2;-方法二select * from (select * from yggz order by gz desc) where rownum=5minussele
网站客服QQ:2055934822
金锄头文库版权所有
经营许可证:蜀ICP备13022795号 | 川公网安备 51140202000112号