三、复杂SQL语句

• 掌握 SQL语句的执行顺序、能分析复杂SQL语句的执行过程
• 掌握 Oracle子查询、自连接、行列转换
• 掌握分析函数、decode函数、 SELECT CASE WHEN的使用
• 掌握 Oracle 分页、删除重复记录的方法

SQL语句的执行顺序

常见的select、from、where的顺序：

1, from  2, where  3, select


完整的select、from、where、group by、having、order by的顺序：

1, from  2, where  3, group by  4,having  5, select  6, order by

EXISTS 的使用

EXISTS用来判断查询所得的结果中，是否有满足条件的纪录存在。

select *　from student 
   where    exists(select * from address
                    where zz='郑州');

​ 从select 、from、where三者的先后执行顺序来分析。

子查询的使用

create table student(sno number(6) ,birthday date, sname varchar2(10));
insert into student values(1, '11-1月-81' , '张三');
insert into student values(2, '10-3月-82' , '李四');
insert into student values(3, '06-1月-83' , '王五');
insert into student values(4, '26-1月-83' , '赵六');


create table address(sno number(6) , zz varchar2(10));
insert into address values(1,   '郑州');
insert into address values(2,   '开封');
insert into address values(3,   '洛阳');
insert into address values(4,   '郑州');

要求：找出zz是郑州的学生中，sno最大的学生的 sname

select sname 　　　　　
　 from student  　　　
　where sno =( 　　　　　　　
　select max(sno) 　　　　　　　　　　　　　
　from address　　　　　　　　　　　　　　　　
where zz= '郑州' );　　

group by的使用

1.创建student表
  create table student(xh number,xm varchar2(10),nl int);
   insert into student values (1,'A',21);  
  insert into student values (2,'B',22);
  insert into student values (3,'A',23);  
  insert into student values (4,'A',24);
  insert into student values (5,'A',25);  
  insert into student values (6,'C',26);
  insert into student values (7,'B',27);

任务：查找xm有相同的纪录，并显示出来，如下

答案：select * from student where xm in (select xm from student group by xm having count(*)>1);

自连接的使用

CREATE TABLE  管理人员  (
  编号  char(10) ,
  姓名  varchar2(10) ,
  管理人员编号  char(10)
);

insert into 管理人员 values('001', '张一', '004');
insert into 管理人员 values('002', '张二', '004');
insert into 管理人员 values('003', '张三', '003');
insert into 管理人员 values('004', '张四', '004');

要求：现在想显示：编号，姓名，管理人员姓名

select a.编号,a.姓名,b.姓名 as 管理人员姓名    
from 管理人员 a   join 管理人员 b on a.管理人员编号=b.编号;

SELECT CASE WHEN

语法：
CASE    WHEN   条件1   THEN   action1   
		WHEN   条件2   THEN   action2
        WHEN   条件3   THEN   action3
        …..   
        ELSE actionN 
        
        END CASE   

select  case 
      when  substr('20090310',5,2) = '01'  then  '一月份'
      when  substr('20090310',5,2) = '02'  then  '二月份'
      when  substr('20090310',5,2) = '03'  then  '三月份'
      when  substr('20090310',5,2) = '04'  then  '四月份'
      else null
    end 
from dual;

语法：
CASE selector   
		WHEN value1 THEN action1
        WHEN value2 THEN action2
      	WHEN value3 THEN action3
        …..
        
        ELSE actionN
        END [CASE]

select  case substr('20090310',5,2) 
      when   '01'  then  '一月份'
      when    '02'  then  '二月份'
      when   '03'  then  '三月份'
      when    '04'  then  '四月份'
      else null
    end 
from dual;

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);

要求：把每个学生的grade列，用相应的等级来更新。


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 成绩
     ) a
    where 成绩.sno = a.sno   );

复杂更新语句的使用

表T1里有 a,b,c...N个字段，表T2里有 a,b,c三个字段，
然后想在T1中"c"与表T2中"c"相同的情况下从表T2中将a,b覆盖表T1中的a,b怎么做 ？

 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);


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);

分析函数的使用

分析函数用于计算完成聚集的累计排名、序号等

分析函数为每组记录返回多个行

以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始

• ROW_NUMBER 返回连续的排序，不论值是否相等

• RANK 具有相等值的行排序相同，序数随后跳跃

• DENSE_RANK 具有相等值的行排序相同，序号是连续的

DECODE 的使用

DECODE 中的if-then-else逻辑

• 在逻辑编程中，经常用到If – Then –Else 进行逻辑判断。在DECODE的语法中，实际上就是这样的逻辑处理过程。它的语法如下：
• DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )
• Value 代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode 函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then 配对。如果value结果不等于给出的任何配对时，Decode 结果就返回else 。
• 需要注意的是，这里的if、then及else 都可以是函数或计算表达式。

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 id,name,
     case sex
      when '1' then '男'
      when '2' then '女'
     end 性别
from student;

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);



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);

作用：对查询结果，输出前若干条记录
注意：只能与==<、<===、between and连用

任务(1)答案：
    select bh,gz 
from (
  select yggz.*, rownum rn
  from yggz 
)
where rn >=3 and rn <= 5;
或者：
select * from yggz where rownum<=5
minus 
select * from yggz where rownum<=2;

任务(2)答案：
     select bh,gz from (
 select a.*,rownum rn from(
  select yggz.*    from yggz order by gz desc  ) a 
 )
 where rn<=5 and rn>2;
或者：
 select * from (select * from yggz
      order by gz desc)       where rownum<=5
 minus
 select * from (select * from yggz
      order by gz desc)      where rownum<=2;

删除重复记录

删除重复记录 方法1

create table student(sno number(6)  , sname varchar2(10), sage  int );  
insert into student values(1, 'AA', 21);
insert into student values(2, 'BB', 22);
insert into student values(3, 'CC', 23);
insert into student values(3, 'CC', 34);
insert into student values(3, 'CC', 35);
insert into student values(3, 'CC', 36);



DELETE FROM student
   WHERE sno IN 
     (SELECT sno FROM student GROUP BY sno HAVING COUNT(*) > 1)
   AND ROWID NOT IN
     (SELECT MIN(ROWID) FROM student GROUP BY sno HAVING COUNT(*) > 1);

删除重复记录 方法2

DELETE FROM student WHERE ROWID IN
    (SELECT A.ROWID FROM student A,student B 
     WHERE A.sno=B.sno AND A.ROWID > B.ROWID);

删除重复记录 方法3

DELETE FROM student d WHERE d.rowid > 
    (SELECT MIN(x.rowid) FROM student x WHERE d.sno=x.sno);

GROUP BY GROUPING SETS

可以用GROUP BY GROUPING SETS来进行分组自定义汇总，**可以应用它来指定你需要的总数组合。

其格式为：
GROUP BY GROUPING SETS ((list), (list) … )

这里（list）是圆括号中的一个列序列，这个组合生成一个总数。要增加一个总和，必须增加一个(NUlL)分组集。

例如：对于scott.emp表，如果要查询：各部门sal大于2000的员工，进行汇总，得到各部门的sal总和、以及总共的sal总和。
SELECT CASE       
   WHEN a.deptno IS NULL THEN
           '合计'
   WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN
          '小计'
   ELSE
         '' || a.deptno
   END deptno,
      a.empno,        a.ename,
     SUM(a.sal) total_sal
  FROM scott.emp a  WHERE a.sal > 2000  GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());