sql复杂实例

缺乏、安全感 2022-07-18 19:29 173阅读 0赞

from：http://www.wzsky.net/html/Program/DataBase/35200.html

**1.查询语句的使用**  
　　  
　　使用 select语句和子查询(subquery)可以从一个或多个表,视图,实体试图中返回数据.  
　　  
　　**1.1相关子查询**  
　　  
　　可以将子查询(as subquery)或in或exists当成where的一个条件的一部分,这样的查询称为子查询  
　　  
　　.where中可以包含一个select语句的子查询  
　　  
　　.where中可以包含in,exists语句  
　　  
　　.最多可以嵌套16层  
　　  
　　.层次过多会影响性能  
　　  
　　\[例\]简单子查询实例  
　　  
　　查询是否有的专家既以研究所的名义来申请基金项目,又以大学系为单位申请项目  
　　  
　　(按规定只能以一个单位来申请)

create table univ_subject
    　　　　(
    　　　　　 name　　　　　　　　 varchar2(12) not null,
    　　　　　 per_id　　　　　　　　number　　 not null,
    　　　　　dept_name　　　 varchar2(20)
    　　　　);
    　　 insert into univ_subject　values('gaoqianjing',1001,'信息工程系');
    　　 insert into univ_subject　values('wangbing',1002,'物理系');
    　　 insert into univ_subject　values('liming',1003,'化学系');

===============

create table　colle_subject
    　　　　 (
    　　　　　　　　　colle_name　　varchar2(20),
    　　　　　　　　　per_id　　　　　　　number
    　　　　 );
    　　 insert into colle_subject values('电子研究所',1001);
    　　　insert into colle_subject values('物理研究所',1005);

================

select name,per_id,dept_name from univ_subject where per_id in
    　　　　(select per_id from colle_subject);

NAME　　　　　　　　　　PER\_ID　　　 DEPT\_NAME  
　　------------　　　　　---------　　 --------------------  
　　gaoqianjing　　　　　　　1001　　　 信息工程系  
　　  
**　　1.2外连接**  
　　  
　　\[例\]外连接实例  
　　  
　　招生中所有学生的信息放在students表中,而部分有特长的学生在另一个表中stuent\_skill中同样有该学生的信息。现在要全部列出所有学生,如果某个学生在表student\_skill中就有其特长信息,并显示特长信息,如果某个学生没有特长就显示特长问空.

create table students
    　   　(
    　　　　 st_id　　varchar2(20),
    　　　　 name　varchar2(10),
    　　　　　 age　　　number(2),
    　　　　　 tol_score　 number(3)
    　　　　) ;
    　 　 insert into students values('973231','wangbindu',22,501);
    　 　 insert into students values('973232','zhuzhijing',21,538);
    　　　insert into students values('973233','gaojing',21,576);

===================

create table student_skill
    　　　 (
    　　　　　st_id　varchar2(20),
    　　　　　skill　　varchar2(20)
    　　　);
    　　  insert into student_skill values('973231','篮球');
    　　　insert into student_skill(st_id) values('973232');
    　　　insert into student_skill values('973233','足球');

===================

select a.* , b.skill from students a,student_skill b where a.st_id=b.st_id(+)
    　　order by a.st_id;

ST\_ID　　　　　　　　NAME　　　　 AGE　　 TOL\_SCORE SKILL  
　　-------------------- ---------- --------- --------- ----------  
　　973231　　　　　　　 wangbindu　　　22　　　 501　　　　篮球  
　　973232　　　　　　　 zhuzhijing　　 21　　　 538  
　　973233　　　　　　　 gaojing　　　　21　　　 576　　　　足球  
　**　**   
**　　1.3自我连接**  
　　  
　　自我连接是在同一个表或视图内进行条件连接.  
　　  
**　　\[例\]自我连接实例**  
　　  
　　查询每个雇员的名字和该雇员的经理的名字:

select e1.ename ||'　 work for　 '|| e2.ename "Employees and their Managers"
    　　
    　　from　scott.emp e1,scott.emp e2 where e1.mgr=e2.empno;

