多列子查询,    -SQL语句中使用标量子查询

学学目的:

学习目的:

    -多列子查询

    -多列子查询

    -SQL语句中利用标量子查询

    -SQL语句中运用标量子查询

    -更新或删除行使用关联子查询

    -更新或删除行使用关联子查询

    -使用EXISTS和NOT EXISTS操作符

    -使用EXISTS和NOT EXISTS操作符

    -使用WITH子句

    -使用WITH子句

 

 

多列子查询

多列子查询

    语法:

    语法:

        Main query

        Main query

            WHERE (<column_name>,<column_name>) IN
Subquery;

            WHERE (<column_name>,<column_name>) IN
Subquery;

 

 

    列的可比

    列的可比

        -非成对相比较(两两比较)

        -非成对相比较(两两比较)

        -成对相比较

        -成对相比

        

        

    非成对比较
                SELECT <column>,[<column>,| <column>]
                FROM <table_name>
                WHERE <column> IN Subquery
                AND <column> IN Subquery;
        成对比较
                SELECT <column>,[<column>,|<column>]
                FROM <table_name>
                WHRE (<column>,<column>) IN
                    (SELECT <column>,<column> FROM <table_name>
                      WHERE clause);
    非成对比较
                SELECT <column>,[<column>,| <column>]
                FROM <table_name>
                WHERE <column> IN Subquery
                AND <column> IN Subquery;
        成对比较
                SELECT <column>,[<column>,|<column>]
                FROM <table_name>
                WHRE (<column>,<column>) IN
                    (SELECT <column>,<column> FROM <table_name>
                      WHERE clause);

                

                

    标量子查询

    标量子查询

        -标量子查询说明式中的子查询重返3个值

        -标量子查询表明式中的子查询再次来到三个值

        -标量子查询表明式中可以涵盖decode和case函数

        -标量子查询表明式中可以涵盖decode和case函数

        -标量子查询中需排除GROUP BY

        -标量子查询中需排除GROUP BY

        -标量子查询可以在UPDATESQL语句SET子句中和WHERE条件

        -标量子查询可以在UPDATESQL语句SET子句中和WHERE条件

        

        

        标量子查询不可以用于:

        标量子查询不或者用于:

            -列的暗许值和集群的散列表达式

            -列的暗中同意值和集群的散列表明式

            -DML语句RETURNING条件中

            -DML语句RETURNING条件中

            -基于函数的目录

            -基于函数的目录

            -GROUP BY子句、CHECK约束和WHEN子句

            -GROUP BY子句、CHECK约束和WHEN子句

            -CONNECT BY子句

            -CONNECT BY子句

            -与查询无关的语句中,如CREATE PROFILE概要文件

            -与查询毫无干系的说话中,如CREATE PROFILE概要文件

        

        

           

           

    例子:
                SELECT <column>,<column>
                    (CASE 
                        WHEN <column> = (SELECT <column> 
                                                            FROM <table_name>
                                                            WHERE clause)
                         THEN 'expression' END) <column_alias>
                 FROM <table_name>; 
    例子:
                SELECT <column>,<column>
                    (CASE 
                        WHEN <column> = (SELECT <column> 
                                                            FROM <table_name>
                                                            WHERE clause)
                         THEN 'expression' END) <column_alias>
                 FROM <table_name>; 

  

  

    关联子查询

    关联子查询

        -子查询引用主查询语句中的列值,Oracle执行相关子查询。

        -子查询引用主查询语句中的列值,Oracle执行相关子查询。

        -对于主查询中的每一行,相关的子查询都会执行三遍。

        -对于主查询中的每一行,相关的子查询都会执行五回。

        -主查询可以是SELECT/UPDATE或DELETE语句。

        -主查询可以是SELECT/UPDATE或DELETE语句。

 图片 1

 图片 2

        Nested Subqueries(嵌套子查询)或Correlated Subqueries(关联子查询)

        Nested Subqueries(嵌套子查询)或Correlated Subqueries(关联子查询)

               
