sql语句的复杂操作

gaoke

oracle202






sql语句的复杂操作



SQL语句的执行顺序：

select from where 的执行顺序：from(选择表) where(选择行,横向过滤) select(选择列,竖向过滤)

select from where having order by group by 的执行顺序是：

from、where、group by、having、select、order
by

create table student(snonumber(6) ,birthday date,snamevarchar2(10)); insert into student values(1,'81/1/11' ,'张三'); insert into student values(2,'82/3/10', '李四'); insert into student values(3,'83/1/06', '王五'); insert into student values(4,’83/1/26', '赵六'); create table address(snonumber(6) ,zzvarchar2(10)); insert into address values(1, '郑州'); insert into address values(2, '开封'); insert into address values(3, '洛阳'); insert into address values(4, '郑州');

要求：求出地址为郑州的，而且是学号最大的学生姓名：

SQL> select s.sname from student s where s.sno=(select max(a.sno) from address a where a.zz='郑州');

SNAME

----------

赵六


SQL>




SQL> create table temp as select s.sno,s.sname,a.zz from student s inner join address a on s.sno = a.sno where a.zz='郑州';

Table created


SQL> select t1.sno,t1.sname from temp t1 inner join temp t2on t1.sno > t2.sno; //使用自连接来比较大小

SNO SNAME

------- ----------

4 赵六


SQL>




select case when的使用
　　The syntax for simpleCASEstatements is:

CASE expr WHEN comparison_expr THEN return_expr
[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END

create table成绩(snonumber, km varchar2(10), scorenumber,gradechar(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列，用相应的等级来更新


分为几部分来分析：

  1.如何得出不同成绩代表的不同的级别；

(select sno,

         case
          when g1.score >= 90 then '优秀'
           when g1.score >=80 then '良好'
            when g1.score >=70 then '中等'
             when g1.score >=60 then '及格'
              else '不及格'
               end grade

from 成绩 g1)

  2. 如何更新每一列；

update 成绩 g2 set g2.grade=(

select a.grade from

(select sno,

case

when g1.score >= 90 then '优秀'

when g1.score >=80 then '良好'

when g1.score >=70 then '中等'

when g1.score >=60 then '及格'

else '不及格'

end grade

from 成绩 g1) a

where a.sno =g2.sno);

注意：在雨中子查询可以使用父查询中表的属性，但是父类不能使用自类查询中表的属性





复杂更新语句的使用

表T1里有a,b,c...N个字段，表T2里有a,b,c三个字段， 然后想在T1中"c"与表T2中"c"相同的情况下从表T2中将a,b覆盖表T1中的a,b怎么做 ？ create table T1(aint,bint,cint,dint,eint); create table T2(aint,bint,cint); 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 t1.a = ? , t1.b = ? where t1.c in (select t2.c from t2);

第二步：确定更新需要的值

SQL> update t1 set t1.a =(select t2.a from t2 where t2.c = t1.c) ,

     t1.b=(select t2.b from t2 where t2.c = t1.c)

     where t1.c in (select t2.c from t2);




3 rows updated









分析函数

分析函数用于计算完成聚集的累计排名、序号等 分析函数为每组记录返回多个行 以下三个分析函数用于计算一个行在一组有序行中的排位，序号从1开始 ROW_NUMBER返回连续的排序，不论值是否相等 RANK具有相等值的行排序相同，序数随后跳跃 DENSE_RANK具有相等值的行排序相同，序号是连续的

DECODE函数的使用if then else的逻辑

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

例子：

Create table sales(month char(2),sales_tvnumber,sales_computernumber); Insert into sales values('01', 10, 18); Insert into sales values('02', 28, 20); Insert into sales values('03', 36, 33);

要求查询出最后两列中较大值：



SQL> selectsign(-111) from dual;//小于就返回-1大于返回1 等于返回0

SIGN(-111)

----------

-1




SQL> select sign(199) from dual;

SIGN(199)

----------

1

SQL> select month, decode(sign(s.sales_tv -       s.sales_computer),1,s.sales_tv,-1,s.sales_computer,0,s.sales_tv,s.sales_tv)

   from sales s;

MONTH DECODE(SIGN(S.SALES_TV-S.SALES

----- ------------------------------

01 18

02 28

03 36

SQL>






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： 商品名称 季度 销售额 ---------- ---- ---------- 电视机01 100 电视机02 200 电视机03 300 空调01 50 空调02 150 空调03 180 格式2： 商品名称 一季度 二季度 三季度 四季度 ---------- ---------- ---------- ---------- ---------- 电视机100 200 300 0 空调50 150 180 0

首先是确定需要分组来进行的。还要明确的是聚合函数是在确定行或者分组以后对每行进行逐行执行的




再对分组的每个小组进行判断是使用了哪种季度

SQL> select xs.商品名称,

2sum(decode(xs.季度,'01',xs.销售额,0)) 第一季度,

       //对每行进行相加，只有通过decode来控制所被加的值是多少，

         不满足＋0如果满足就+销售额


3 sum(decode(xs.季度,'02',xs.销售额,0)) 第二季度,

4 sum(decode(xs.季度,'03',xs.销售额,0)) 第三季度

5 from 销售 xs group by xs.商品名称 order by 1;

商品名称 第一季度 第二季度 第三季度

---------- ---------- ---------- ----------

电视机 100 200 300

空调 50 150 180




SQL>






ROWNUM的使用和TOP取前多少行

创建yggz表 create tableyggz( bhnumber(6) , gznumber ); insert intoyggzvalues(1,1000); insert intoyggzvalues(2,1100); insert intoyggzvalues(3,900); insert intoyggzvalues(4,2000); insert intoyggzvalues(5,1500); insert intoyggzvalues(6,3000); insert intoyggzvalues(7,1400); insert intoyggzvalues(8,1200); 作用：对查询结果，输出前若干条记录 注意：只能与<、<=、between and连用。

查询 3号到5号的记录出来

方式1：通过集合相减

SQL> select bh,gz from yggz where rownum <=5

2 minus

3 select bh,gz from yggz where rownum <3;

BH GZ

------- ----------

   3 900

4 2000

5 1500




SQL>




方式2：通过取别名来处理

SQL> select * from (select yggz.*,rownum as rn from yggz) where rn <=5 and rn >= 3 ;

BH GZ RN

------- ---------- ----------

3 900 3

4 2000 4

5 1500 5




SQL>






注意：select * from (select * from student);

在oracle中这句话是可以的，但是在sql server中是不可以的必须个后面的语句起个别名才行




如何删除重复记录

create table student(snonumber(6) ,snamevarchar2(10), sageint); 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);

方式1：通过rowid（rowid所表示的是唯一标识一行记录，使用rowid 的方式查询记录是最快的）

     他表示段、区、块、行号所以唯一定位一行记录。

     和rownum一样都是伪列，默认是不显示的。



SQL> delete from student s where s.sno in(select sno from student group by sno having count(sno) > 1)

2 and

3 s.rowid not in (select min(rowid) from student group by sno having count(sno) > 1);

3 rows deleted

SQL>



方式2：自连接方式，首先观察下面的结果集

SQL> select * from student s1,student s2 where s2.sno = s1.sno; SNO SNAME SAGE SNO SNAME SAGE ------- ---------- --------------------------------------- ------- ---------- --------------------------------------- 1 AA 21 1 AA 21 2 BB 22 2 BB 22 3 CC 36 3 CC 23 3 CC 35 3 CC 23 3 CC 34 3 CC 23 3 CC 23 3 CC 23 3 CC 36 3 CC 34 3 CC 35 3 CC 34 3 CC 34 3 CC 34 3 CC 23 3 CC 34 3 CC 36 3 CC 35 3 CC 35 3 CC 35 3 CC 34 3 CC 35 3 CC 23 3 CC 35 3 CC 36 3 CC 36 3 CC 35 3 CC 36 3 CC 34 3 CC 36 3 CC 23 3 CC 36 18 rows selected

通过结果集可以知道只要删除表1中的rowid大于表2的rowid就可以了，这样最先插入的记录的rowid最小就不会存在被删除的可能了

因为他找不到比他小rowid的记录


SQL> delete from student s where s.rowid in(

2 select s1.rowid from student s1 inner join student s2 on s1.sno = s2.sno where s1.rowid > s2.rowid);

3 rows deleted




SQL>



方式:3子查询的方式

SQL> delete from student s1 where s1.rowid > (

2 select min(s2.rowid) from student s2 where s1.sno = s2.sno);

3 rows deleted




SQL>






GROUP BY GROUPING SETS的使用


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

其格式为：

GROUP BY GROUPING SETS ((list), (list) ... )

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

SQL> SQL> SELECT CASE 2WHEN a.deptno IS NULL THEN 3 '合计' 4 WHENa.deptno IS NOT NULL ANDa.empno IS NULL THEN 5 '小计' 6 ELSE 7 '' || a.deptno 8 END deptno, 9 a.empno, a.ename, 10 SUM(a.sal) total_sal 11 FROM scott.emp a WHERE a.sal > 2000 GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),()); 注意： 1. 在select中出现的值必须在group by grouping sets中出现但是在聚合函数中出现的则不需要这样的要求 2. 在第一个list中出现的分组属性必须在第二个list中的分组属性出现产生一个递阶的效果 3. 在when中的判断第一个when判断第一个分组是否为空，一次类推。 DEPTNO EMPNO ENAME TOTAL_SAL ---------------------------------------- ----- ---------- ---------- 10 7782 CLARK 2450 10 7839 KING 5000 小计 7450 20 7566 JONES 2975 20 7788 SCOTT 3000 20 7902 FORD 3000 小计 8975 30 7698 BLAKE 2850 小计 2850 合计 19275 10 rows selected SQL> DEPTNO EMPNO ENAME TOTAL_SAL ---------------------------------------- ----- ---------- ---------- 10 7782 CLARK 2450 10 7839 KING 5000 小计 7450 20 7566 JONES 2975 20 7788 SCOTT 3000 20 7902 FORD 3000 小计 8975 30 7698 BLAKE 2850 小计 2850 合计 19275 10 rows selected SQL> SQL> SQL> SQL> SELECT CASE 2 WHEN a.deptno IS NULL THEN 3 '合计' 4 WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN 5 '小计' 6 ELSE 7 '' || a.deptno 8 END deptno, 9 a.empno, a.ename, 10 SUM(a.sal) total_sal 11 FROM scott.emp a WHERE a.sal > 2000 GROUP BY GROUPING SETS((a.deptno, a.empno, a.ename),()); DEPTNO EMPNO ENAME TOTAL_SAL ---------------------------------------- ----- ---------- ---------- 10 7782 CLARK 2450 10 7839 KING 5000 20 7566 JONES 2975 20 7788 SCOTT 3000 20 7902 FORD 3000 30 7698 BLAKE 2850 合计 19275 7 rows selected SQL> SQL> SELECT CASE 2 WHEN a.deptno IS NULL THEN 3 '合计' 4 WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN 5 '小计' 6 ELSE 7 '' || a.deptno 8 END deptno, 9 a.empno, a.ename, 10 SUM(a.sal) total_sal 11 FROM scott.emp a WHERE a.sal > 2000 GROUP BY GROUPING SETS(()); 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(()) ORA-00979: 不是 GROUP BY 表达式 SQL> SQL> SELECT CASE 2 WHEN a.deptno IS NULL THEN 3 '合计' 4 WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN 5 '小计' 6 ELSE 7 '' || a.deptno 8 END deptno, 9 a.empno, a.ename, 10 SUM(a.sal) total_sal 11 FROM scott.emp a WHERE a.sal > 2000 GROUP BY GROUPING SETS((a.deptno),()); 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),()) ORA-00979: 不是 GROUP BY 表达式 SQL> SQL> SELECT CASE 2 WHEN a.deptno IS NULL THEN 3 '合计' 4 WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN 5 '小计' 6 ELSE 7 '' || a.deptno 8 END deptno, 9 a.empno, a.ename, 10 SUM(a.sal) total_sal 11 FROM scott.emp a WHERE a.sal > 2000 GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename)); DEPTNO EMPNO ENAME TOTAL_SAL ---------------------------------------- ----- ---------- ---------- 10 7782 CLARK 2450 10 7839 KING 5000 小计 7450 20 7566 JONES 2975 20 7788 SCOTT 3000 20 7902 FORD 3000 小计 8975 30 7698 BLAKE 2850 小计 2850 9 rows selected SQL> SQL> SELECT CASE 2 WHEN a.deptno IS NULL THEN 3 '合计' 4 WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN 5 '小计' 6 ELSE 7 '' || a.deptno 8 END deptno, 9 a.empno, a.ename, a.sal, 10 SUM(a.sal) total_sal 11 FROM scott.emp a WHERE a.sal > 2000 GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename,a.sal),()); DEPTNO EMPNO ENAME SAL TOTAL_SAL ---------------------------------------- ----- ---------- --------- ---------- 10 7782 CLARK 2450.00 2450 10 7839 KING 5000.00 5000 小计 7450 20 7566 JONES 2975.00 2975 20 7788 SCOTT 3000.00 3000 20 7902 FORD 3000.00 3000 小计 8975 30 7698 BLAKE 2850.00 2850 小计 2850 合计 19275 10 rows selected SQL>