Employees and their Managers  
　　-------------------------------------------------  
　　SMITH　　　work for　 FORD  
　　ALLEN　　 work for　 BLAKE  
　　WARD　　　work for　 BLAKE  
　　JONES　　 work for　 KING  
　　MARTIN　 work for　 BLAKE  
　　BLAKE　　 work for　 KING  
　　CLARK　　 work for　 KING  
　　SCOTT　　　work for　 JONES  
　　TURNER　work for　 BLAKE  
　　ADAMS　　 work for　 SCOTT  
　　JAMES　　　work for　 BLAKE  
　　FORD　　　　work for　 JONES  
　　MILLER　　 work for　 CLARK  
　　  
**　　1.4UNION , INTERSECT及 MINUS**  
　　  
　　UNION:　　　　　　可以将两个以上的表的相类似的查询结果放在一起 (union all则表示返回所有的行)  
　　  
　　具体语法:  
　　  
　　select ...  
　　union\[all\]  
　　select...  
　　==========  
　　  
　　INTERSECT:　返回两个表中相同的信息  
　　具体语法:  
　　select ...  
　　intersect  
　　select...  
　　==========  
　　  
　　MINUS　　　　　:　返回一个表中出现的信息  
　　具体语法:  
　　select ...  
　　minus  
　　select...  
　　\[例1\]UNION操作实例

select　st_id　from students
    　　　union
    　　　select　st_id　from student_skill;

ST\_ID  
　　--------------------  
　　973231  
　　973232  
　　973233  
　　  
　　\[例2\]INTERSECT操作实例  
　　  
　　列出有特长的学生的学号

select st_id from students
    　　  intersect
    　　 select st_id from student_skill;

ST\_ID  
　　--------------------  
　　973231  
　　973233  
　　  
　　\[例3\]**MINUS操作实例**  
　　  
　　列出没有特长学生的学号

select st_id from students
    　　minus
    　　select st_id from student_skill;

ST\_ID  
　　--------------------  
　　973232  
　　  
　　 2.创建复杂的视图  
　　  
　　许多应用系统有统计等功能,建议最好把这些复杂语句写成视图.下面是几个常用的视图.  
　　  
　　2.1分组视图  
　　  
　　\[例1\]简单的分组视图

create or replace view dept_tot as
    　　　select a.dname dept,sum(b.sal) total_sal from scott.dept a,scott.emp b
    　　　where a.deptno=b.deptno group by a.dname;

查看已建立。  
　　SQL> select \* from dept\_tot;  
　　  
　　DEPT　　　　　　　TOTAL\_SAL  
　　--------------　　---------  
　　ACCOUNTING　　　　 8750  
　　RESEARCH　　　　　 10875  
　　SALES　　　　　　　9400  
　　  
　　\[例2\]带复杂函数视图

create or replace view itemtot as
    　　select persion,sum(amount) itemtot from ledger
    　　where actiondate between
    　　　to_date('01-MAR-1901','dd-mon-yyyy') and
    　　　to_date('31-MAR-1901','dd-mon-yyyy')
    　　　and action in('bought','raid') group by persion;

2.2合计视图  
　　  
　　\[例\]合计函数视图实例

create or replace view emp_no1　as
    　　　select deptno,sum(sal) 工资和,sum(comm) 总和
    　　　from scott.emp group by deptno;
    　   select * from emp_no1;

DEPTNO　　工资和　　　总和  
　　-------- --------- ---------  
　　10　　　　　8750  
　　20　　　　 10875  
　　30　　　　　9400　　　2200  
　　  
　　2.3组合视图  
　　  
　　\[例\]带组合函数的视图

create or replace view byitem as
    　　　select l.persion persion.item, amount, 100*amount/item bypersion,100*amount/total bytotal
    　　　from ledgent l,itemtotal i,total where l.persion=i.persion where l.persion=i.persion
    　　　and actiondate between
    　　　to_date('01-MAR-1901','dd-mon-yyyy') and
    　　　to_date('31-MAR-1901','dd-mon-yyyy')
    　 　 and action in('bought','raid') ;

3.家族树  
　　  
　　语法:  
　　select column from table\_name start with column=value  
　　connect by prior 父主键=子主键  
　　  
　　3.1排除单一性和分枝  
　　  
　　以ORACLE中的EMP表为例  
　　  
　　\[例\]从顶到底列出各雇员的信息

select lpad(' ',4*(level-1))||ename name,empno,mgr from emp start with mgr is null
    　　　connect by prior empno=mgr;