-嵌套子查询:子查询首先运转并执行一回,重临结果给主查询。

               
-嵌套子查询:子查询首先运维并推行一回,重临结果给主查询。

               
-关联子查询:关联子查询由外部的主查询驱动的,所以主查询中的每一种列值子查询均会实施五回。

               
-关联子查询:关联子查询由外部的主查询驱动的,所以主查询中的各类列值子查询均会进行一次。

                

                

                嵌套子查询执行顺序:

                嵌套子查询执行顺序:

                    -子查询执行一遍并且重临一个值

                    -子查询执行两回并且重返1个值

                    -主查询使用子查询重回的值执行两回

                    -主查询使用子查询再次来到的值执行五遍

                关联子查询执行顺序:

                关联子查询执行顺序:

                    -主查询候选列值

                    -主查询候选列值

                    -子查询利用主查询候选的列值运行并履行

                    -子查询利用主查询候选的列值运维并举办

                    -子查询值重返给主查询

                    -子查询值再次回到给主查询

                    -重复以上进程,直到没有候选列值

                    -重复以上进程,直到没有候选列值

                    

                    

    SELECT <columu1>,<column2>,....
                    FROM <table_name> outer
                    WHERE <column1> operator
                                    (SELECT <column1>,<column2>
                                       FROM <table_name>
                                       WHERE expr1 = outer.expr2);
    SELECT <columu1>,<column2>,....
                    FROM <table_name> outer
                    WHERE <column1> operator
                                    (SELECT <column1>,<column2>
                                       FROM <table_name>
                                       WHERE expr1 = outer.expr2);

 

 

                    注意:关联子查询中得以选拔ANY和ALL操作符

                    注意:关联子查询中可以接纳ANY和ALL操作符

                    

                    

关联子查询效能验证与可替换方式:
16:32:16 SQL> col last_name format a30
16:32:16 SQL> set pagesize 1200
16:32:16 SQL> select last_name,salary,department_id
16:32:16   2  from employees outer
16:32:16   3  where salary >(select avg(salary)
16:32:16   4            from employees
16:32:16   5            where department_id=outer.department_id)
16:32:16   6            ;
LAST_NAME                          SALARY DEPARTMENT_ID
------------------------------ ---------- -------------
Hartstein                           12990            20
Raphaely                            10990            30
Weiss                                7990            50
Fripp                                8190            50
Kaufling                             7890            50
Vollman                              6490            50
Mourgos                              5790            50
Ladwig                               3590            50
Rajs                                 3490            50
Sarchand                             4190            50
Bull                                 4090            50
Chung                                3790            50
Dilly                                3590            50
Bell                                 3990            50
Everett                              3890            50

语句改写:

col last_name format a30
set pagesize 1200
select last_name,salary,department_id
from employees outer
where salary >(select avg(salary)
        from employees
        where department_id=outer.department_id) 
minus        
select outer.last_name,outer.salary,outer.department_id
from employees outer,(select department_id,avg(salary) avg_salary from employees group by department_id) inner
where outer.department_id=inner.department_id
and outer.salary>inner.avg_salary;

对比两种写法:

16:45:11 SQL> set autotrace traceonly
16:45:21 SQL> alter system flush shared_pool;
已更改系統.
16:45:22 SQL> select last_name,salary,department_id
16:45:22   2  from employees outer
16:45:22   3  where salary >(select avg(salary)
16:45:22   4            from employees
16:45:22   5            where department_id=outer.department_id); --关联子查询
已選取 38 個資料列.
執行計畫
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=17 Bytes=6
          97)

   1    0   MERGE JOIN (Cost=11 Card=17 Bytes=697)
   2    1     SORT (JOIN) (Cost=5 Card=11 Bytes=286)
   3    2       VIEW OF 'VW_SQ_1' (VIEW) (Cost=5 Card=11 Bytes=286)
   4    3         HASH (GROUP BY) (Cost=5 Card=11 Bytes=77)
   5    4           TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4
           Card=107 Bytes=749)

   6    1     FILTER
   7    6       SORT (JOIN) (Cost=5 Card=107 Bytes=1605)
   8    7         TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4 C
          ard=107 Bytes=1605)

