复杂查询语句的使用

Question

2.创建复杂的视图
许多应用系统有统计等功能,建议最好把这些复杂语句写成视图.下面是几个常用的视图.
2.1分组视图
[例1]简单的分组视图
SQL> create or replace view dept_tot as 
  2  select a.dname dept,sum(b.sal) total_sal from scott.dept a,scott.emp b 
  3  where a.deptno=b.deptno group by a.dname;查看已建立。
SQL> select * from dept_tot;DEPT                           TOTAL_SAL
--------------                     ---------
ACCOUNTING          8750
RESEARCH              10875
SALES                        9400[例2]带复杂函数视图
SQL> create or replace view itemtot as
  2  select persion,sum(amount) itemtot from ledger
  3  where actiondate between 
  4  to_date('01-MAR-1901','dd-mon-yyyy') and
  5  to_date('31-MAR-1901','dd-mon-yyyy')
  6  and action in('bought','raid') group by persion;2.2合计视图
[例]合计函数视图实例
SQL> create or replace view emp_no1  as 
  2  select deptno,sum(sal) 工资和,sum(comm) 总和
  3  from scott.emp group by deptno;
SQL> select * from emp_no1;
DEPTNO    工资和      总和
--------- --------- ---------
       10      8750
       20     10875
       30      9400      22002.3组合视图
[例]带组合函数的视图
SQL> create or replace view byitem as
  2  select l.persion persion.item, amount, 100*amount/item bypersion,100*amount/total bytotal
  3  from ledgent l,itemtotal i,total where l.persion=i.persion where l.persion=i.persion
  4  and actiondate between
  5  to_date('01-MAR-1901','dd-mon-yyyy') and
  6  to_date('31-MAR-1901','dd-mon-yyyy')
  7   and action in('bought','raid') ;3.家族树
语法:
select column from table_name start with column=value
connect by prior 父主键=子主键3.1排除单一性和分枝
以ORACLE中的EMP表为例
[例]从顶到底列出各雇员的信息
SQL> select lpad(' ',4*(level-1))||ename name,empno,mgr from emp start with mgr is null
  2  connect by prior empno=mgr;NAME                                 EMPNO       MGR
---------                                  ---------           ---------
KING                                   7839            
    JONES                           7566            7839
          SCOTT                     7788            7566
                ADAMS              7876            77883.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