NAME　　　　　　　　　　　　　　　　 EMPNO　　　 MGR  
　　---------　　　　　　　　　　　　　---------　　---------  
　　KING　　　　　　　　　　　　　　　　 7839  
　　JONES　　　　　　　　　　　　　　　　7566　　　　　　7839  
　　SCOTT　　　　　　　　　　　　　　　　7788　　　　　　7566  
　　ADAMS　　　　　　　　　　　　　　　　7876　　　　　　7788  
　　  
　　3.2遍历至根  
　　  
　　\[例1\]现在要从某个雇员开始向他的上级列出该雇员的层次结构  
　　  
　　SQL> col ename for a30;  
　　SQL> select lpad(' ',4\*(level-1))||ename ename,mgr,empno from scott.emp  
　　2　start with mgr=7788 connect by prior mgr=empno;  
　　ENAME　　　　　　　　　　　　　　　　MGR　　 EMPNO  
　　--------------------------　　　　---------　---------  
　　ADAMS　　　　　　　　　　　　　　　 7788　　　　7876  
　　SCOTT　　　　　　　　　　　　　　　 7566　　　　7788  
　　JONES　　　　　　　　　　　　　　　 7839　　　　7566  
　　KING　　　　　　　　　　　　　　　　　　　　　　7839  
　　  
　　\[例2\]列出所有雇员的层次结构  
　　  
　　SQL> select lpad(' ',4\*(level-1))||ename ename,empno,mgr from scott.emp  
　　2　start with mgr is not null  
　　3　connect by empno=prior mgr;  
　　  
　　ENAME　　　　　　　　　　　　　　　EMPNO　　　 MGR  
　　------------------　　　　　　　 ---------　 ---------  
　　SMITH　　　　　　　　　　　　　　　 7369　　　　 7902  
　　FORD　　　　　　　　　　　　　　　　7902　　　　 7566  
　　JONES　　　　　　　　　　　　　　　 7566　　　　 7839  
　　KING　　　　　　　　　　　　　　　　7839  
　　ALLEN　　　　　　　　　　　　　　　 7499　　　　 7698  
　　BLAKE　　　　　　　　　　　　　　　 7698　　　　 7839  
　　KING　　　　　　　　　　　　　　　　7839  
　　WARD　　　　　　　　　　　　　　　　7521　　　　 7698  
　　BLAKE　　　　　　　　　　　　　　　 7698　　　　 7839  
　　KING　　　　　　　　　　　　　　　　7839  
　　JONES　　　　　　　　　　　　　　　 7566　　　　 7839  
　　KING　　　　　　　　　　　　　　　　7839  
　　MARTIN　　　　　　　　　　　　　　　7654　　　　 7698  
　　BLAKE　　　　　　　　　　　　　　　 7698　　　　 7839  
　　KING　　　　　　　　　　　　　　　　7839  
　　BLAKE　　　　　　　　　　　　　　　 7698　　　　 7839  
　　KING　　　　　　　　　　　　　　　　7839  
　　CLARK　　　　　　　　　　　　　　　 7782　　　　 7839  
　　KING　　　　　　　　　　　　　　　　7839  
　　SCOTT　　　　　　　　　　　　　　　 7788　　　　 7566  
　　JONES　　　　　　　　　　　　　　　 7566　　　　 7839

**实例2:**

表结构：

CREATE TABLE [dbo].[Exam](
        [S_date] [datetime] NOT NULL,
        [Order_Id] [varchar](50) NOT NULL,
        [Product_Id] [varchar](50) NOT NULL,
        [Amt] [numeric](18, 0) NOT NULL
    ) ON [PRIMARY]

题目一: 写一条Sql语句查询前出100到199的记录  
题目二: 写一条Sql语句删除重复\[除时间外的所有字段字段相同\]的记录,保留重复记录中时间最大的记录  
题目三: 一条Sql语句查出年份,1月,2月,3月....12月的订单总数列表  
题目四: 一条sql语句查询出年份,本月销量,上月销量,环比%,去年同期销量,同比%列表  
  
第一题比较简单，前面也讲过，就不赘述。

第二题：