統計值
----------------------------------------------------------
        351  recursive calls
          0  db block gets
        502  consistent gets
          0  physical reads
          0  redo size
       1268  bytes sent via SQL*Net to client
        449  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         50  sorts (memory)
          0  sorts (disk)
         38  rows processed

16:45:23 SQL> alter system flush shared_pool;
已更改系統.
16:45:45 SQL> select outer.last_name,outer.salary,outer.department_id
16:45:45   2  from employees outer,(select department_id,avg(salary) avg_salary from employees grou
department_id) inner
16:45:45   3  where outer.department_id=inner.department_id
16:45:45   4  and outer.salary>inner.avg_salary; --join连接查询
已選取 38 個資料列.

執行計畫
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=17 Bytes=6
          97)

   1    0   MERGE JOIN (Cost=11 Card=17 Bytes=697)
   2    1     SORT (JOIN) (Cost=5 Card=11 Bytes=286)
   3    2       VIEW (Cost=5 Card=11 Bytes=286)
   4    3         HASH (GROUP BY) (Cost=5 Card=11 Bytes=77)
   5    4           TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4
           Card=107 Bytes=749)

   6    1     FILTER
   7    6       SORT (JOIN) (Cost=5 Card=107 Bytes=1605)
   8    7         TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4 C
          ard=107 Bytes=1605)

統計值
----------------------------------------------------------
        191  recursive calls
          0  db block gets
        257  consistent gets
          0  physical reads
          0  redo size
       1268  bytes sent via SQL*Net to client
        449  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         19  sorts (memory)
          0  sorts (disk)
         38  rows processed

16:45:46 SQL>

JOIN连接查询比关联子查询性能上都有所提高。
关联子查询效能验证与可替换方式:
16:32:16 SQL> col last_name format a30
16:32:16 SQL> set pagesize 1200
16:32:16 SQL> select last_name,salary,department_id
16:32:16   2  from employees outer
16:32:16   3  where salary >(select avg(salary)
16:32:16   4            from employees
16:32:16   5            where department_id=outer.department_id)
16:32:16   6            ;
LAST_NAME                          SALARY DEPARTMENT_ID
------------------------------ ---------- -------------
Hartstein                           12990            20
Raphaely                            10990            30
Weiss                                7990            50
Fripp                                8190            50
Kaufling                             7890            50
Vollman                              6490            50
Mourgos                              5790            50
Ladwig                               3590            50
Rajs                                 3490            50
Sarchand                             4190            50
Bull                                 4090            50
Chung                                3790            50
Dilly                                3590            50
Bell                                 3990            50
Everett                              3890            50

语句改写:

col last_name format a30
set pagesize 1200
select last_name,salary,department_id
from employees outer
where salary >(select avg(salary)
        from employees
        where department_id=outer.department_id) 
minus        
select outer.last_name,outer.salary,outer.department_id
from employees outer,(select department_id,avg(salary) avg_salary from employees group by department_id) inner
where outer.department_id=inner.department_id
and outer.salary>inner.avg_salary;

对比两种写法:

16:45:11 SQL> set autotrace traceonly
16:45:21 SQL> alter system flush shared_pool;
已更改系統.
16:45:22 SQL> select last_name,salary,department_id
16:45:22   2  from employees outer
16:45:22   3  where salary >(select avg(salary)
16:45:22   4            from employees
16:45:22   5            where department_id=outer.department_id); --关联子查询
已選取 38 個資料列.
執行計畫
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=17 Bytes=6
          97)

   1    0   MERGE JOIN (Cost=11 Card=17 Bytes=697)
   2    1     SORT (JOIN) (Cost=5 Card=11 Bytes=286)
   3    2       VIEW OF 'VW_SQ_1' (VIEW) (Cost=5 Card=11 Bytes=286)
   4    3         HASH (GROUP BY) (Cost=5 Card=11 Bytes=77)
   5    4           TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4
           Card=107 Bytes=749)

   6    1     FILTER
   7    6       SORT (JOIN) (Cost=5 Card=107 Bytes=1605)
   8    7         TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4 C
          ard=107 Bytes=1605)

統計值
----------------------------------------------------------
        351  recursive calls
          0  db block gets
        502  consistent gets
          0  physical reads
          0  redo size
       1268  bytes sent via SQL*Net to client
        449  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         50  sorts (memory)
          0  sorts (disk)
         38  rows processed

16:45:23 SQL> alter system flush shared_pool;
已更改系統.
16:45:45 SQL> select outer.last_name,outer.salary,outer.department_id
16:45:45   2  from employees outer,(select department_id,avg(salary) avg_salary from employees grou
department_id) inner
16:45:45   3  where outer.department_id=inner.department_id
16:45:45   4  and outer.salary>inner.avg_salary; --join连接查询
已選取 38 個資料列.

執行計畫
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=17 Bytes=6
          97)

   1    0   MERGE JOIN (Cost=11 Card=17 Bytes=697)
   2    1     SORT (JOIN) (Cost=5 Card=11 Bytes=286)
   3    2       VIEW (Cost=5 Card=11 Bytes=286)
   4    3         HASH (GROUP BY) (Cost=5 Card=11 Bytes=77)
   5    4           TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4
           Card=107 Bytes=749)

   6    1     FILTER
   7    6       SORT (JOIN) (Cost=5 Card=107 Bytes=1605)
   8    7         TABLE ACCESS (FULL) OF 'EMPLOYEES' (TABLE) (Cost=4 C
          ard=107 Bytes=1605)

統計值
----------------------------------------------------------
        191  recursive calls
          0  db block gets
        257  consistent gets
          0  physical reads
          0  redo size
       1268  bytes sent via SQL*Net to client
        449  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         19  sorts (memory)
          0  sorts (disk)
         38  rows processed

16:45:46 SQL>

JOIN连接查询比关联子查询性能上都有所提高。

 

 

    EXISTS/NO EXISTS操作符

    EXISTS/NO EXISTS操作符

       
-EXISTS运算符平日与有关的子查询一起利用,测试子查询检索的值的结果集中是还是不是留存主查询检索的值。

       
-EXISTS运算符平时与相关的子查询一起利用,测试子查询检索的值的结果集中是或不是留存主查询检索的值。

        -若是实查询重回至少一行,则该运算符再次回到TRUE。
倘若该值不存在,则赶回FALSE。 

        -假使实查询再次回到至少一行,则该运算符重回TRUE。
如若该值不存在,则赶回FALSE。 

        -NOT
EXISTS测试主查询检索的值是不是是子查询检索的值的结果集的一有的。

        -NOT
EXISTS测试主查询检索的值是还是不是是子查询检索的值的结果集的一有的。

       

       

 SELECT <column1>,<colum2>,...
        FROM <table_name> outer
        WHERE [EXISTS | NOT EXISTS] (SELECT <column1>,<column2>,....
                                                            FROM <table_name>
                                                            WHERE <column1> = outer.<column1>);
 SELECT <column1>,<colum2>,...
        FROM <table_name> outer
        WHERE [EXISTS | NOT EXISTS] (SELECT <column1>,<column2>,....
                                                            FROM <table_name>
                                                            WHERE <column1> = outer.<column1>);

 

 

    关联UPDATE

    关联UPDATE

        

        

UPDATE <table_name> alias1
            SET <column> = (SELECT expression
                                        FROM <table_name> alias2 
                                        WHERE alias1.column = alias2.column);
UPDATE <table_name> alias1
            SET <column> = (SELECT expression
                                        FROM <table_name> alias2 
                                        WHERE alias1.column = alias2.column);

 

 

       
注意:关联UPDATE若是主查询选定值在子查询中未找到,SET栏位值将被更新成NULL值。

       
注意:关联UPDATE假设主查询选定值在子查询中未找到,SET栏位值将被更新成NULL值。

        

        

     create table ORA_01407_T0(
        id number not null,
        name varchar2(10) not null
        )
        /
        insert into ORA_01407_T0 values(1,'T01');
        insert into ORA_01407_T0 values(2,'T02');
        insert into ORA_01407_T0 values(3,'T03');
        commit;

        create table ORA_01407_T1(
        id number not null,
        name varchar2(10) not null
        )
        /
        insert into ORA_01407_T1 values(3,'T1_T03');
        insert into ORA_01407_T1 values(4,'T1_T04'); 
        commit;

        update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a.id=b.id);

        16:47:15 SQL> update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a.id=b.id);
        update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a.id=b.id)
                            *
        ERROR 在行 1:
        ORA-01407: 無法將 ("HR"."ORA_01407_T0"."NAME") 更新為 NULL

        將NAME NOT NULL約束去掉
        17:12:55 SQL> ALTER TABLE HR.ORA_01407_T0 MODIFY(NAME  NULL);
        已更改表格.
        17:12:58 SQL> update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a
        已更新 3 個資料列.
        17:13:05 SQL> select * from ORA_01407_T0;
            ID NAME
        ---------- --------------------
             1 ->可以看到關聯查詢中不匹配的記錄name欄被賦值為NULL。
             2  
             3 T1_T03