delete from Exam where S_date not in (
        select e2.maxdt from
        ( 
             select Order_Id,Product_Id,Amt,MAX(S_date) as maxdt from Exam 
             group by Order_Id,Product_Id,Amt
        ) as e2
    )

第三题：分两次组

select y,sum(c1) as m1,sum(c2) as m2,sum(c3) as m3,sum(c4) as m4,sum(c5) as m5,sum(c6) as m6,
    sum(c7) as m7,sum(c8) as m8,sum(c9) as m9,sum(c10) as m10,sum(c11) as m11,sum(c12) as m12
    from 
    (
    select 
      y, 
      case m when 1 then c else 0 end as c1,
      case m when 2 then c else 0 end as c2,
      case m when 3 then c else 0 end as c3,
      case m when 4 then c else 0 end as c4, 
      case m when 5 then c else 0 end as c5,
      case m when 6 then c else 0 end as c6,
      case m when 7 then c else 0 end as c7,
      case m when 8 then c else 0 end as c8,
      case m when 9 then c else 0 end as c9,
      case m when 10 then c else 0 end as c10,
      case m when 11 then c else 0 end as c11,
      case m when 12 then c else 0 end as c12
    from 
      (
       select y,m,count(s_date) as c from 
       (
        select datepart(year,convert(DateTime,s_date)) as y,
         datepart(month,convert(DateTime,s_date)) as m ,
         s_date from exam
       )  as T1
       group by T1.y,T1.m 
      )
    as T2
    ) as T3
    group by T3.y

第三题：SQL相当复杂，不过只要理清了思路就很容易上手了，这里主要用到了left join on，语句如下：

select y1 年,m1 月,c1 本月销售额,
           c2 上月销售额,
           case when c2 is null or c2=0 then '无穷大' 
           else cast(cast((isnull(c1, 0)-isnull(c2,0))*100/isnull(c2, 0) as decimal(10,2)) as varchar(50))+'%' end as 环比增长 ,
           c3 去年本月销售额,
           case when c3 is null or c3=0 then '无穷大' 
           else cast(cast((isnull(c1, 0)-isnull(c3,0))*100/isnull(c3, 0) as decimal(10,2)) as varchar(50))+'%' end as 同比增长 
    from 
    (
    select y1,m1,c1,c2,c3
    from 
    (
       select y1,m1,c1,c2 from 
       (
        select y1,m1,sum(Amt) as c1 from 
         (
              select datepart(year,convert(DateTime,s_date)) as y1,
                       datepart(month,convert(DateTime,s_date)) as m1 ,
                      Amt 
             from orders
         )  as T1
         group by T1.y1,T1.m1 
        ) o2  
    
    left join
       (
         select y2,m2,sum(Amt) as c2 from 
         (
             select datepart(year,convert(DateTime,s_date)) as y2,
                      datepart(month,convert(DateTime,s_date)) as m2 ,
                      Amt from orders
           )  as T1
           group by T1.y2,T1.m2
         ) o3
       on o2.y1 = o3.y2 and o2.m1 = o3.m2 - 1
      ) as o4 
      left join 
      (
         select y3,m3,sum(Amt) as c3 from 
         (
             select datepart(year,convert(DateTime,s_date)) as y3,
                      datepart(month,convert(DateTime,s_date)) as m3,
                      Amt from orders
          )  as T1
          group by T1.y3,T1.m3
       ) as o5
       on o4.y1 = o5.y3 - 1 and o4.m1 = o5.m3
    ) as o6

年   月    本月销售额           上月销售额   环比        去年同期   同比

2012 3     230                         420   -45.24%         NULL    无穷大  
    2010 4     270                         NULL   无穷大          300      -10.00%  
    2011 4     300                         353   -15.01%         420      -28.57%  
    2012 4     420                         463   -9.29%           NULL     无穷大  
    2011 5     353                         NULL   无穷大          463       -23.76%  
    2012 5     463                         NULL   无穷大          NULL     无穷大  
    2011 11    492                        240    105.00%        NULL    无穷大  
    2011 12    240                        NULL    无穷大          NULL     无穷大

其他：http://www.cnblogs.com/webreport/archive/2012/10/11/2720240.html

http://www.admin10000.com/document/5812.html

[点击打开链接][Link 1]　！！

[Link 1]: http://blog.csdn.net/dxnn520/article/category/1145372