解決方案:
  使用MERGE函數進行處理,匹配記錄UPDATE
   truncate table ORA_01407_T0;
    ALTER TABLE HR.ORA_01407_T0 MODIFY(NAME  NOT NULL);
    insert into ORA_01407_T0 values(1,'T01');
    insert into ORA_01407_T0 values(2,'T02');
    insert into ORA_01407_T0 values(3,'T03');
    commit;

    merge into ORA_01407_T0 a using ORA_01407_T1 b on (a.id=b.id)
    when matched then update
    set a.name=b.name
      ;

    17:20:34 SQL> merge into ORA_01407_T0 a using ORA_01407_T1 b on (a.id=b.id)
    17:21:48   2  when matched then update
    17:21:48   3  set a.name=b.name
    17:21:48   4  ;
    合併 1 個資料列.
    17:21:49 SQL> select * from ORA_01407_T0;
        ID NAME
    ---------- --------------------
         1 T01
         2 T02
         3 T1_T03
     create table ORA_01407_T0(
        id number not null,
        name varchar2(10) not null
        )
        /
        insert into ORA_01407_T0 values(1,'T01');
        insert into ORA_01407_T0 values(2,'T02');
        insert into ORA_01407_T0 values(3,'T03');
        commit;

        create table ORA_01407_T1(
        id number not null,
        name varchar2(10) not null
        )
        /
        insert into ORA_01407_T1 values(3,'T1_T03');
        insert into ORA_01407_T1 values(4,'T1_T04'); 
        commit;

        update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a.id=b.id);

        16:47:15 SQL> update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a.id=b.id);
        update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a.id=b.id)
                            *
        ERROR 在行 1:
        ORA-01407: 無法將 ("HR"."ORA_01407_T0"."NAME") 更新為 NULL

        將NAME NOT NULL約束去掉
        17:12:55 SQL> ALTER TABLE HR.ORA_01407_T0 MODIFY(NAME  NULL);
        已更改表格.
        17:12:58 SQL> update ORA_01407_T0 a set a.name=(select name from ORA_01407_T1 b where a
        已更新 3 個資料列.
        17:13:05 SQL> select * from ORA_01407_T0;
            ID NAME
        ---------- --------------------
             1 ->可以看到關聯查詢中不匹配的記錄name欄被賦值為NULL。
             2  
             3 T1_T03

解決方案:
  使用MERGE函數進行處理,匹配記錄UPDATE
   truncate table ORA_01407_T0;
    ALTER TABLE HR.ORA_01407_T0 MODIFY(NAME  NOT NULL);
    insert into ORA_01407_T0 values(1,'T01');
    insert into ORA_01407_T0 values(2,'T02');
    insert into ORA_01407_T0 values(3,'T03');
    commit;

    merge into ORA_01407_T0 a using ORA_01407_T1 b on (a.id=b.id)
    when matched then update
    set a.name=b.name
      ;

    17:20:34 SQL> merge into ORA_01407_T0 a using ORA_01407_T1 b on (a.id=b.id)
    17:21:48   2  when matched then update
    17:21:48   3  set a.name=b.name
    17:21:48   4  ;
    合併 1 個資料列.
    17:21:49 SQL> select * from ORA_01407_T0;
        ID NAME
    ---------- --------------------
         1 T01
         2 T02
         3 T1_T03

   关联DELETE

   关联DELETE

 10:51:14 SQL> select * from ora_01407_t0
        10:51:41   2  ;
             ID NAME
        ---------- --------------------
         1 T01
         2 T02
         3 T1_T03
10:51:42 SQL> select * from ora_01407_t1;
        ID NAME
---------- --------------------
         3 T1_T03
         4 T1_T04
10:51:46 SQL> delete
10:53:05   2
10:53:07 SQL>
10:53:07 SQL>
10:53:07 SQL> delete from ora_01407_t0 t1
10:53:10   2  where id=(select id from ora_01407_t1 t2
10:53:10   3      where t2.id=t1.id);
已刪除 1 個資料列.
10:53:11 SQL> select * from ora_01407_t0;
        ID NAME
---------- --------------------
         1 T01
         2 T02
 10:51:14 SQL> select * from ora_01407_t0
        10:51:41   2  ;
             ID NAME
        ---------- --------------------
         1 T01
         2 T02
         3 T1_T03
10:51:42 SQL> select * from ora_01407_t1;
        ID NAME
---------- --------------------
         3 T1_T03
         4 T1_T04
10:51:46 SQL> delete
10:53:05   2
10:53:07 SQL>
10:53:07 SQL>
10:53:07 SQL> delete from ora_01407_t0 t1
10:53:10   2  where id=(select id from ora_01407_t1 t2
10:53:10   3      where t2.id=t1.id);
已刪除 1 個資料列.
10:53:11 SQL> select * from ora_01407_t0;
        ID NAME
---------- --------------------
         1 T01
         2 T02

 

 

    WITH子句

    WITH子句

        -使用WITH子句可以将几个相同的SELECT语句块组成七个繁杂的查询

        -使用WITH子句可以将多个一律的SELECT语句块组成三个繁杂的询问

        -WITH子句 中查询块再次回到值会保存在用户默许的临时表空间

        -WITH子句 中查询块再次来到值会保存在用户默许的临时表空间

        -使用WITH子句可压实品质

        -使用WITH子句可拉长品质

        

        

        WITH子句好处

        WITH子句好处

            -查询语句块便于阅读

            -查询语句块便于阅读

            -收缩解析,查询块数十次冒出只会评估两回

            -减弱解析,查询块数十次面世只会评估三回

            -多数场地下,可增强查询品质

            -多数景色下,可拉长查询质量

        

        

  例子:查询部门薪资超过部门平均报酬的机关

  例子:查询部门薪金当先部门平均薪给的部门

        

        

col department_name format a20
with
  dept_costs as (
    select d.department_name,sum(salary) as dept_total
    from employees e join departments d
    on e.department_id = d.department_id
    group by d.department_name),
  avg_cost as (
      select sum(dept_total)/count(*) as dept_avg
      from dept_costs)
  select *
  from dept_costs
  where dept_total > (select dept_avg from avg_cost)
  order by department_name;

    DEPARTMENT_NAME      DEPT_TOTAL
    -------------------- ----------
    Sales                    304160
    Shipping                 155950
col department_name format a20
with
  dept_costs as (
    select d.department_name,sum(salary) as dept_total
    from employees e join departments d
    on e.department_id = d.department_id
    group by d.department_name),
  avg_cost as (
      select sum(dept_total)/count(*) as dept_avg
      from dept_costs)
  select *
  from dept_costs
  where dept_total > (select dept_avg from avg_cost)
  order by department_name;

    DEPARTMENT_NAME      DEPT_TOTAL
    -------------------- ----------
    Sales                    304160
    Shipping                 155950

 

 

    学习计算:

    学习统计:

            1.多行/多列子查询(成对/非成相比较)

            1.多行/多列子查询(成对/非成对相比)

           
2.关联子查询(主查询->子查询->再次回到值->主查询)和标量子查询(再次回到一个列值)、关联UPDATE/DELETE/SELECT

           
2.关联子查询(主查询->子查询->重返值->主查询)和标量子查询(再次来到2个列值)、关联UPDATE/DELETE/SELECT

            3.EXISTS和NOT EXISTS操作符

            3.EXISTS和NOT EXISTS操作符

            4.使用WITH子句

            4.使用WITH子句

 

 

相关文章